PostgreSQL Union

What UNION Does

Unlike a JOIN, which combines columns from related tables side by side, UNION stacks the rows of multiple queries on top of each other. Each SELECT in a UNION must return the same number of columns, and the corresponding columns must have compatible data types. The column names in the final result come from the first SELECT.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

UNION vs UNION ALL

Plain UNION scans the combined result and removes duplicate rows, which requires an internal sort or hash step and therefore costs extra work. UNION ALL skips deduplication entirely and simply concatenates the results, making it noticeably faster when you know duplicates either cannot occur or do not matter.

SELECT product_name FROM current_catalog
UNION ALL
SELECT product_name FROM discontinued_catalog;

Ordering Combined Results

ORDER BY applies to the final combined result, not to an individual SELECT, so it must appear once at the very end of the whole statement and reference the output column names.

SELECT name, 'employee' AS source FROM employees
UNION
SELECT name, 'contractor' AS source FROM contractors
ORDER BY name;

• Every SELECT in a UNION must return the same number of columns.
• Corresponding columns must have compatible, implicitly convertible types.
• UNION removes duplicate rows; UNION ALL keeps every row from every query.
• A single ORDER BY at the end sorts the combined result set.