SQL UNION vs UNION ALL: Stack Two Lists
UNION and UNION ALL stack the rows of two queries into one list. See how UNION ALL keeps every row and UNION removes the duplicates, and why both queries need the same columns, using two contact lists from a sample online shop.
Step by step
Two lists
- One list from two. Marketing wants one mailing list made from two lists. Union stacks the rows of two queries on top of each other. But union and union all behave differently. Let's see how.
- The customers table. Both lists come from the customers table, which has names, emails, regions and signup dates.
List one
- First list. List one is every customer in the North region. First name, last name and email. Ten customers in the North.
SELECT first_name, last_name, email FROM customers WHERE region = 'North';
UNION ALL
- UNION ALL. List two is our earliest customers, who signed up before April twenty twenty four. Put union all between the two queries, and keep the same three columns. Eighteen rows. Ten plus eight. Union all keeps every row, so three people who are on both lists show up twice. Their second copies sit further down the list.
UNION ALL SELECT first_name, last_name, email FROM customers WHERE signup_date < '2024-04-01';
UNION
- UNION. Nobody wants two copies of the same email. Delete the word all, so it just says union. Fifteen rows. Union removed the three duplicates. Now every person is on the list once.
UNION
Same columns
- Same columns. One rule. Both queries must give the same number of columns. Watch what happens if the second list drops the email. An error. The first query gives three columns and the second gives two, so SQL can't stack them. Put the email back. The columns line up by position, so keep them in the same order too. Fifteen rows again. One clean mailing list.
FROM customers WHERE signuplast_name, email FROM customers WHERE signup
Recap
- UNION vs UNION ALL. So, union all stacks every row, duplicates included, and it's faster. Union removes the duplicates. Both need the same number of columns, in the same order.
