UNION, INTERSECT and EXCEPT
Joins put tables side by side. Set operators stack the results of whole queries on top of each other: all rows, shared rows, or the rows of one result that the other doesn't have.
Syntax
Order of execution: each SELECT runs on its own → the set operators combine the results → ORDER BY sorts the final result
CodeSELECT column1, column2 FROM table_a WHERE condition UNION | UNION ALL | INTERSECT | EXCEPT SELECT column1, column2 FROM table_b WHERE condition ORDER BY column1;
| Operator | Returns |
|---|---|
UNION | rows from either query, duplicates removed |
UNION ALL | rows from either query, duplicates kept |
INTERSECT | rows that appear in both |
EXCEPT | rows of the first query that aren't in the second (Oracle before 21c calls it MINUS) |
Examples
UNION vs UNION ALL
Products with a 5-star review or a line of 3 or more items.
CodeSELECT product_id FROM reviews WHERE rating = 5 UNION SELECT product_id FROM order_items WHERE quantity >= 3 ORDER BY product_id; -- each product once SELECT product_id FROM reviews WHERE rating = 5 UNION ALL SELECT product_id FROM order_items WHERE quantity >= 3 ORDER BY product_id; -- every matching row, repeats included
INTERSECT
Products that are in both results.
CodeSELECT product_id FROM reviews WHERE rating = 5 INTERSECT SELECT product_id FROM order_items WHERE quantity >= 3 ORDER BY product_id;
EXCEPT and its direction
A EXCEPT B is not B EXCEPT A.
CodeSELECT product_id FROM products EXCEPT SELECT product_id FROM order_items ORDER BY product_id; -- never ordered SELECT product_id FROM order_items EXCEPT SELECT product_id FROM products ORDER BY product_id; -- empty
Column names and sorting
The first query names the columns, and one ORDER BY at the end sorts everything.
CodeSELECT 'customer' AS kind, full_name AS name FROM customers WHERE city = 'Goa' UNION ALL SELECT 'product', product_name FROM products WHERE category_id = 8 ORDER BY kind, name;
Matching columns
Both queries need the same number of columns, with compatible types.
Code-- Fails: 2 columns vs 1 SELECT customer_id, full_name FROM customers UNION SELECT customer_id FROM orders; -- Fails: a number can't stack on text (MySQL converts instead) SELECT customer_id FROM customers UNION SELECT full_name FROM customers;
Order of operators
In PostgreSQL, MySQL and SQL Server, INTERSECT runs first; UNION and EXCEPT run left to
right. Oracle runs all of them left to right. Brackets change the order everywhere.
Code-- (Toys UNION big lines) EXCEPT 5-star: Toys with a 5-star review are removed too SELECT product_id FROM products WHERE category_id = 8 UNION SELECT product_id FROM order_items WHERE quantity >= 3 EXCEPT SELECT product_id FROM reviews WHERE rating = 5 ORDER BY product_id; -- Toys UNION (big lines EXCEPT 5-star): every Toys product stays SELECT product_id FROM products WHERE category_id = 8 UNION (SELECT product_id FROM order_items WHERE quantity >= 3 EXCEPT SELECT product_id FROM reviews WHERE rating = 5) ORDER BY product_id;
Remember
- Same number of columns, in the same order, with compatible types.
UNIONremoves duplicates; useUNION ALLwhen repeats matter.INTERSECTruns beforeUNIONandEXCEPT, which run left to right (Oracle: all left to right).- When you mix operators, add brackets so the order is clear.
Practice
- All Known Cities (Easy)
- Customers Who Ordered in 2026 and Reviewed (Easy)
- Delhi Customers or Silent Buyers (Medium)