Union

Part of the Fundamentals section of Coddy's SQL journey — lesson 48 of 72.

Unions are different from joins. Joins are using conditions to combine tables but unions just add two tables on top of the other. To use UNION we will write:

SELECT col1, col2, ... FROM table1
UNION
SELECT col1, col2, ... FROM table2

Both selects must obey the following rules:

The number of fields should be equal
Order is important

The columns in the same place must match the data types

For example, let's assume we have the following tables:

germany_people

id	name
1	Lena
2	Leonie

england_people

id	name
1	George
2	Lena

Problem: We want to make one big table of all the names we have.

SELECT name from germany_people
UNION
SELECT name from england_people

Result:

name

Lena

Leonie

George

UNION returns only distinct values while UNION ALL return all of the records as-is:

SELECT name from germany_people
UNION ALL
SELECT name from england_people

Result:

name

Lena

Leonie

George

Lena

You can also combine UNION ALL with GROUP BY, aggregate functions, and ORDER BY by wrapping the union in a subquery. For example, to count how many times each name appears across both tables:

SELECT name, COUNT(*) AS total
FROM (
    SELECT name FROM germany_people
    UNION ALL
    SELECT name FROM england_people
) AS all_people
GROUP BY name
ORDER BY total DESC

Result:

name	total
Lena	2
Leonie	1
George	1

Here, UNION ALL keeps all rows (including duplicates) so the COUNT reflects the true number of occurrences. The outer query then groups and sorts the combined result.

Challenge

Easy

Available tables and columns:
sales_2009: product_id, quantity_sold
sales_2010: product_id, quantity_sold
sales_2011: product_id, quantity_sold

There are 3 sales tables.

Find the sum of sales for each product of all tables together.

The result should include the product_id and the total sales.

Name this column total_sales.

Sort the results by the total sales in descending order.

Cheat sheet

UNION combines tables by stacking them vertically. Unlike joins, unions don't use conditions - they simply add tables on top of each other.

Basic UNION syntax:

SELECT col1, col2, ... FROM table1
UNION
SELECT col1, col2, ... FROM table2

UNION rules:

Number of fields must be equal
Order is important
Columns in the same position must match data types

UNION vs UNION ALL:

UNION returns only distinct values
UNION ALL returns all records including duplicates

SELECT name FROM germany_people
UNION
SELECT name FROM england_people

SELECT name FROM germany_people
UNION ALL
SELECT name FROM england_people

You can use UNION ALL inside a subquery and then apply GROUP BY, ORDER BY, and aggregate functions on the combined result:

SELECT name, COUNT(*) AS total
FROM (
  SELECT name FROM germany_people
  UNION ALL
  SELECT name FROM england_people
) AS all_people
GROUP BY name
ORDER BY total DESC

This counts how many times each name appears across both tables, including duplicates.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Union

Cheat sheet

All lessons in Fundamentals