UNION combines the rows of two queries and removes duplicates; UNION ALL does the same but keeps duplicates, which is faster since it skips the deduplication step entirely. INTERSECT returns only rows present in both queries' results. EXCEPT (called MINUS in Oracle) returns rows from the first query that don't appear in the second. All three require both queries to select the same number of columns, with compatible types in the same order.
UNION vs UNION ALL
If you already know the two queries can't produce overlapping rows (common when combining results from genuinely distinct tables), UNION ALL is a free performance win over UNION — there's no reason to pay for deduplication that could never find anything to remove.
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;
-- combined list, duplicates removed (someone who's both appears once)
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_subscribers;
-- combined list, duplicates KEPT (faster, no dedup check)INTERSECT: rows in both
This directly answers "which rows appear in both result sets" — often more readable than the equivalent WHERE...IN subquery, though both can express the same logic.
SELECT customer_id FROM orders WHERE order_date > '2026-01-01'
INTERSECT
SELECT customer_id FROM orders WHERE total > 500;
-- customers who BOTH ordered recently AND spent over 500 on at least one orderEXCEPT: rows in the first, not the second
This is the standard way to answer "who's in A but not B" — for example, finding customers who haven't placed an order in the current promotion period.
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders WHERE order_date > '2026-01-01';
-- customers with NO order since the given date
-- Oracle uses MINUS instead of EXCEPT for the identical operation