SQL Server supports the concept of a SEMI JOIN in the many different forms:
- LEFT SEMI JOIN
- LEFT ANTI SEMI JOIN
- RIGHT SEMI JOIN
- RIGHT ANTI SEMI JOIN
The key thing to know about a SEMI JOIN is that it only returns the results from one table. If you use a LEFT SEMI JOIN or LEFT ANTI SEMI JOIN, it only returns data from the LEFT table. The records are filtered based on the contents of the RIGHT table. The inverse is true when a RIGHT SEMI JOIN or RIGHT ANTI SEMI JOIN query is being executed. Only data from the RIGHT table is returned in the query.
When using a LEFT SEMI JOIN the purpose of the RIGHT table is to filter the records returned from the LEFT table. So, a LEFT SEMI JOIN returns data from the left table, and filters out the rows that have one or more matching records in the RIGHT table. The LEFT ANTI SEMI JOIN returns all of the records from the LEFT table having exactly zero matching records in the RIGHT table.
The Semi Join syntax may not be used in TSQL syntax when you write your query. However, if you review your query plan using traditional TSQL syntax, you will see these specific terms demonstrated in the plan. Perhaps this will make more sense by demonstrating in TSQL how to produce a query resulting with SEMI JOIN results.
Lets take a look at a LEFT SEMI JOIN implementation.
SELECT *
FROM TABLE1
WHERE TABLE1.ID IN (SELECT TABLE1__ID FROM TABLE2)
This query returns each row from Table1 where there is one or more corresponding rows in TABLE2. You can get the same data results and query plan with a LEFT SEMI JOIN by using the EXISTS operation instead of IN. It looks like the following:
SELECT *
FROM TABLE1
WHERE EXISTS( (SELECT 1 FROM TABLE2 WHERE TABLE2.TABLE1__ID = TABLE1.ID)
If you wish to have the LEFT ANTI SEMI JOIN operation you would simply preceed the IN or EXISTS operation with the key word NOT. So, you would now return all records from TableA that do not exist in TableB.
So, while you cannot use these unique join syntax methods directly in your TSQL queries, you can get the same results using TSQL methods that go WAY Back many versions.
If you’re interested in looking at these terms in more depth you will find many examples with a google search on LEFT SEMI JOIN.
Cheers,
Ben