UNION

The UNION operator combines the result sets of two or more SELECT queries into a single result set. By default, UNION removes duplicate rows. Use UNION ALL to retain duplicates — it is faster because no deduplication step is performed.

UNION syntax and rules

SELECT select_list FROM A
UNION
SELECT select_list FROM B;

Both queries must follow these rules:

• The same number of columns in the same order.
• Compatible data types for each corresponding column pair.
• ORDER BY, if needed, goes after the final query only.

To sort the combined result:

SELECT select_list FROM A
UNION
SELECT select_list FROM B
ORDER BY sort_expression;

UNION vs UNION ALL examples

Given two tables, top_rated_films and most_popular_films, both sharing The Godfather and The Dark Knight:

-- UNION removes duplicates (6 rows instead of 8)
SELECT * FROM top_rated_films
UNION
SELECT * FROM most_popular_films;

          title          | release_year
-------------------------+--------------
 An American Pickle      |         2020
 The Dark Knight         |         2008
 Greyhound               |         2020
 The Shawshank Redemption|         1994
 The Godfather           |         1972
 12 Angry Men            |         1957
(6 rows)

-- UNION ALL keeps all rows including duplicates (8 rows)
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films;

          title          | release_year
-------------------------+--------------
 The Shawshank Redemption|         1994
 The Godfather           |         1972
 The Dark Knight         |         2008
 12 Angry Men            |         1957
 An American Pickle      |         2020
 The Godfather           |         1972
 The Dark Knight         |         2008
 Greyhound               |         2020
(8 rows)

UNION ALL with ORDER BY

To sort the combined result, add ORDER BY after the last query:

SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films
ORDER BY title;

          title          | release_year
-------------------------+--------------
 12 Angry Men            |         1957
 An American Pickle      |         2020
 Greyhound               |         2020
 The Dark Knight         |         2008
 The Dark Knight         |         2008
 The Godfather           |         1972
 The Godfather           |         1972
 The Shawshank Redemption|         1994
(8 rows)

Key tips for using UNION

• Prefer UNION ALL over UNION when you know the component queries return distinct rows — it avoids an expensive sort-and-deduplicate step on large result sets.
• Column names in the output come from the first SELECT. Use aliases in the first query to control output column names.
• You cannot place ORDER BY, LIMIT, or FETCH inside individual component queries — only after the final query in the chain.
• When combining queries from different schemas or sources, ensure data types are compatible or use explicit CAST to avoid type mismatch errors.
• For comparing which rows appear in both result sets, use INTERSECT instead of UNION.

Reference: PostgreSQL documentation — Combining Queries (UNION, INTERSECT, EXCEPT).