SQL Interview Question: UNION vs UNION ALL

Short answer: both stack the results of two queries. UNION removes duplicate rows; UNION ALL keeps every row. Because removing duplicates needs a sort or hash step, UNION ALL is faster.

-- Emails from two sources
SELECT email FROM newsletter_subscribers
UNION
SELECT email FROM customers;
-- each email appears once

SELECT email FROM newsletter_subscribers
UNION ALL
SELECT email FROM customers;
-- an email in both tables appears twice

Rules for both

• Each query must return the same number of columns.
• Corresponding columns must have compatible types.
• Column names come from the first query.
• ORDER BY applies to the combined result and goes at the end.

SELECT id, created_at, 'order' AS kind FROM orders WHERE customer_id = 42
UNION ALL
SELECT id, created_at, 'ticket' AS kind FROM support_tickets WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;   -- a combined activity feed

Which to use

• Default to UNION ALL when duplicates are impossible (different sources, a kind column) or acceptable.
• Use UNION only when you genuinely need distinct results.
• Using UNION "just to be safe" costs performance on large results.

Related set operators

• INTERSECT - rows present in both results.
• EXCEPT (MINUS in Oracle) - rows in the first result but not the second.