UNION and UNION ALL in Postgres

The UNION operator is used to combine the result-set of two or more SELECT statements with the same number of columns and same column data types. The columns in each SELECT statement must also be in the same order.

UNION

Syntax:

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

Examples:

I am using the database for all examples. It is available on my Github public repo

UNION Example

SELECT city FROM customers
UNION 
SELECT city FROM suppliers
ORDER BY city;

/Output
city
"Aachen"
"Albuquerque"
"Anchorage"
"Ann Arbor"
...
...
93 rows

If some customers or suppliers have the same city then each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

UNION Example with WHERE

SELECT city FROM customers WHERE country='Germany'
UNION 
SELECT city FROM suppliers WHERE country='Germany'
ORDER BY city;

//Output
city
"Aachen"
"Berlin"
"Brandenburg"
"Cunewalde"
...
...
13 rows

UNION ALL

Syntax:

SELECT column(s) FROM table1
UNION ALL
SELECT column(s) FROM table2;

Examples:

UNION ALL Example

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;

/Output
city
"Aachen"
"Albuquerque"
"Anchorage"
"Ann Arbor"
...
...
120 rows

UNION ALL Example with WHERE

SELECT city FROM customers WHERE country='Germany'
UNION ALL
SELECT city FROM suppliers WHERE country='Germany'
ORDER BY city;

//Output
city
"Aachen"
"Berlin"
"Brandenburg"
"Cunewalde"
...
...
14 rows

Share this post