UNION, INTERSECT, EXCEPT and CROSS JOIN
In short: UNION combines the results of two queries, INTERSECT keeps the common rows, EXCEPT removes the rows of the second from the first, and CROSS JOIN returns every pair of rows.
Problem
One list from two places
The shop sends an autumn newsletter to its active customers. Active means anyone who has ordered since 1 September 2026 or written a review since 1 June.
Orders and reviews are in different tables. If you join them with JOIN, only the customers who have both remain. We need the two lists, put one under the other.
The idea
UNION
UNION puts the result of the second query under that of the first and removes the repeats:
11 customers. Each of the two queries is whole, with its own FROM, JOIN and WHERE. The column names come from the first.
UNION ALL keeps the repeats too:
17 rows: five customers appear more than once (with several orders, or with both an order and a review). The newsletter needs UNION, or someone will get the email three times. UNION ALL is for when every row is a separate event and has to be counted.
Try it
A coffee tasting
The shop is inviting everyone who has bought coffee beans (product 17) or written a review of them to a tasting. Show their names, each once.
Solution
SELECT c.name FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE oi.product_id = 17
UNION
SELECT c.name FROM reviews r
JOIN customers c ON c.id = r.customer_id
WHERE r.product_id = 17;Two queries with UNION: the buyers through orders, customers and order_items with oi.product_id = 17, and the reviewers through reviews and customers with r.product_id = 17. Six people.
See it
The columns must match
The two queries give rows for one and the same table, so they must have the same number of columns:
Columns are paired by position, not by name. If the first query returns name, email, the second must also return two columns, and in a sensible order: name under name.
For a customer's whole history, add a column that says where the row comes from. Maria's orders and reviews, by date:
ORDER BY at the end sorts the whole result, not just the second query. The columns are called day and event, as in the first query.
The idea
INTERSECT and EXCEPT
INTERSECT keeps the rows that are in both results. The products that have been ordered and also have a review:
13 products. These are exactly the products with a review, because every product has been ordered (you saw it in the lesson on LEFT JOIN).
EXCEPT keeps the rows of the first result that are not in the second. The products without a review:
12 products. LEFT JOIN reviews … WHERE r.id IS NULL gives the same. EXCEPT reads like a sentence: “all products, minus those with a review”. With EXCEPT the order of the queries matters, with UNION and INTERSECT it does not.
Try it
Asking for a review
Which products have been bought since 1 September 2026 but have no review at all? Their buyers will be asked for one. Show the product names.
Solution
SELECT p.name FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.ordered_at >= '2026-09-01'
EXCEPT
SELECT p.name FROM reviews r
JOIN products p ON p.id = r.product_id;The ones bought since September (order_items, orders, products with o.ordered_at >= '2026-09-01') EXCEPT the products with a review. 10 products, among them the gift card and the banitsa.
The idea
CROSS JOIN
Sometimes you need every pair. The report by city and category must have a row for every city and every subcategory, even where there are no sales. The grid is made with CROSS JOIN: every row of one table with every row of the other, without ON.
6 cities times 6 subcategories, 36 rows. DISTINCT is needed because the city repeats in customers: without it there are 114 rows, one for every customer with a city and every subcategory.
You will be able to fill the grid with the sales (and the zeros) in the module on subqueries. The grid is the first half.
See it
Watch the number of rows
CROSS JOIN multiplies. 60 orders and 152 order lines:
9,120 pairs, almost all of them meaningless. The same happens if you forget ON. In SQLite a JOIN without ON is a CROSS JOIN, with no error:
1,200 rows (60 × 20) instead of 60. PostgreSQL gives an error in this case. If a report suddenly shows many more rows or much more money than expected, check the conditions in ON first.
Exercise
The grid of top categories
The yearly report is by city and top category (“Food”, “Home”). Make the grid: every city (without the empty one) with every top category, each pair once.
Solution
SELECT DISTINCT cu.city, cat.name AS category
FROM customers cu
CROSS JOIN categories cat
WHERE cu.city IS NOT NULL AND cat.parent_id IS NULL;FROM customers cu CROSS JOIN categories cat WHERE cu.city IS NOT NULL AND cat.parent_id IS NULL, with DISTINCT. 6 cities times 2 categories, 12 rows. The top categories are those without a parent.
More precisely, if you are curious
UNION in SQLite often returns the rows sorted, because that is how it removes repeats. This is not promised. Whenever the order matters, write ORDER BY at the end.
INTERSECT and EXCEPT exist in SQLite, PostgreSQL and SQL Server. In Oracle EXCEPT is called MINUS, and newer versions understand EXCEPT too. MySQL has them since version 8.0.31.
Test