UNION

A join puts tables side by side. UNION stacks query results on top of each other:

SELECT name, 'customer' AS type FROM customers
UNION
SELECT first_name || ' ' || last_name, 'staff' FROM employees
ORDER BY name;

That's one list of everybody the shop knows, customers and staff together.

The rules

• Every SELECT must return the same number of columns, with compatible types.
• The column names come from the first SELECT.
• ORDER BY appears once, at the very end, and sorts the combined result.

UNION vs UNION ALL

UNION removes duplicate rows; UNION ALL keeps them all. Which categories have cheap products, or products with plenty in stock?

SELECT category FROM products WHERE price < 50
UNION
SELECT category FROM products WHERE stock > 50;

SELECT category FROM products WHERE price < 50
UNION ALL
SELECT category FROM products WHERE stock > 50;

Two rows versus seven. UNION ALL is also faster, because the database doesn't have to hunt for duplicates, so prefer it whenever duplicates are impossible or you want them.

Building a summary

UNION ALL is handy for stitching summary rows together:

SELECT 'delivered' AS orders, COUNT(*) AS how_many FROM orders WHERE status = 'delivered'
UNION ALL
SELECT 'in progress', COUNT(*) FROM orders WHERE status IN ('shipped', 'processing')
UNION ALL
SELECT 'cancelled', COUNT(*) FROM orders WHERE status = 'cancelled';

INTERSECT: rows in both results

Customers who have a delivered order and an order still processing:

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

EXCEPT: rows in the first result but not the second

Customers who have never ordered:

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

It's the same answer the LEFT JOIN lesson found with IS NULL: SQL often offers several routes to one result. Older versions of Oracle call EXCEPT MINUS, and MySQL only gained INTERSECT and EXCEPT in version 8.0.31.