UNION, INTERSECT, EXCEPT (and CROSS JOIN)

So far you have combined data horizontally: JOIN attaches columns from related rows. SQL also offers another family of operators that combines data vertically, treating the results of two SELECTs as sets: UNION, INTERSECT, EXCEPT. We also revisit CROSS JOIN, the cartesian product.

CROSS JOIN: every row with every row

CROSS JOIN has no ON: it produces every possible combination of rows between two tables (cartesian product). If A has n rows and B has m rows, the result has n × m.

-- Tutte le combinazioni cliente × prodotto:
SELECT c.email, p.name
FROM   customers c
CROSS JOIN products p;

12 customers × 16 products = 192 rows. It's rarely used on real data (it's expensive!), but it's useful for:

• generating "all against all" combinations (e.g. a compatibility matrix);
• pairing a table with a small table of constants / parameters.

UNION and UNION ALL

UNION appends the results of two SELECTs with the same number and type of columns:

SELECT email FROM customers WHERE country = 'Italia'
UNION
SELECT email FROM customers WHERE country = 'Francia';

Rules:

• the two SELECTs must have the same number of columns, with compatible types;
• UNION (without ALL) removes duplicates — effectively performing a DISTINCT on the final result;
• UNION ALL keeps duplicates too, and is much faster because it avoids the sort/hash for deduplication.

-- Email di clienti che hanno ordinato O recensito qualcosa, senza duplicati:
SELECT c.email
FROM   customers c JOIN orders  o ON o.customer_id = c.id
UNION
SELECT c.email
FROM   customers c JOIN reviews r ON r.customer_id = c.id;

INTERSECT and EXCEPT

INTERSECT keeps only the rows present in both results; EXCEPT keeps the rows of the first that are not in the second (the set "difference").

-- Clienti che hanno sia ordinato sia recensito:
SELECT c.email FROM customers c JOIN orders  o ON o.customer_id = c.id
INTERSECT
SELECT c.email FROM customers c JOIN reviews r ON r.customer_id = c.id;

-- Clienti che hanno ordinato ma mai recensito:
SELECT c.email FROM customers c JOIN orders  o ON o.customer_id = c.id
EXCEPT
SELECT c.email FROM customers c JOIN reviews r ON r.customer_id = c.id;

INTERSECT and EXCEPT also remove duplicates by default; the INTERSECT ALL / EXCEPT ALL variants do not.

Try it yourself

Review exercise