How to combine multiple queries into a single result set using UNION, INTERSECT, and EXCEPT

PostgreSQL provides three set operators that allow you to compare or combine query result sets. These are UNION, INTERSECT and EXCEPT.

For these examples, we're going to use two tables: one that contains data on inventory we have in stock, and the second that contains data on inventory available from the supplier.  Here's what they look like:

postgres=# SELECT * FROM inventory;

 id | item_code |       item       | quantity |         description

----+-----------+------------------+----------+-----------------------------

  1 | MUGBLUE   | Blue mug         |       18 | A blue mug.

  2 | MUGGREEN  | Green mug        |       12 | A green mug.

  3 | MUGYELLOW | Yellow mug       |        2 | A yellow mug.

  4 | MUGGOLD   | Gold mug limited |       18 | A limited edition gold mug.

(4 rows)





postgres=# SELECT * FROM supplier_inventory;

 id | item_code |    item    | quantity |  description

----+-----------+------------+----------+---------------

  1 | MUGRED    | Red mug    |      288 | A red mug.

  2 | MUGBLUE   | Blue mug   |      182 | A blue mug.

  3 | MUGGREEN  | Green mug  |      225 | A green mug.

  4 | MUGYELLOW | Yellow mug |       90 | A yellow mug.

  5 | MUGGREY   | Grey mug   |       45 | A grey mug.

(5 rows)

Let's see how we can use the UNION, INTERSECT and EXCEPT operators on this data.

UNION

First, we want to see everything that we have in stock as well as everything that the supplier has in stock:

postgres=# SELECT item_code, item, description FROM inventory

           UNION

           SELECT item_code, item, description FROM supplier_inventory;

 item_code |       item       |         description

-----------+------------------+-----------------------------

 MUGYELLOW | Yellow mug       | A yellow mug.

 MUGBLUE   | Blue mug         | A blue mug.

 MUGGOLD   | Gold mug limited | A limited edition gold mug.

 MUGGREY   | Grey mug         | A grey mug.

 MUGRED    | Red mug          | A red mug.

 MUGGREEN  | Green mug        | A green mug.

(6 rows)

As you can see, we used a UNION query to return three columns from each of the tables to get a unified list of all items available. The query returned an item if either we or the supplier have it in stock—if one table lists the item in stock but the other is out of stock, the item still returns as long as one table lists it as in stock.  

Note that both queries have to return the same number of columns/expression results and they also need to be the same data types between both queries for it to combine the results.  For example, you can't have the first SELECT statement returning 3 columns, and the second returning 5, or having the first SELECT statement returning a text then a date column, but the second returning a text then an integer.  They need to match.

Even if an item appeared in both tables, we only get one entry. This is because UNION outputs distinct rows only, so duplicates are excluded. However, we can change that by adding the ALL keyword after UNION, which will show an entry twice if it appears on both sides:

postgres=# SELECT item_code, item, description FROM inventory

           UNION ALL

           SELECT item_code, item, description FROM supplier_inventory;

 item_code |       item       |         description

-----------+------------------+-----------------------------

 MUGBLUE   | Blue mug         | A blue mug.

 MUGGREEN  | Green mug        | A green mug.

 MUGGOLD   | Gold mug limited | A limited edition gold mug.

 MUGYELLOW | Yellow mug       | A yellow mug.

 MUGRED    | Red mug          | A red mug.

 MUGBLUE   | Blue mug         | A blue mug.

 MUGGREEN  | Green mug        | A green mug.

 MUGYELLOW | Yellow mug       | A yellow mug.

 MUGGREY   | Grey mug         | A grey mug.

(9 rows)

This time we get two entries for “MUGBLUE,” for example, because the item appears in both tables.

INTERSECT

Now, let's see what items we have in stock that the supplier also has in stock.  For this, we can use INTERSECT, which will only return results that are present in both tables.

postgres=# SELECT item_code, item, description FROM inventory

           INTERSECT

           SELECT item_code, item, description FROM supplier_inventory;

 item_code |    item    |  description

-----------+------------+---------------

 MUGBLUE   | Blue mug   | A blue mug.

 MUGYELLOW | Yellow mug | A yellow mug.

 MUGGREEN  | Green mug  | A green mug.

(3 rows)

This time, we only get three results. We didn't get “MUGGOLD” because the limited edition gold mug only appears in our own stock, not in the supplier’s. We also don't get “MUGRED” or “MUGGREY” because only the supplier has those in stock, and not us.

We could run a query adding the ALL keyword after INTERSECT, but unlike UNION ALL, this doesn't return rows that appear in both tables. Instead, INTERSECT ALL returns duplicate rows only if they appear within a single table. Since we do not have multiple row entries for items in our stock table, the INTERSECT ALL query returns the same result as INTERSECT.

EXCEPT

Next we want to see what the supplier has in stock that we don't—a useful query, for example, for deciding if there is an item we would like to order. For this, we can use EXCEPT.

postgres=# SELECT item_code, item, description FROM supplier_inventory

           EXCEPT

           SELECT item_code, item, description FROM inventory;

 item_code |   item   | description

-----------+----------+-------------

 MUGGREY   | Grey mug | A grey mug.

 MUGRED    | Red mug  | A red mug.

(2 rows)

You may have noticed that, this time, we specified the supplier_inventory table first. This is because we want everything from that table, except for items that appear in the inventory table. In this case, the order matters.

Again, we could try using EXCEPT ALL, but this only de-duplicates rows in each table.  Since we don't have any such duplicates, we would get the same result as we would with EXCEPT.

​