UNION vs UNION ALL
Overview
If JOINS fuse tables together Horizontally (adding more columns), UNION fuses queries together Vertically (adding more rows). If you have a query returning 100 'Active' users, and a completely separate query returning 50 'Banned' users, UNION allows you to stack the results on top of each other into a single 150-row output. The critical interview difference is UNION vs UNION ALL.
Syntax
-- 1. UNION (Stacks rows, then violently deduplicates them)
SELECT email FROM users
UNION
SELECT email FROM newsletter_subscribers;
-- 2. UNION ALL (Stacks rows instantly, keeps all duplicates)
SELECT log_message FROM server_logs_2025
UNION ALL
SELECT log_message FROM server_logs_2026;
-- Rules for UNIONs:
-- 1. Both queries MUST have the exact same number of columns!
-- 2. The columns MUST have matching data types!
-- 3. ORDER BY can only be applied once, at the absolute bottom.Common Pitfalls
- Using
UNIONwhen you actually meantUNION ALL.UNIONforces the database to run a massive, expensive deduplication algorithm (hashing and sorting every single row) just to ensure no duplicates slipped through. If you know the two datasets don't overlap, or if you don't care about duplicates, ALWAYS useUNION ALLfor a massive performance boost. - Column mismatch errors. If Query A
SELECTs name, age(2 columns) and Query BSELECTs name(1 column), theUNIONwill instantly throw a fatal error. The structural shapes must perfectly match.
Interview Questions
UNION return? How many will UNION ALL return?UNION will return 17 rows (it identifies the 3 duplicates and destroys the copies). UNION ALL will return exactly 20 rows (it just blindly glues them together).
Real-World Example
Building a global search bar that searches through Users, Products, and Articles simultaneously, returning them all in one unified dropdown list.
SELECT id, name, 'User' AS search_type FROM users WHERE name LIKE '%react%'
UNION ALL
SELECT id, title, 'Product' FROM products WHERE title LIKE '%react%'
UNION ALL
SELECT id, headline, 'Article' FROM blog_posts WHERE headline LIKE '%react%'
-- The entire unified stack is then sorted!
ORDER BY name ASC
LIMIT 10;Check Your Knowledge
Test your understanding of UNION vs UNION ALL with these quick questions.