EXCEPT ALL operator in SQL

The EXCEPT ALL operator in SQL is used to return all records from the first query that are not found in the second query, including duplicates. Unlike the EXCEPT operator, which removes duplicate rows, EXCEPT ALL preserves duplicate records in the results.

This operator is particularly useful when you need to analyze the exact differences between two datasets, even if some rows are repeated.

If we look at it mathematically, the EXCEPT ALL operator is a difference between two sets of values. This and a couple of other operators (UNION) come from a branch of math called “set theory”.

Syntax

The syntax for the EXCEPT ALL operator involves two SELECT statements. The first SELECT statement defines the primary dataset, and the second SELECT statement defines the dataset you want to exclude from the results.

Here’s a simple query to demonstrate the EXCEPT ALL operator:

SELECT id AS user_id
FROM users
EXCEPT ALL
SELECT user_id
FROM profiles

It’s important that each SELECT statement has the same number of columns and compatible data types to avoid errors.

This query gives us user_id-s of all library entries (every time a user starts reading a book a books_users record is created) for free users.

Understanding how EXCEPT ALL works

Let’s build some trivial data sets with CTE-s and see how EXCEPT ALL works in details.

WITH table1 AS (
  SELECT 1 AS id
  UNION ALL
  SELECT 1
  UNION ALL
  SELECT 2
), table2 AS (
  SELECT 1 AS id
  UNION ALL
  SELECT 2
)

SELECT *
FROM table1
EXCEPT ALL
SELECT *
FROM table2

Note how we’ve used another set operator UNION ALL to combine multiple constant rows into a temporary table. This is a perfect way sandbox approach to learn any SQL operation.

As you can see, we got the following result set:

It shows us that EXCEPT ALL removes one record from the table1 for every matching record from the table2. All other duplicated records will make it to the final result set.

EXCEPT ALL vs JOIN-s

As you can see, the EXCEPT ALL operator is quite tricky. It’s hard to use it for real analysis, because columns in both sets must match.

It’s more intuitive to use INNER JOIN or LEFT JOIN to implement subtraction between tables.

If we want to see all users without profiles, we can easily do this via a LEFT JOIN and pull any columns into the final result set:

SELECT
  u.id,
  u.email
FROM users u
LEFT JOIN profiles p
  ON u.id = p.user_id
WHERE
  p.id IS NULL

See also

EXCEPT operator in SQL

UNION operator in SQL

UNION ALL operator in SQL