EXCEPT
EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.
Overview
EXCEPT (called MINUS in some databases, though not in DuckDB) is a set operation that returns rows from the first SELECT query that don't appear anywhere in the second query's results. As with UNION and INTERSECT, both queries must return the same number of columns with compatible types.
Copy code
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM churned_customers;
-- customers who have NOT churned
EXCEPT vs NOT IN / NOT EXISTS
EXCEPT compares entire rows and is often more concise than an equivalent NOT IN or NOT EXISTS subquery, especially when comparing on multiple columns at once:
Copy code
SELECT customer_id, email FROM crm_contacts
EXCEPT
SELECT customer_id, email FROM suppressed_contacts;
Unlike NOT IN, EXCEPT isn't vulnerable to the NULL-poisoning trap where a single NULL in the subquery silently zeroes out the whole result.
EXCEPT vs EXCEPT ALL
Plain EXCEPT uses set semantics: duplicates are removed from the output, and a row from the first query is excluded entirely if it matches any row in the second query, regardless of how many times it appears in either side. DuckDB also supports EXCEPT ALL, which uses bag semantics -- it removes only as many matching duplicate rows as appear in the second query, keeping the remainder:
Copy code
-- table_a has 'x' three times, table_b has 'x' once
SELECT val FROM table_a
EXCEPT ALL
SELECT val FROM table_b;
-- returns 'x' twice
This ALL variant is part of the ANSI SQL standard but isn't implemented everywhere; DuckDB supports it alongside UNION ALL and INTERSECT ALL for full bag-semantics set operations.
Order and column names
Order in EXCEPT matters -- A EXCEPT B is not the same as B EXCEPT A. Column names in the result come from the first query.
Related terms
INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them.
UNION and UNION ALL →UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates.
EXISTS →EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.
IN operator →The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.
DISTINCT →DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.
WHERE clause →The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
FAQS
EXCEPT compares entire rows between two queries and isn't affected by NULLs in the second query the way NOT IN is, where a single NULL in the subquery can cause it to unexpectedly return zero rows.
Yes -- EXCEPT ALL uses bag semantics, removing only as many duplicate matching rows as appear in the second query rather than eliminating all instances of a matching value.
They're the same operation under different names -- some databases like Oracle use MINUS, while DuckDB, PostgreSQL, and the ANSI standard use EXCEPT.
