In this post I’ll show what the INTERSECT and EXCEPT operators in T-SQL do, with a couple of examples.
- INTERSECT: returns the rows that two tables or queries with the same shape (same number of columns, compatible types) have in common.
- EXCEPT: returns the rows that are in the first result but not in the second.
Both operators remove duplicate rows from the result and treat NULL values as equal to each other. That’s where they differ from comparisons written with NOT IN or a JOIN.
Sample tables
CREATE TABLE dbo.Table1 (
ProductName varchar(50) NULL,
ProductCategory varchar(50) NULL
);
CREATE TABLE dbo.Table2 (
ProductName varchar(50) NULL,
ProductCategory varchar(50) NULL
);
INSERT INTO dbo.Table1 (ProductName, ProductCategory)
VALUES ('Keyboard', 'Computer'),
('Mouse', 'Computer'),
('Monitor', 'Computer'),
('Printer', 'Computer');
INSERT INTO dbo.Table2 (ProductName, ProductCategory)
VALUES ('Mouse', 'Computer'),
('Printer', 'Computer');INTERSECT
Returns the rows the two result sets share:
SELECT * FROM dbo.Table1
INTERSECT
SELECT * FROM dbo.Table2;Result: Mouse and Printer.
EXCEPT
Returns the rows in the first table that aren’t in the second:
SELECT * FROM dbo.Table1
EXCEPT
SELECT * FROM dbo.Table2;Result: Keyboard and Monitor.
Where does this come in handy?
- Comparing table contents between two environments (test and production)
- Finding which rows were added or went missing before and after an ETL load
- Reconciliation checks between a source system and the data warehouse
Order matters with EXCEPT: A EXCEPT B and B EXCEPT A give different results. To see the differences in both directions at once, combine the two queries with UNION ALL.
Common mistakes
- Mismatched column count or types: Both queries must return the same number of columns with compatible types, or the statement fails.
- Assuming columns match by name: They match by position, not by name. Listing columns explicitly, in the same order, is safer than
SELECT *. - Putting ORDER BY in the first query:
ORDER BYis only allowed once, at the very end, and sorts the combined result. - Forgetting operator precedence: When you mix operators,
INTERSECTis evaluated beforeEXCEPTandUNION. Use parentheses to make your intent explicit.
Frequently asked questions
What is the difference between EXCEPT and NOT EXISTS?
EXCEPT removes duplicates, treats NULL values as equal and compares every column together. NOT EXISTS keeps duplicate rows and only checks the condition you write. Use EXCEPT for full-row comparisons and NOT EXISTS when you are matching on a specific key.
Why does NOT IN sometimes return no rows at all?
If the subquery returns even one NULL, NOT IN returns nothing, because comparing with NULL yields unknown. EXCEPT and NOT EXISTS do not have this trap.
Related posts
- SQL Server Window Functions: Ranking, LAG and Running Totals
- RegEx Comes to T-SQL: The New REGEXP Functions in SQL Server 2025
STAY IN THE LOOP
New guides, sorted by topic in a single email.
Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.