UNION AND UNION ALL

Understand how to combine data from 2 queries using UNION and UNION ALL

8 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Union vs Join — UNION combines rows vertically from multiple queries, unlike JOIN which combines columns horizontally.
Basic Union Syntax — The basic syntax involves placing the UNION keyword between two complete SELECT statements.
Data Consistency Rule — All SELECT statements must select the same number and type of columns for the union to produce meaningful results.
UNION is DISTINCT — By default, UNION acts as UNION DISTINCT, removing duplicate rows from the final combined result set.
Using UNION ALL — UNION ALL must be used explicitly if the goal is to include all rows from all queries, including duplicates.
Adding a Label Column — A constant string column can be added to each SELECT statement to categorize the rows based on the filter criteria applied.
Complex Union Use Case — Multiple SELECT statements can be combined using sequential UNIONs to identify employees meeting various criteria, such as age or salary.
Ordering the Final Result — A single ORDER BY clause placed at the end of the entire union query is used to sort the final combined output.

PDF notes

Bottom line: You will learn how to combine the rows returned by multiple independent SELECT statements into a single result set using the UNION operator. You will be able to differentiate between combining only unique rows (UNION) and combining all rows (UNION ALL). This technique is essential for merging data subsets that meet different criteria into one cohesive report.

This lesson on UNION AND UNION ALL is hands-on and example-driven. You will learn how to combine the rows returned by multiple independent SELECT statements into a single result set using the UNION operator. You will be able to differentiate between combining only unique rows (UNION) and combining all rows (UNION ALL). This technique is essential for merging data subsets that meet different criteria into one cohesive report.

What You'll Be Able To Do

Combine rows from separate queries using the UNION operator.
Differentiate between UNION (distinct) and UNION ALL (all rows).
Ensure column count and data types are consistent across unioned queries.
Apply filtering criteria (e.g., WHERE clauses) to individual SELECT statements within a union.
Use unions to categorize and label rows based on different selection criteria.
Apply ORDER BY only once to the final combined result set.

Topics Covered in UNION AND UNION ALL

Union vs Join (0:00 - 0:30) — UNION combines rows vertically from multiple queries, unlike JOIN which combines columns horizontally.
Basic Union Syntax (0:30 - 1:30) — The basic syntax involves placing the UNION keyword between two complete SELECT statements.
Data Consistency Rule (1:30 - 3:00) — All SELECT statements must select the same number and type of columns for the union to produce meaningful results.
UNION is DISTINCT (3:00 - 4:30) — By default, UNION acts as UNION DISTINCT, removing duplicate rows from the final combined result set.
Using UNION ALL (4:30 - 5:30) — UNION ALL must be used explicitly if the goal is to include all rows from all queries, including duplicates.
Adding a Label Column (5:30 - 7:30) — A constant string column can be added to each SELECT statement to categorize the rows based on the filter criteria applied.
Complex Union Use Case (7:30 - 10:00) — Multiple SELECT statements can be combined using sequential UNIONs to identify employees meeting various criteria, such as age or salary.
Ordering the Final Result (10:00 - 11:30) — A single ORDER BY clause placed at the end of the entire union query is used to sort the final combined output.

SQL Cheat Sheet

SELECT <cols> FROM <table> UNION SELECT <cols> FROM <table>; — Combines results of two SELECT statements, automatically removing duplicates
```
SELECT first_name, last_name FROM employee_demographics UNION SELECT first_name, last_name FROM employee_salary;
```

UNION ALL — Combines results of two SELECT statements, retaining all rows, including duplicates

SELECT first_name, last_name FROM employee_demographics UNION ALL SELECT first_name, last_name FROM employee_salary;

Data Consistency Rule — All SELECT statements must return the same number and type of columns

SELECT first_name, last_name FROM employee_demographics UNION SELECT first_name, last_name FROM employee_salary;

Labeling Column — Adds a constant string column to identify the source or category of the row

