BA Prince the B.A. Subscribe

Home / SQL for Business Analysts

SQL UNION vs UNION ALL: Stack Two Lists

SQL UNION vs UNION ALL: Stack Two Lists

Watch on YouTube Subscribe

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 1 mailing list made from 2 lists. UNION and UNION ALL both stack the rows of 2 queries on top of each other, but they handle duplicates differently.
The customers table. Both lists come from the customers table. It has first_name, last_name, email, region and signup_date columns.

List one

First list. List 1 is every customer in the North region. Select first_name, last_name and email from customers WHERE region = 'North'. Run it and you get 10 rows: the 10 customers in the North.
```
SELECT first_name, last_name, email
FROM customers WHERE region = 'North';
```

UNION ALL

UNION ALL. List 2 is the earliest customers, who signed up before 1 April 2024. Remove the semicolon, type UNION ALL under the first query, then add a second SELECT with the same 3 columns and WHERE signup_date < '2024-04-01'. Run it and you get 18 rows, which is 10 plus 8. UNION ALL keeps every row, so the 3 people who are on both lists appear twice. Their second copies sit further down.
```
UNION ALL
SELECT first_name, last_name, email
FROM customers WHERE signup_date < '2024-04-01';
```

UNION

UNION. Nobody wants 2 copies of the same email. Delete the word ALL so the line just says UNION. Run it again and you get 15 rows. UNION removed the 3 duplicate rows, so each person now appears once.
```
UNION
```

Same columns

Same columns. Both queries must return the same number of columns. To see why, delete ", email" from the second SELECT so it returns only first_name and last_name. Running it gives an error. The first query returns 3 columns and the second returns 2, so SQL can't stack them. Put email back after last_name. Columns are matched by position, not by name, so keep them in the same order in both queries. Run it and you get 15 rows again: 1 clean mailing list.
```
FROM customers WHERE signup
```
```
last_name, email
FROM customers WHERE signup
```

Recap

UNION vs UNION ALL. UNION ALL keeps every row, duplicates included, and is usually faster because it skips the duplicate check. UNION keeps 1 copy of each repeated row. Both need the same number of columns, in the same order.

Common mistakes

Using UNION when repeated rows are real data. If you stack 2 order lists and 2 different orders have the same values in every selected column, UNION keeps only 1 of them and your totals come out too low. Use UNION ALL whenever every row counts.
Columns listed in a different order. Columns line up by position, so if the second query lists email, first_name, last_name, the emails end up under first_name. There may be no error if the types match. List the columns in the same order in both queries.
Expecting UNION to merge the same person with different details. UNION removes a row only when every selected column matches. If someone appears with 2 different email addresses, they stay on the list twice. Clean the data first, or select fewer columns.
Putting ORDER BY on the first query. In most databases this gives an error or doesn't sort the result. Put 1 ORDER BY at the very end, after the last SELECT, and it sorts the whole stacked list.

When a business analyst uses this

Before a requirements workshop, a business analyst needs 1 invite list from the stakeholder register and the RAID log owners. Selecting name and email from each and joining them with UNION gives 1 list where people on both lists appear only once, so nobody gets 2 invites.

Questions people ask

What is the difference between UNION and UNION ALL in SQL?

Both stack the rows of 2 queries into 1 result. UNION ALL keeps every row, including duplicates. UNION keeps only 1 copy of any row that appears more than once.

Is UNION ALL faster than UNION?

Usually, yes. UNION has to compare rows to find and remove duplicates, and UNION ALL skips that step. Use UNION ALL when you know there are no duplicates or you want to keep them.

Do UNION and UNION ALL work the same in SQL Server and Oracle?

Yes. Both support UNION and UNION ALL with the same meaning, and both need the same number of columns, with matching types, in each query.

More in SQL for Business Analysts

SQL for Business Analysts: The 5 Queries You Actually Need

SQL SELECT Basics: Get Data Out of a Table (Beginner Tutorial)

SQL WHERE Clause: Filter Rows Like an Analyst

SQL INNER JOIN Explained With a Simple Example