0Pricing
SQL Interview Prep · Lesson

INTERSECT and EXCEPT for Comparison

Finding common and differing rows between two datasets.

INTERSECT and EXCEPT for Comparison is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Comparison Operators

INTERSECT and EXCEPT are the set operators for comparing two result sets rather than merging them. Interviewers reach for them in questions like "which customers are in both lists" or "which rows are in A but not B".

  • INTERSECT = rows present in both queries.
  • EXCEPT = rows in the first query but not the second.

What INTERSECT Returns

INTERSECT returns only the distinct rows that appear in both result sets. A row must match on every column to count as common.

Like UNION, plain INTERSECT removes duplicates, returning each common row once.

SELECT customer_id FROM orders_2023
INTERSECT
SELECT customer_id FROM orders_2024;
-- customers who ordered in BOTH years

What EXCEPT Returns

EXCEPT (called MINUS in Oracle) returns the distinct rows from the first query that do not appear in the second. It is directional: A EXCEPT B differs from B EXCEPT A.

This is the natural way to find records missing from a second dataset.

SELECT customer_id FROM orders_2023
EXCEPT
SELECT customer_id FROM orders_2024;
-- ordered in 2023 but NOT in 2024 (churned)

EXCEPT Is Not Symmetric

A favorite interview point: EXCEPT is directional. Swapping the two queries answers a different question.

  • A EXCEPT B = in A, not in B.
  • B EXCEPT A = in B, not in A.

INTERSECT, by contrast, is symmetric: A INTERSECT B equals B INTERSECT A.

-- new customers in 2024 (not seen in 2023):
SELECT customer_id FROM orders_2024
EXCEPT
SELECT customer_id FROM orders_2023;

Duplicates and the DISTINCT Default

Standard INTERSECT and EXCEPT operate on distinct rows, like UNION. Duplicate input rows collapse before comparison.

Some databases support INTERSECT ALL and EXCEPT ALL that respect multiplicity, but these are less common. If the interviewer does not say ALL, assume distinct semantics.

SELECT city FROM a
INTERSECT ALL
SELECT city FROM b;
-- multiplicity-aware (Postgres supports this; MySQL 8+ too)

Comparing Whole Rows for Equality

Both operators compare entire rows across all selected columns. Two rows are equal only when every column matches. This makes them perfect for detecting whether two tables hold identical data.

Select the full set of columns you care about so the comparison is meaningful.

SELECT id, name, email FROM prod_users
EXCEPT
SELECT id, name, email FROM staging_users;
-- rows in prod that differ from / are missing in staging

The Two-Way Table Diff Pattern

To check if two tables are identical, run EXCEPT in both directions and combine the differences. If the combined result is empty, the tables match exactly.

This is a classic data-validation interview answer for migration and reconciliation checks.

(SELECT * FROM table_a EXCEPT SELECT * FROM table_b)
UNION ALL
(SELECT * FROM table_b EXCEPT SELECT * FROM table_a);
-- empty result => tables are identical

How NULLs Are Treated

Inside set operations, two NULL values are treated as equal to each other for matching purposes, which is different from the usual NULL = NULL being UNKNOWN.

So a row with a NULL in one column will match another row with NULL in the same position. Interviewers test this because it contradicts ordinary comparison rules.

-- (1, NULL) INTERSECT (1, NULL) -> returns (1, NULL)
SELECT id, region FROM a
INTERSECT
SELECT id, region FROM b;

Operator Precedence Among Set Ops

When mixing operators, INTERSECT typically binds tighter than UNION and EXCEPT in the SQL standard. To avoid ambiguity, wrap branches in parentheses.

Stating that you parenthesize to make evaluation order explicit shows maturity in an interview.

(SELECT id FROM a EXCEPT SELECT id FROM b)
UNION
(SELECT id FROM c);

Choosing INTERSECT/EXCEPT vs Joins

INTERSECT and EXCEPT are concise and compare full rows with built-in dedup. Joins are more flexible (you can return extra columns and choose how to handle duplicates).

Prefer the set operators when the question is purely "which rows are common / missing". Switch to joins when you need columns from both sides or the dialect lacks these operators.

Tying It Together

Summary you can recite: "INTERSECT returns rows in both queries and is symmetric; EXCEPT returns rows in the first but not the second and is directional. Both compare whole rows, treat NULLs as equal, and default to distinct results."

Add the two-way EXCEPT diff trick for the data-reconciliation follow-up and you cover the topic fully.

Quick Check

You want the customers who placed an order in 2023 but did NOT place one in 2024 (churned customers).

Recap

Key takeaways:

  • INTERSECT = rows in both queries; symmetric.
  • EXCEPT (Oracle: MINUS) = rows in the first not the second; directional.
  • Both compare whole rows and default to distinct output.
  • NULLs are treated as equal for matching.
  • Two-way EXCEPT gives a full table diff.

Frequently asked questions

Is the “INTERSECT and EXCEPT for Comparison” lesson free?

Yes — the full text of “INTERSECT and EXCEPT for Comparison” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “INTERSECT and EXCEPT for Comparison”?

Finding common and differing rows between two datasets. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “INTERSECT and EXCEPT for Comparison” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. UNION vs UNION ALL
  2. Column Count and Type Compatibility
  3. INTERSECT and EXCEPT for Comparison
  4. Emulating Set Operations With Joins
← Back to SQL Interview Prep