UNION and UNION ALL

UNION and UNION ALL stack result sets vertically.

Use them when multiple queries should produce one combined output.

Core Concepts

flowchart LR
  A[Query A result] --> C[Set combination]
  B[Query B result] --> C
  C -->|UNION| D[Combined unique rows]
  C -->|UNION ALL| E[Combined rows with duplicates kept]

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