 Hello everybody. In this lesson, we're going to be taking a look at unions in my SQL. A union allows you to combine rows together, not like columns like we were doing before with joins, or one column is next to the other. And union allows you to combine the rows of data from separate tables or from the same table. It's up to you. But you do that by taking one select statement and using a union to combine it with another select statement. Let's see how this actually looks. So what we're going to do is right after the select statement, we're going to come here and say union, then we're going to go right below the union. And we're going to do another select statement. So we're going to copy this, place it right here. I said the demographics table. Just for example, we'll do the salary table. Now if we look at the demographics table, let's say we want to take age and gender. And let's go and take a look really quickly at the salary table. And let's say we want to take a first name and last name. We'll do first underscore name and last underscore name. Now let's go ahead and run this and see what it looks like. And let's pull this up. So as you can see, we have age and gender. That's from the very first select statement. And that's also the column names. But then we have all of the data for the age and gender. And then below, if we move this over a little bit, we have the last name and first name from the employee salary table. It's just down here. Now what I just demonstrate is that this doesn't always work for everything. You can't just combine random data together because this is bad data. We shouldn't have age and gender mixed with first name and last name. Really, when you're using this, you need to keep the data the same. So for us, we should take the first, I'll actually just copy this, the first and last name from the employee demographics as well. And let's run this. Now we have all the names from all of the tables. Now you may be thinking, where'd all the other data go before we had a lot of rows, but now we only have a unique row for each one. Well, by default, this is actually a union distinct. And if you remember, distinct is only going to take unique values. So when we're doing this, union is going to remove all the duplicates. And the first name and last name from salary overlaps a lot with the employee demographics table. So when we ran this, the only one that's actually somewhat unique to one table is that in the employee salary table we have Ron Swanson, whereas we don't have that in the employee demographics. Now, if we wanted to show all of them without the distinct, there is something called a union all. If we run this Now we're going to get all of the results without removing any of the duplicates. So if we were scrolled down, we're going to have duplicates in here. But we're just showing all of the results from this table and from this table. Now that we know how to actually use a union. Let's look at a use case. So let's go right down here and let's copy this. Why not And let's put it right down here. Now let's say in the employee demographics, we wanted to take the first name and last name where the age is greater than 50. And let's run this. There's only one person, but let's label them. Let's have a label. We're gonna say comma old. This person is old. And if we run this, it says first name, last name and old and we can even call this as label. And if we run this, the label is old. So Jerry Gergich is the only old person in this demographics table. Now, why are we doing this? Well, the parks department is trying to cut their budget a little bit. They want to identify older employees that they can push out. And they also want to identify high paid employees who they can reduce their pay or push them out to save money. So we just identified someone who's older who we're going to want to try to push out. But let's in the same output find people who are also highly paid. So now we can come down here. We can say Union. And let's do this like this. I need to spell this right. All right, Union. And let's take this. We're not going to be using this exact same query. But we actually need to pull from the salary table for the employee salary. So we also want the first name and last name. But let's say where their salary is greater than let's say 70,000. That's a lot of money. You're making more than 70. For sure the parks department is going to try to get rid of you. But for the label, we're going to change it to a highly paid employee. Now let's go ahead and run this. So now we have Leslie Nope and Chris Traeger. They're both labeled as highly paid employees. Now 50 I think is just a little too low. If I'm being completely honest, I think we need to change this and we should do a union and then add another select statement. Bring this down. I think the 50 is too low. Let's change it to 40. And let's add one more thing. Let's say and the gender is equal to male and then we'll go down here and say where the gender is equal to female because we want to separate this out. So we want to know who's the old man. Oh, that's actually old lady. This is the female one. And for up here where it's male will say old man. So we have three different select statements using two separate unions. We're selecting the first name and the last name in all of them keeping the data consistent. And then in our third column, we're labeling it either old man, old lady, or highly paid employee. Let's go ahead and run this. And let's look at our output. Now you may notice something really quickly that Chris Trager and Leslie Knope are an old man and an old lady and Leslie Knope and Chris Trager are both highly paid employees. So these people meet multiple criteria. So let's actually order by and then we'll do first underscore name comma last underscore name because we want to order by these to see. So let's run. And now we can easily see that Chris Trager is both of these. Donna is just an old lady. Barry is just an old man and Leslie is both an old lady and a highly paid employee. So now we can send this to whoever we need to send it to to make sure that these people get looked at first so that our job is still secure. The job market is tough these days. You got to do what you got to do. So that is how we use union. And let's just take one more look at it. There we go. So this is how we can use unions. It's kind of a real use case. I've done something very similar to this in my real job. But you know this is just an example of how you can have multiple select statements all combined or combining the rows using a union. In the next lesson, we're going to be taking a look at case statements.