Craig Freedman's SQL Server Blog

In my “Introduction to Joins” post , I gave an example of how we can use a semi-join to evaluate an EXISTS subquery. Just to recap, here is another example: create table T1 ( a int , b int ) create table T2 ( a int ) create table T3 ( a int ...

In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just...

The following table summarizes the characteristics of the three physical join operators which I described in my three prior posts. Nested Loops Join Merge Join Hash Join Best for … Relatively small inputs...

When it comes to physical join operators, hash join does the heavy lifting. While nested loops join works well with relatively small data sets and merge join helps with moderately sized data sets, hash join excels at performing the largest joins. Hash...

In this post, I’ll describe the second physical join operator: merge join (MJ). Unlike the nested loops join which supports any join predicate, the merge join requires at least one equijoin predicate. Moreover, the inputs to the merge join must be sorted...