Intermediate SQL Tutorial | Unions | Union Operator

Key Takeaways

I mean it's going on everybody my name is Alex Freiburg and in today's video we're gonna be looking at unions now in the very last video we walked through joins and if I was appropriate to look at unions next because unions and joins are somewhat similar are closely related and that's because in both instances they're combining two tables to create one output now what's the difference the difference is that a join combines both tables based off a common column and in last video that was the employee ID so in both tables we had an employee ID and when you're selecting your data you have to choose either to only select one employee ID or you can choose both employee IDs but they're in separate columns and with a union you're actually able to select all the data from both tables and put it into one output where all the data is in in each column and not separate it out and you don't have to choose which table you're choosing it from now that may not have made a hundred percent sense but let's look at it real quick in stages so let's go down here and let's actually join this table together and see what we get now the two tables that we're looking at is employee demographics and warehouse employee demographics so over here we have our employee demographics information and then over here or actually down here we have our warehouse employee demographics now right now I'm doing a full outer join so we're looking at all the data and if we were to pull this in to an Excel spreadsheet we could just copy this and paste it over here and we would be good to go and that's because we have all the same columns first name last name age gender first name last name age gender but if we tried to combine this in a query where we have this information right here it wouldn't work we cannot get it in the same column and that's where a Union comes into play so let's go back up here and let's actually run both of these now as you can see they have the exact same columns and that makes it super easy for what we're about to do all we're going to do is between these two queries which are completely separate right now all we're gonna do is write Union so let's run just this now because of the Union you can look down here and the information that used to be in the other table which were in separate columns are now added down below in the exact same order now Darryl Philbin was actually in both tables and the reason he isn't showing up multiple times is because this Union is actually taking out and removing the duplicates kind of like a distinct statement now there's actually another thing called Union all and if we do Union all it is going to show us all of the information regardless if it is a duplicate or not so let's run that real quick and they are both there but let's order by and let's do employee ID so now let's run it and as you can see right here these are exact duplicates and so the Union got rid of it because they were the exact same but the Union all kept it in because it is showing just the data as is now let's get rid of this Union all because the only reason why it works so well is because those two tables were the exact same they were employee ID first name last name age gender so they're basically the same tables just with different information so it made it really easy but we have another table employee salary and let's look at these two tables so these two tables are obviously very different they hold different information now we would still be able to combine them so let's do employee ID first name and let's do age now down here on the employee salary table we will do employee ID job title and salary now let's use a union really quick and run this one and it is still going to work now why does this work well first off the reason it's working is because these data types are the exact same or at least similar so text and text age which is an integer sound reader which is an integer it has the same amount of columns so 3 & 3 so we have employee ID first name and age and it's taking that from the first select statement and it's still using a union to take the data from the second select statement so it's still inserting this information now this is not what you want to do because right here we have first name and it's salesman salesman and then our age we have thirty forty-five thousand and forty-five dollars obviously not an age so you want to be careful when you're using a union to combine two separate tables and make sure that the data you're selecting is the same in the very next video we're going to walk you through case statements thank you guys so much for joining me I really appreciate it if you like this type of content be sure to subscribe below and I'll see you in the next video