UNION ​

UNION appends both inputs and removes duplicate complete rows. UNION ALL appends the inputs while retaining every duplicate.

Syntax ​

query UNION [ALL] query
[ORDER BY expression, ...]
[LIMIT count [OFFSET offset]]

Both inputs must return the same number of columns. Result column names come from the first query. Duplicate comparison applies to whole rows; NULL is equal to another NULL for this purpose.

A final ORDER BY or LIMIT applies to the combined result. Set operators at the same query level are evaluated from left to right. Put a nested query in a FROM subquery when a different grouping is needed.

Example ​

Combining two sets of identifiers ​

CREATE TABLE first_set (id INTEGER);
INSERT INTO first_set VALUES (1), (2);

CREATE TABLE second_set (id INTEGER);
INSERT INTO second_set VALUES (2), (3);

SELECT id FROM first_set
UNION
SELECT id FROM second_set
ORDER BY id
FORMAT csv;

1
2
3

Retaining duplicates ​

SELECT 1 AS id
UNION ALL
SELECT 1
FORMAT csv;

1
1

See UNION, INTERSECT and EXCEPT for the other set operations.