🎓 A1Learner
🔍
0 of 43 lessons completed

SQL Union

A union is a combined result that stacks the rows from two queries. UNION drops duplicate rows, and UNION ALL keeps every row.

Cities from two tables

The customer cities stack with the supplier cities here. Toronto, Austin and Berlin appear in both tables, so each one lands in the result once.

SELECT city FROM customers WHERE city IS NOT NULL
UNION
SELECT city FROM suppliers
ORDER BY city;

Keeping every stacked row

UNION ALL keeps the repeats this time. Austin appears three times, once per customer or supplier row that holds it.

SELECT city FROM customers WHERE city IS NOT NULL
UNION ALL
SELECT city FROM suppliers
ORDER BY city;

Names from two tables

Names stack the same way as cities. No name appears in both tables, so all eleven names stay in the result.

SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;

Example

SELECT city, country FROM customers WHERE city IS NOT NULL
UNION
SELECT city, country FROM suppliers
ORDER BY city;

Key points

  • UNION: a keyword that stacks two results and drops duplicate rows
  • UNION ALL: a form that stacks two results and keeps every row
  • The two queries in a union must return the same number of columns.
  • An ORDER BY at the end sorts the whole combined result.
  • A union stacks the rows of two queries into one result set.

In this example, each side of the union returns two columns, a city and its country. The pairs from customers stack with the pairs from suppliers. Shared pairs such as Toronto with Canada appear once, so six rows remain.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What is the difference between UNION and UNION ALL?