How to Do a LEFT ANTI SEMI JOIN in SQL Server

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.

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.

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.