A LEFT ANTI SEMI JOIN
is a type of join that returns only those distinct rows in the left rowset that have no matching row in the right rowset.
But when using T-SQL in SQL Server, if you try to explicitly use LEFT ANTI SEMI JOIN
in your query, you’ll probably get the following error:
Msg 155, Level 15, State 1, Line 4
'ANTI' is not a recognized join option.
Fortunately, SQL Server includes the EXCEPT
operator, which allows us to perform a LEFT ANTI SEMI JOIN
.
When you use the EXCEPT
operator, it appears in the query execution plan as a LEFT ANTI SEMI JOIN
.
You can also construct a subquery that does the same thing.
Example
Imagine we have two tables; Cats
and Dogs
, and we then run the following query:
SELECT
CatName AS PetName
FROM Cats
EXCEPT
SELECT
DogName AS PetName
FROM Dogs;
This query uses the EXCEPT
operator, and it will appear as a LEFT ANTI SEMI JOIN
in the execution plan.
![Query plan for the EXCEPT operator in SQL Server.](https://database.guide/wp-content/uploads/2020/09/Query-Execution-Plan-for-EXCEPT-Operator-in-SQL-Server-1024x631.jpg)
It’s similar to doing the following:
SELECT
DISTINCT CatName
FROM Cats c
WHERE NOT EXISTS (SELECT DogName FROM Dogs d
WHERE c.CatName = d.DogName);
Here’s the execution plan for that query:
![Query plan for a subquery in SQL Server.](https://database.guide/wp-content/uploads/2020/09/Query-Execution-Plan-for-Subquery-in-SQL-Server-1024x528.jpg)
If you use U-SQL with Azure Data Lake Analytics, you can use the ANTISEMIJOIN
clause to do right and left anti semi joins. That is, you can use LEFT ANTISEMIJOIN
or RIGHT ANTISEMIJOIN
.