UNION and UNION ALL

UNION and UNION ALL stack result sets vertically.

Use them when multiple queries should produce one combined output.

Core Concepts

Operator	Duplicate handling
UNION	Removes duplicate rows
UNION ALL	Keeps duplicates

UNION

SELECT name FROM team_a
UNION
SELECT name FROM team_b
ORDER BY name;

name
Ada
Alan
Grace

UNION ALL

SELECT name FROM team_a
UNION ALL
SELECT name FROM team_b
ORDER BY name;

name
Ada
Ada
Alan
Grace

Code Examples

-- Setup source tables.
CREATE TABLE team_a (
  name TEXT NOT NULL
);

CREATE TABLE team_b (
  name TEXT NOT NULL
);

INSERT INTO team_a (name) VALUES ('Ada'), ('Alan');
INSERT INTO team_b (name) VALUES ('Ada'), ('Grace');

Expected output
Two small tables created and populated.

-- Combine with source marker using UNION ALL.
SELECT 'A' AS source, name FROM team_a
UNION ALL
SELECT 'B' AS source, name FROM team_b
ORDER BY source, name;

source	name
A	Ada
A	Alan
B	Ada
B	Grace

SQLite-Specific Nuances

SQLite Nuance

All queries in a SQLite UNION chain must return the same number of columns with compatible types/order.

Common Pitfalls / Best Practices

Pitfall

Using UNION when duplicates are meaningful and should be retained.

Best Practice

Default to UNION ALL unless de-duplication is explicitly required.

Quick Challenge

Combine team_a and team_b names while preserving duplicates.

View Solution

SELECT name FROM team_a
UNION ALL
SELECT name FROM team_b;

name
Ada
Alan
Ada
Grace