This was something which came up in one of my online courses recently… everyone was fine with all the main join types that appear in SQL Server both syntactical (inner join, left join, right join, full outer join) and internal (nested loops, merge, and hash), but a question arose surrounding some of the joins that you can only see listed on an execution plan and do not specify yourself.
In this article I’ll quickly cover one of those… Semi Join:
To be honest this is something that most people will have used, but without ever knowing it… especially when the syntax is only present within an execution plan.
If you have a join that, for each row in table A returns AT LEAST 1 record from table B, then you have a Semi Join.
The best examples of this comes from EXISTS or IN. For example, using AdventureWorks:
select *
from Production.product p
where exists
(
select *
from Production.TransactionHistoryArchive t
where p.ProductID = t.ProductID
)
The query is simply returning a record where the ProductID exists at least once in the TransactionHistoryArchive table.