TopolearnMerging similar data from several sources, or finding what is in one set but not another, is a set operation.
Joins combine tables side by side; set operations combine query results vertically. Each takes two queries with the same number of columns, in compatible types, and treats their results as sets of rows:
UNIONreturns rows that appear in either query, removing duplicates.UNION ALLreturns all rows from both, keeping duplicates.INTERSECTreturns rows present in both.EXCEPTreturns rows in the first query but not the second. Oracle traditionally calls itMINUS; MySQL addedINTERSECTandEXCEPTonly in 8.0.31.
Prefer UNION ALL whenever duplicates are impossible or wanted. Plain UNION must sort or hash the whole result to remove duplicates, which is wasted work on large results.
Columns are matched by position, not name, and the output takes its column names from the first query. Lining up the wrong columns gives silently wrong results as long as the types are compatible, so keep the select lists visibly parallel.
ORDER BY and LIMIT apply to the combined result and go at the very end. To sort or limit one branch, wrap it in parentheses.
Unlike =, set operations treat two NULLs as duplicates of each other, the same way DISTINCT does. That makes EXCEPT a convenient NULL-safe way to compare two tables row by row: (SELECT * FROM a EXCEPT SELECT * FROM b) lists rows of a missing from b.
Precedence: INTERSECT binds tighter than UNION and EXCEPT. Use parentheses when you mix them.
Example
-- Customers who ordered in February / March
-- Feb: customers 1, 2 Mar: customers 3, 2
SELECT customer_id FROM orders WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
INTERSECT
SELECT customer_id FROM orders WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01';
-- 2 (ordered in both months)
SELECT customer_id FROM orders WHERE ordered_at >= '2026-02-01' AND ordered_at < '2026-03-01'
EXCEPT
SELECT customer_id FROM orders WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01';
-- 1 (February but not March)
-- Stack two sources into one list; names come from the first query
SELECT name, 'customer' AS kind FROM customers
UNION ALL
SELECT name, 'product' FROM products
ORDER BY kind, name; -- applies to the whole resultRemember
- UNION removes duplicates; UNION ALL keeps them and is cheaper
- INTERSECT = in both; EXCEPT = in the first but not the second
- Columns match by position; names come from the first query
- Set operations treat NULLs as equal, unlike =
Watch out for
- Using UNION where UNION ALL is correct, paying for a needless de-duplication sort.