SQL · Theory

Union vs Union All

Theory

297/439

Union vs Union All

UNION runs DISTINCT on the stacked result. UNION ALL concatenates as-is. If you know there are no duplicates, UNION ALL is the right default for performance.

Same number of columns. First SELECT names the output columns.

Trap — UNION ALL then DISTINCT on the outside without thinking — you paid twice.

On the example next to this theory — Union vs Union All — uNION merges result sets and drops duplicate rows.

Exam tip

Need unique names → UNION. Two year dumps → UNION ALL.

Union vs Union All — sample query

CREATE TABLE a (id INTEGER, label TEXT);
CREATE TABLE b (id INTEGER, label TEXT);
INSERT INTO a VALUES (1, 'x'), (2, 'y');
INSERT INTO b VALUES (2, 'y'), (3, 'z');
SELECT label FROM a
UNION
SELECT label FROM b;

Union vs Union All — uNION merges result sets and drops duplicate rows.

Short notes

DefUNION = unique rows. UNION ALL = all rows.
RulePrefer UNION ALL when duplicates cannot happen.
TrapUNION on huge identical lists for no reason.

CREATE TABLE a (id INTEGER, label TEXT); CREATE TABLE b (id INTEGER, label TEXT); INSERT INTO a VALUES (1, 'x'), (2, 'y'); INSERT INTO b VALUES (2, 'y'), (3, 'z'); SELECT label FROM a UNION SELECT label FROM b;

Union vs Union All

Union vs Union All — sample query

Short notes

Questions

Union vs Union All

Union vs Union All — sample query

Short notes

Questions