UNION, INTERSECT, and EXCEPT: Combining Query Results

A set operation stacks the results of two queries. UNION returns the rows found in either, INTERSECT the rows found in both, and EXCEPT the rows in the first but not the second. The examples use two sign-up lists from two campaigns, with a duplicate and a missing email in them.

-- setup.sql
CREATE TABLE spring (email TEXT);   -- who signed up in the spring campaign
CREATE TABLE autumn (email TEXT);   -- who signed up in the autumn campaign
INSERT INTO spring VALUES ('ana@x.io'), ('ben@x.io'), ('ben@x.io'), ('cai@x.io'), (NULL);
INSERT INTO autumn VALUES ('ben@x.io'), ('cai@x.io'), ('dee@x.io'), (NULL);

$ psql -d sqldemo -f setup.sql
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 4

ben@x.io signed up twice in the spring, and each campaign has one row with no email.

UNION vs UNION ALL

Both stack the rows; only one removes duplicates.

-- union.sql
SELECT count(*) AS union_rows     FROM (SELECT email FROM spring UNION     SELECT email FROM autumn) u;
SELECT count(*) AS union_all_rows FROM (SELECT email FROM spring UNION ALL SELECT email FROM autumn) u;
SELECT email FROM spring
UNION
SELECT email FROM autumn
ORDER BY email;

$ psql -d sqldemo -f union.sql
 union_rows 
------------
          5
(1 row)

 union_all_rows 
----------------
              9
(1 row)

  email   
----------
 ana@x.io
 ben@x.io
 cai@x.io
 dee@x.io
 
(5 rows)

The inputs have 5 and 4 rows. UNION ALL keeps all 9. UNION keeps 5, because it removes every duplicate: the second ben@x.io from the spring list, plus the copies of ben@x.io, cai@x.io and the missing email that appear in both lists. The empty line at the bottom of the sorted list is that missing email, a NULL, which PostgreSQL sorts last. Removing duplicates means sorting or hashing the whole result, so prefer UNION ALL whenever duplicates can’t occur.

INTERSECT, EXCEPT and EXCEPT ALL

INTERSECT keeps rows found in both queries, and EXCEPT keeps rows of the first query that the second doesn’t have.

-- intersect.sql
SELECT email FROM spring INTERSECT SELECT email FROM autumn ORDER BY email;
SELECT email FROM spring EXCEPT    SELECT email FROM autumn ORDER BY email;
SELECT email FROM spring EXCEPT ALL SELECT email FROM autumn ORDER BY email;

$ psql -d sqldemo -f intersect.sql
  email   
----------
 ben@x.io
 cai@x.io
 
(3 rows)

  email   
----------
 ana@x.io
(1 row)

  email   
----------
 ana@x.io
 ben@x.io
(2 rows)

Three people are in both lists: ben@x.io, cai@x.io and the NULL. Only ana@x.io signed up in the spring and never in the autumn. EXCEPT ALL works on counts instead of distinct values: the spring list has ben@x.io twice and the autumn list once, so one copy survives. Plain EXCEPT removes duplicates first, so it never shows that second sign-up.

NULLs Match in Set Operations

The NULL in the INTERSECT result is not a mistake. Set operations treat two NULLs as the same value, which a join on = never does.

-- nulls.sql
SELECT count(*) AS joined_on_equals
FROM spring s JOIN autumn a ON s.email = a.email
WHERE s.email IS NULL;
SELECT count(*) AS null_in_intersect
FROM (SELECT email FROM spring INTERSECT SELECT email FROM autumn) i
WHERE email IS NULL;

$ psql -d sqldemo -f nulls.sql
 joined_on_equals 
------------------
                0
(1 row)

 null_in_intersect 
-------------------
                 1
(1 row)

NULL = NULL is unknown, so the join finds no match for the missing emails. INTERSECT compares rows the way IS NOT DISTINCT FROM does, so the two NULLs are equal and one comes through. The same rule makes UNION collapse the two NULLs into a single row.

Both Sides Must Line Up

Each query in a set operation must return the same number of columns, with types that can be matched. Columns are matched by position, not by name.

-- mismatch.sql
SELECT email FROM spring
UNION
SELECT email, 'autumn' FROM autumn;
SELECT email FROM spring
UNION
SELECT 42;
SELECT email, 'spring' AS campaign FROM spring
UNION ALL
SELECT email, 'autumn' FROM autumn
ORDER BY campaign, email;

