PostgreSQL UNION Operator

UNION

The UNION operator is used to combine the result-set of two or more queries.

The queries in the union must follow these rules:

They must have the same number of columns
The columns must have the same data types
The columns must be in the same order

Example

Combine products and testproducts using the UNION operator:

SELECT product_id, product_name
FROM products
UNION
SELECT testproduct_id, product_name
FROM testproducts
ORDER BY product_id;

Run Example »

UNION vs UNION ALL

With the UNION operator, if some rows in the two queries returns the exact same result, only one row will be listed, because UNION selects only distinct values.

Use UNION ALL to return duplicate values.

Let's make some changes to the queries, so that we have duplicate values in the result:

Example - UNION

SELECT product_id
FROM products
UNION
SELECT testproduct_id
FROM testproducts
ORDER BY product_id;

Run Example »

Example - UNION ALL

SELECT product_id
FROM products
UNION ALL
SELECT testproduct_id
FROM testproducts
ORDER BY product_id;

Run Example »

❮ Previous Next ❯