Lesson 18 of 26 · see the practice database

UNION and other set operations

A join puts tables side by side, adding columns. A set operation puts results one on top of the other, adding rows. Use it when two queries produce the same kind of list and you want them as one.

UNION

SELECT name, 'customer' AS kind FROM customers WHERE city = 'Delhi'
UNION
SELECT name, 'employee' FROM employees WHERE department = 'Support';

The rows of the first query come out together with the rows of the second, as one result. The column names are taken from the first query.

The rules

1. Both queries must return the same number of columns.
2. Columns in the same position should hold the same kind of data. The database lines columns up by position, not by name.
3. One ORDER BY, at the very end, sorts the combined result.

Break the first rule and you get an error.

SELECT name, city FROM customers
UNION
SELECT name FROM employees;

Error: SELECTs to the left and right of UNION do not have the same number of result columns

The second rule is more dangerous, because breaking it often gives no error. If you list name, city in one query and city, name in the other, the database stacks them happily and you get cities in the name column.

UNION removes repeats, UNION ALL keeps them

UNION removes duplicate rows from the combined result, as DISTINCT does. UNION ALL keeps everything.

SELECT city FROM customers WHERE state = 'Punjab'
UNION
SELECT city FROM customers WHERE name LIKE 'A%';

SELECT city FROM customers WHERE state = 'Punjab'
UNION ALL
SELECT city FROM customers WHERE name LIKE 'A%';

The second result is longer, because a city that both queries produce is listed each time.

Which should you use? UNION ALL is faster, since the database does not have to hunt for repeats, and it never throws away rows you may have wanted. Use it whenever the two lists cannot overlap, or when repeats are correct. Use plain UNION only when you really want each row once.

Sorting the combined result

Put a single ORDER BY after the last query. It applies to everything and uses the column names of the first query.

SELECT name, joined AS since, 'customer' AS kind FROM customers WHERE joined >= '2025-10-01'
UNION ALL
SELECT name, hired, 'employee' FROM employees WHERE hired >= '2023-01-01'
ORDER BY since;

A list of people who arrived recently, customers and staff together, in date order.

A total row under a report

A common use of UNION ALL is to add a grand total beneath a grouped report.

SELECT category, COUNT(*) AS products, SUM(stock) AS stock
FROM products
GROUP BY category
UNION ALL
SELECT 'ALL CATEGORIES', COUNT(*), SUM(stock)
FROM products;

Filling the gaps

When one query lacks a column that the other has, pad it with NULL or a fixed value so that the counts match.

SELECT name, city, NULL AS department FROM customers WHERE id <= 2
UNION ALL
SELECT name, NULL, department FROM employees WHERE id <= 2;

INTERSECT: rows in both

INTERSECT returns only the rows that appear in both results. Which customers have both a delivered order and a pending one?

SELECT customer_id FROM orders WHERE status = 'delivered'
INTERSECT
SELECT customer_id FROM orders WHERE status = 'pending';

EXCEPT: rows in the first but not the second

EXCEPT returns the rows of the first result that are not in the second. Which customers have never ordered?

SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

The order matters here. Swap the two queries and you ask a different question: which orders belong to customers who do not exist? In a healthy database, none.

SELECT customer_id FROM orders
EXCEPT
SELECT id FROM customers;

That makes EXCEPT a handy tool for checking data. Which products have never been in a delivered order?

SELECT id FROM products
EXCEPT
SELECT oi.product_id
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'delivered';

Oracle calls this operator MINUS. MySQL gained INTERSECT and EXCEPT only in version 8.0.31, so on older MySQL servers you write these questions with IN, NOT IN or NOT EXISTS, as in the last lesson.

Using the result further

To join the result of a set operation to another table, or to filter it, wrap it in brackets as a subquery in FROM.

SELECT c.name
FROM customers c
JOIN (
  SELECT id FROM customers
  EXCEPT
  SELECT customer_id FROM orders
) AS never ON never.id = c.id;

The four side by side

UNION or JOIN?

They answer different needs.

• JOIN matches rows of two tables and makes each result row wider. Use it to bring related facts together: an order and its customer.
• UNION stacks two lists and makes the result longer. Use it to merge lists of the same kind: this year's orders and an archive table of last year's.

A good sign that you need UNION is the word "and" between two lists in the question: "all customers and all employees".

UNION or OR?

When both queries read the same table, one query with OR is usually simpler.

SELECT DISTINCT city FROM customers WHERE state = 'Punjab' OR name LIKE 'A%';

That returns the same cities as the first UNION above. Keep UNION for combining different tables or differently built lists.

Try it yourself

SELECT city FROM customers WHERE state = 'Maharashtra' UNION SELECT 'Ludhiana';

SELECT id FROM products EXCEPT SELECT product_id FROM order_items;

SELECT customer_id FROM orders WHERE status = 'delivered' INTERSECT SELECT customer_id FROM orders WHERE status = 'cancelled';

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.