PostgreSQL UNION

PostgreSQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements. Instead of merging columns horizontally like a JOIN, UNION appends rows vertically.

It stacks the result of one query on top of another.

The Crucial Requirements

To use UNION, there are strict rules that must be followed. Each SELECT statement within the UNION must have the same number of columns.

The columns must also have similar data types in the same order. The column names in the result-set are usually equal to the column names in the first SELECT statement.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Dropping Duplicate Rows

By default, the UNION operator selects only distinct (unique) values. If a row appears in both the first and second query, it will only be listed once in the final result. This behavior is similar to SELECT DISTINCT.

-- Merge a list of cities from Customers and Suppliers, showing only unique cities
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Allowing Duplicate Results

If you want to include all duplicate values, you must use the UNION ALL operator. UNION ALL is significantly faster than UNION because it does not have to perform the extra step of checking for and removing duplicates. Use UNION ALL when you know the combined results will not have duplicates or when duplicates are desired.

-- Merge all cities from both tables, including duplicates
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

Summary

UNION combines result sets vertically. UNION removes duplicates by default. UNION ALL includes duplicates and is faster. The SELECT statements must have the same column structure.