UNION, INTERSECT & EXCEPT

The results of 2 queries can be combined using the set operations union, intersection, and difference:

query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2

The queries must be “union compatible”:

• both return the same number of columns

• corresponding columns have compatible data types.

mydb=> CREATE TABLE fruits (name text, price money);
CREATE TABLE
mydb=> INSERT INTO fruits VALUES ('Apples', 25), ('Tomatoes', 10), ('Pears', 16);
INSERT 0 3
mydb=> CREATE TABLE vegetables (name text, price money);
CREATE TABLE
mydb=> INSERT INTO vegetables VALUES ('Spinach', 5), ('Carrots', 4), ('Tomatoes', 10);
INSERT 0 3

UNION

Appends the result of query2 to the result of query 1 (order of returned rows still not guaranteed). Eliminates duplicate rows (like DISTINCT), unless UNION ALL is used.

mydb=> SELECT name, price FROM fruits
mydb->   UNION SELECT name, price FROM vegetables;
   name   | price  
----------+--------
 Apples   | $25.00
 Carrots  |  $4.00
 Pears    | $16.00
 Spinach  |  $5.00
 Tomatoes | $10.00
(5 rows)

mydb=> SELECT name, price FROM fruits
mydb->   UNION ALL SELECT name, price FROM vegetables;
   name   | price  
----------+--------
 Apples   | $25.00
 Tomatoes | $10.00
 Pears    | $16.00
 Spinach  |  $5.00
 Carrots  |  $4.00
 Tomatoes | $10.00
(6 rows)

INTERSECT

Returns rows present in both query1 and query2 results. Eliminates duplicate rows, unless INTERSECT ALL is used.

mydb=> SELECT name, price FROM fruits
mydb->   INTERSECT SELECT name, price FROM vegetables;
   name   | price  
----------+--------
 Tomatoes | $10.00
(1 row)

EXCEPT

Returns rows present in the result of query1 but not that of query2 (aka difference). Eliminates duplicates, unless EXCEPT ALL is used.

mydb=> SELECT name, price FROM fruits
mydb->   EXCEPT SELECT name, price FROM vegetables;
  name  | price  
--------+--------
 Apples | $25.00
 Pears  | $16.00
(2 rows)

Note

You might need to surround individual queries with parentheses e.g. if any of the queries has a LIMIT clause.

mydb=> SELECT * FROM fruits ORDER BY price LIMIT 2
mydb->   UNION SELECT * FROM vegetables;
ERROR:  syntax error at or near "UNION"
LINE 2:   UNION SELECT * FROM vegetables;
          ^

mydb=> (SELECT * FROM fruits ORDER BY price LIMIT 2)
mydb->   UNION SELECT * FROM vegetables;
   name   | price  
----------+--------
 Carrots  |  $4.00
 Pears    | $16.00
 Spinach  |  $5.00
 Tomatoes | $10.00
(4 rows)

Set operations can be combined. You can use () to control evaluation order:

query1 UNION query2 EXCEPT query3
-- is equivalent to
(query1 UNION query2) EXCEPT query3

Important

Without parentheses, UNION and EXCEPT associate left-to-right, but INTERSECT binds more tightly than these 2:

query1 UNION query2 INTERSECT query3
-- is equivalent to
query1 UNION (query2 INTERSECT query3)