UNION

Lesson 14 of 21 · Part 3 · More than one table · about 15 minutes

JOIN puts two tables side by side, so rows get wider. UNION stacks them: it puts the results of two queries one above the other, so you get more rows and the same columns.

Stacking two results

The shop wants to send a notice to its customers in London and to all its employees, so it needs a list of names first:

main.sql

1SELECT name FROM customers WHERE city = 'London'2UNION3SELECT name FROM employees;

Output

name
Ana Silva
Chloe Martin
Hana Sato
Grace Lee
Omar Haddad
Lucia Romero
Tom Becker
Mei Tanaka
Sam Wilson

9 rows

The first query gives 3 customers, the second 6 employees, and UNION stacks them into one table of 9 rows. Two queries that would each give their own result now give just one.

Repeated rows appear once

Both queries below give customer numbers: one gives the customers who ordered in September, the other the customers in London.

main.sql

1SELECT customer_id FROM orders WHERE order_date < '2025-10-01'2UNION3SELECT id FROM customers WHERE city = 'London'4ORDER BY customer_id;

Output

customer_id
1
2
3
4
8

5 rows

There were 4 orders in September, two of them from customer 1; the customers in London are 1, 3 and 8. Stacked, that would be 7 numbers, but UNION removes every repeat and leaves 5. It removes repeats between the two sides and within one side as well.

Two more things:

The result's column names come from the first query: here it's customer_id; the id in the second one doesn't count.
ORDER BY is written once, at the very end, and sorts the whole stacked table.

UNION ALL

UNION ALL removes nothing: every row from both sides stays.

Guess first

How many rows does the result have?

1SELECT customer_id FROM orders WHERE order_date < '2025-10-01'2UNION ALL3SELECT id FROM customers WHERE city = 'London'4ORDER BY customer_id;

UNION ALL removes nothing: the first query has 4 rows (customer 1 twice) and the second has 3, so 7 in all. Customer 1 shows up 3 times.

main.sql

1SELECT customer_id FROM orders WHERE order_date < '2025-10-01'2UNION ALL3SELECT id FROM customers WHERE city = 'London'4ORDER BY customer_id;

Output

customer_id
1
1
1
2
3
4
8

7 rows

Removing repeats means comparing every row, which gets slow on big tables. When you know the two sides can't repeat, or you want the repeats kept, use UNION ALL.

The same number of columns

If the two queries have a different number of columns, they can't be stacked:

main.sql

1SELECT name, city FROM customers2UNION3SELECT name FROM employees;

Output

ERROR 1222 (21000): The used SELECT statements have a different number of columns

ERROR 1222 says the two SELECTs have different numbers of columns. The employees table has no city, so you can fill in a NULL in the second one: SELECT name, NULL FROM employees.

Columns are matched by position, not by name. Write them in the wrong order and MySQL raises no error; it just puts a city under the names:

main.sql

1SELECT name, city FROM customers WHERE id = 12UNION3SELECT city, name FROM customers WHERE id = 2;

Output

name	city
Ana Silva	London
Paris	Ben Okafor

2 rows

So the columns in the same position on both sides should hold the same kind of thing.

Making a timeline

UNION is often used to put different kinds of events into one table. Below, "new customer signed up" and "order placed" go together, sorted by date into a timeline. Each query adds a column of fixed text that says what kind of event the row is:

main.sql

1SELECT joined AS day, name, 'signed up' AS event2FROM customers3WHERE joined >= '2025-08-01'4UNION ALL5SELECT o.order_date, c.name, 'ordered'6FROM orders AS o7JOIN customers AS c ON c.id = o.customer_id8WHERE o.order_date < '2025-10-01'9ORDER BY day;

Output

day	name	event
2025-08-30	Ivan Petrov	signed up
2025-09-02	Ana Silva	ordered
2025-09-05	Ben Okafor	ordered
2025-09-14	Julia Costa	signed up
2025-09-18	Ana Silva	ordered
2025-09-21	David Kim	ordered

6 rows

The column names day and event are given in the first query; the second needs no aliases. Each query can have its own JOIN and WHERE, and ORDER BY day sorts the whole timeline.

Tip: For two conditions on the same table, OR is often simpler. UNION is for when the two sides come from different tables, or are found in very different ways.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Products to watch

List the product numbers the warehouse should watch: products in pending orders, and products with fewer than 20 in stock. One column, product_id, no repeats, smallest first.

It should print

product_id
1
2
3
5
6
8
10

7 rows

exercise1.sql

1SELECT oi.product_id2FROM order_items AS oi3JOIN orders AS o ON o.id = oi.order_id4WHERE o.status = 'pending';

Output

Press Run or Ctrl+Enter

Exercise 2

Fix it: column count

You want one list: the customers in Tokyo plus the employees whose title is "Sales", in a single column name. This query gives an error. Fix it.

It should print

name
David Kim
George Brown
Tom Becker
Mei Tanaka

4 rows

exercise2.sql

1SELECT name, city FROM customers WHERE city = 'Tokyo'2UNION ALL3SELECT name FROM employees WHERE title = 'Sales';

Output

Press Run or Ctrl+Enter

Exercise 3

Customers with missing details

Find customers with missing details: no email, or no orders. Show three columns, id, name and problem, where problem is no email or no orders; a customer missing both appears twice. Order by id, then by problem.

It should print

id	name	problem
3	Chloe Martin	no email
6	Farah Khan	no email
6	Farah Khan	no orders
9	Ivan Petrov	no email
9	Ivan Petrov	no orders
10	Julia Costa	no orders

6 rows

exercise3.sql

1SELECT id, name, 'no email' AS problem2FROM customers3WHERE email IS NULL;

Output

Press Run or Ctrl+Enter

no email comes before no orders because text is sorted letter by letter, and e comes before o.

What you learned

UNION stacks the results of two queries and removes repeated rows; UNION ALL keeps them all, and is faster.
Both sides need the same number of columns, or you get ERROR 1222; columns match by position, not by name.
The result's column names come from the first query; ORDER BY goes at the end and sorts the whole table.
A column of fixed text shows which side each row came from.
For conditions on one table, OR is enough; UNION is for different tables or different ways of finding rows.