UNION vs UNION ALL

Easy4 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

Two tables hold ids.

Table: full_time_employees

employee_id
1
2
3

Table: interns

intern_id
3
4

Task

Return one list of ids from both tables without duplicates. Then say when you would use UNION ALL instead.

Expected output

person_id
1
2
3
4

Work out your query on your own, then open Answers & Optimization to compare.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

UNION removes duplicates, which needs a sort or hash step.
UNION ALL keeps every row and skips that work, so it is faster.
Both queries need the same number of columns with compatible types. Column names come from the first query.

1Basic

UNION

1SELECT employee_id AS person_id FROM full_time_employees2UNION3SELECT intern_id FROM interns;

Cost: the whole combined result is sorted or hashed to find duplicates

2Optimized

UNION ALL, deduplicate only where needed

1-- Ids can never overlap between the tables? UNION ALL is enough2SELECT employee_id AS person_id FROM full_time_employees3UNION ALL4SELECT intern_id FROM interns;5 6-- Overlap possible, but employee_id is unique inside its own table:7-- remove only the cross-table duplicates with an anti-join8SELECT employee_id AS person_id9FROM full_time_employees10UNION ALL11SELECT i.intern_id12FROM interns i13WHERE NOT EXISTS (SELECT 1 FROM full_time_employees f WHERE f.employee_id = i.intern_id);

Rule: use UNION ALL by default, UNION only when duplicates are possible and unwanted
Add ORDER BY once, at the very end

Spotted a mistake in these answers?

Report a problem