EXCEPT and INTERSECT

Explanation

EXCEPT and INTERSECT are set operators that compare two result sets with matching columns:

• A EXCEPT B — rows in A that do not appear in B (the difference)
• A INTERSECT B — only the rows that appear in both A and B (the overlap)

They're the SQL equivalent of set difference and intersection, and they read more naturally than nested NOT IN / EXISTS for many "compare two groups" questions.

Syntax

SELECT col FROM table_a
EXCEPT
SELECT col FROM table_b;
 
SELECT col FROM table_a
INTERSECT
SELECT col FROM table_b;

Interactive Example

Find product categories that have no completed orders (EXCEPT), then find cities that appear among both active and inactive customers (INTERSECT).

Common Mistakes

• Mismatched columns. Both sides must have the same count and order, or the operator fails.
• Forgetting it's distinct. EXCEPT/INTERSECT deduplicate; use the ALL variant if you need duplicates preserved.
• Confusing EXCEPT direction. A EXCEPT B keeps A's extras; B EXCEPT A keeps B's — order matters.

Best Practices

• Use EXCEPT for "in this set but not that one" comparisons — often clearer than NOT IN.
• Use INTERSECT to find shared members across two groups.
• Keep both inputs' column shapes aligned and aliased consistently.

Practice Question

Using INTERSECT, find the category values that appear in both the products table and the set of categories actually ordered in orders (joined to products).

Summary

EXCEPT returns rows in the first set but not the second; INTERSECT returns only shared rows. Both require matching column shapes and deduplicate by default, offering a readable alternative to NOT IN / EXISTS for set comparisons.

FAQ