It’s about time I picked this series up again.
I’m not going to go into too much detail on joins. There are some very good articles elsewhere on joins. The important thing to notice about joins, in the context of an execution plan, is that there are six logical join operators and three physical join operators. The logical operators are what you ask for in the context of the query, the physical operators are what the optimiser picks to do the join.
The six logical operators are:
- Inner Join
- Outer Join
- Cross Join
- Cross Apply (new in SQL 2005)
- Anti Semi-Join
Craig Freedman wrote a long article on the logical join operators – Introduction to Joins
The semi-joins are the exception, in that they cannot be specified in a query. Nonetheless, they are present in disguise. They’re the logical operators for EXISTS, IN, NOT EXISTS and NOT IN. They’re used when matching is required, but not a complete join.