SELECT first_name, last_name, 'Old Man' AS label FROM employee_demographics WHERE age > 50;

Multiple Unions — Allows combining results from three or more SELECT statements sequentially

SELECT first_name FROM T1 UNION SELECT first_name FROM T2 UNION SELECT first_name FROM T3;

ORDER BY — Sorts the entire combined result set based on specified columns

SELECT first_name, last_name FROM T1 UNION ALL SELECT first_name, last_name FROM T2 ORDER BY first_name;

Comparison Table

Comparison	UNION (Default)	UNION ALL
Duplicate Handling	Removes duplicates (DISTINCT)	Keeps all rows
Speed	Slower (Requires sorting)	Faster (Simple concatenation)
Use Case	Unique list of values	Full data audit
Keyword Required	No (Default behavior)	Yes (Must be specified)

Common Pitfalls

Mistake: Selecting different numbers or types of columns in each query. Avoid: Ensure all SELECT statements return identical column structure.
Mistake: Expecting all rows to appear when using the default UNION operator. Avoid: Use UNION ALL explicitly if duplicates must be included in the output.
Mistake: Applying ORDER BY to the first SELECT statement only. Avoid: Place the single ORDER BY clause at the very end of the entire union query.
Mistake: Mixing data types that represent different concepts (e.g., age and name). Avoid: Keep the meaning of corresponding columns consistent across all queries.

FAQs

Which SELECT statement determines the final column names? The column names and aliases from the very first SELECT statement are used for the final result set.
Can I use WHERE clauses in the individual SELECT statements? Yes, WHERE clauses are essential for filtering the data subset returned by each individual query before the union occurs.
Can I union results from tables that have no relationship? Yes, unlike JOINs, UNIONs only require that the selected columns are structurally compatible (same count and data types).
Why did my result set suddenly shrink when I switched from UNION ALL to UNION? UNION automatically removes duplicate rows that exist across the combined result sets, resulting in fewer total rows.

Common pitfalls

Mistake: Selecting different numbers or types of columns in each query. Avoid: Ensure all SELECT statements return identical column structure.
Mistake: Expecting all rows to appear when using the default UNION operator. Avoid: Use UNION ALL explicitly if duplicates must be included in the output.
Mistake: Applying ORDER BY to the first SELECT statement only. Avoid: Place the single ORDER BY clause at the very end of the entire union query.
Mistake: Mixing data types that represent different concepts (e.g., age and name). Avoid: Keep the meaning of corresponding columns consistent across all queries.

Hello everybody, in this lesson we're going to be taking a look at unions in MySQL. A union allows you to combine rows together, not like columns like we were doing before with joins, where one column is next to the other. A 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. Instead of 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 first name and last name. So, 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 demonstrated 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, actually, I'll just copy this. The first and last name from the employee demographics as well. And let's run this. And now we have all the names from all of the tables. Now, you may be thinking, where did 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 to scroll 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 add a label. We're going to say comma "Old". So, 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 and 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, so 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, because that's a lot of money. If 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. Let's 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 is the old man. Oh, that's actually "Old Lady". This is the female one. And for up here where it's male, we'll 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 Traeger and Leslie Nope are an "Old Man" and an "Old Lady", and Leslie Nope and Chris Traeger 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 Traeger is both of these. Donna is just an "Old Lady". Jerry 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.

Frequently asked questions

Which SELECT statement determines the final column names?

The column names and aliases from the very first SELECT statement are used for the final result set.

Can I use WHERE clauses in the individual SELECT statements?

Yes, WHERE clauses are essential for filtering the data subset returned by each individual query before the union occurs.

Can I union results from tables that have no relationship?

Yes, unlike JOINs, UNIONs only require that the selected columns are structurally compatible (same count and data types).

Why did my result set suddenly shrink when I switched from UNION ALL to UNION?

UNION automatically removes duplicate rows that exist across the combined result sets, resulting in fewer total rows.