T-SQL 101: 112 Excluding Data with EXCEPT

We saw how UNION and UNION ALL worked in the last T-SQL 101 post. Sometimes you want to work with two (or more) row sets in other ways.

The EXCEPT clause says that I want all the distinct entries in the first row set unless they also exist in the second row set.

In some database engines, this operator is called MINUS, but EXCEPT is the ANSI SQL standard, and that’s what SQL Server uses.

You might think “I could just do this with a WHERE NOT EXISTS clause” and indeed you can. However, as the number of columns in the row sets increases, this gets very messy. And if any of them are nullable, it gets even messier.

EXCEPT ALL ??

Similar to the way that UNION and UNION ALL work, in ANSI SQL there is a definition of an EXCEPT ALL. This is not implemented in SQL Server. I have no idea why. It seems a strange omission.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-09