T-SQL 101: 113 Finding Common Data with INTERSECT

Another interesting operator is INTERSECT. We saw how EXCEPT takes a set of rows and removes any duplicates, and removes any rows that are also contained in a second set of rows.

INTERSECT is similar in the way it works, but it only returns the rows that are common to both row sets.

You could replace an INTERSECT statement with a WHERE EXISTS clause but if you need to deal with NULLable columns, and as the number of columns increases, you can see that INTERSECT becomes quite an elegant solution.

INTERSECT ALL ??

Similar to the way that UNION and UNION ALL work, in ANSI SQL there is a definition of an INTERSECT ALL. Like EXCEPT ALL, this is not implemented in SQL Server. Again, I have no idea why. It seems another 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-10