SQL Server Interview Question #80

What are INTERSECT and EXCEPT?

Joins, Subqueries, CTEs & Set Operators Mid-Level Intermediate

Detailed Explanation

INTERSECT returns distinct rows that appear in both query results. EXCEPT returns distinct rows from the first query that do not appear in the second query.

Like UNION, the participating SELECT statements must return the same number of columns with compatible data types. These operators are useful for comparison, reconciliation, and data-validation tasks.

The order matters for EXCEPT: A EXCEPT B is not the same as B EXCEPT A.

Code Example

-- Emails appearing in both sets
SELECT Email
FROM dbo.Customers
INTERSECT
SELECT Email
FROM dbo.NewsletterSubscribers;

-- Customer emails not in subscribers
SELECT Email
FROM dbo.Customers
EXCEPT
SELECT Email
FROM dbo.NewsletterSubscribers;