Anti Semi Joins

I happened across an unfamiliar term today, "anti semi join".

I encountered it in this nice little writeup from the MS SQL support team.

So, of course, I went hunting for more information about semi joins and anti semi joins. It turns out that I had a fairly good understanding of them already, but it was nice to find several well-written articles describing them, including:

• Craig Freedman's Introduction to Joins


• Roger Schrag's Speeding Up Queries with Semi-Joins and Anti-Joins


• Conor Cunningham's Conor vs. Anti-Semi-Join Reordering

Cunningham's point about not needing to do "TOP 1" in existence sub-queries is a very good one.

Query optimizers are incredibly sophisticated algorithms. I've only been studying them for 25 years. Eventually I'll understand them :)