$ psql -d sqldemo -f mismatch.sql
psql:mismatch.sql:4: ERROR:  each UNION query must have the same number of columns
LINE 3: SELECT email, 'autumn' FROM autumn;
               ^
psql:mismatch.sql:7: ERROR:  UNION types text and integer cannot be matched
LINE 3: SELECT 42;
               ^
  email   | campaign 
----------+----------
 ben@x.io | autumn
 cai@x.io | autumn
 dee@x.io | autumn
          | autumn
 ana@x.io | spring
 ben@x.io | spring
 ben@x.io | spring
 cai@x.io | spring
          | spring
(9 rows)

The first statement has one column on the left and two on the right. The second puts an integer under a text column. The third works: a constant labels where each row came from, and the column names in the result come from the first query, which is why ORDER BY campaign can use the name campaign.

ORDER BY Sorts the Whole Result

An ORDER BY belongs to the combined result, so it can only go after the last query.

-- orderby.sql
SELECT email FROM spring ORDER BY email
UNION
SELECT email FROM autumn;
(SELECT email FROM spring ORDER BY email LIMIT 1)
UNION ALL
(SELECT email FROM autumn ORDER BY email LIMIT 1);

$ psql -d sqldemo -f orderby.sql
psql:orderby.sql:4: ERROR:  syntax error at or near "UNION"
LINE 2: UNION
        ^
  email   
----------
 ana@x.io
 ben@x.io
(2 rows)

PostgreSQL reads ORDER BY email as the end of the first query, then finds UNION where it expected the end of the statement. Wrapping each branch in parentheses makes it a complete query of its own, so it can have its own ORDER BY and LIMIT: this returns the first email from each list.

EXCEPT vs NOT EXISTS vs NOT IN

“Rows in the spring list that aren’t in the autumn list” can be written three ways. On data with NULLs, they give three different answers.

-- notin.sql
SELECT 'NOT IN' AS method, count(*) FROM spring
WHERE email NOT IN (SELECT email FROM autumn)
UNION ALL
SELECT 'NOT EXISTS', count(*) FROM spring s
WHERE NOT EXISTS (SELECT 1 FROM autumn a WHERE a.email = s.email)
UNION ALL
SELECT 'EXCEPT', count(*) FROM (SELECT email FROM spring EXCEPT SELECT email FROM autumn) e;

$ psql -d sqldemo -f notin.sql
   method   | count 
------------+-------
 NOT IN     |     0
 NOT EXISTS |     2
 EXCEPT     |     1
(3 rows)

• NOT IN returns 0. The autumn list contains a NULL, so for every spring row the condition includes email <> NULL, which is unknown. NOT IN never succeeds once its list has a NULL, a classic silent bug.
• NOT EXISTS returns 2: ana@x.io and the spring row with no email. The correlated a.email = s.email is never true for a NULL, so that row has no match and is kept.
• EXCEPT returns 1: just ana@x.io. The two NULLs match, and duplicates are removed.

None of them is wrong; they answer slightly different questions. Pick EXCEPT for “distinct values in one list but not the other”, NOT EXISTS for “rows with no matching row”, and avoid NOT IN against a column that can be NULL.

Practice

1. Count the distinct people who signed up in either campaign, ignoring the rows with no email.

-- p1.sql
SELECT count(*) AS people
FROM (SELECT email FROM spring UNION SELECT email FROM autumn) u
WHERE email IS NOT NULL;

$ psql -d sqldemo -f p1.sql
 people 
--------
      4
(1 row)

2. Find who signed up in the autumn but not the spring.

-- p2.sql
SELECT email FROM autumn EXCEPT SELECT email FROM spring;

$ psql -d sqldemo -f p2.sql
  email   
----------
 dee@x.io
(1 row)

3. INTERSECT ALL keeps a row as many times as it appears in both inputs. Predict what it returns for these lists, then run it.

-- p3.sql
SELECT email FROM spring INTERSECT ALL SELECT email FROM autumn ORDER BY email;

$ psql -d sqldemo -f p3.sql
  email   
----------
 ben@x.io
 cai@x.io
 
(3 rows)

4. Make the NOT IN version return the same count as EXCEPT for the non-NULL emails, by changing only the subquery.

-- p4.sql
SELECT count(*) FROM spring
WHERE email NOT IN (SELECT email FROM autumn WHERE email IS NOT NULL);

$ psql -d sqldemo -f p4.sql
 count 
-------
     1
(1 row)