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 :)