Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Semi Joins – SQL Training Question
Published: Nov 27, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.