Welcome to MSDN Blogs Sign in | Join | Help

August 2006 - Posts

Wednesday, August 30, 2006 2:02 PM

Subqueries: ANDs and ORs

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 ) select *
Posted by craigfr | 3 Comments
Filed under: ,
Wednesday, August 23, 2006 1:23 PM

Subqueries in CASE Expressions

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 evaluate
Posted by craigfr | 5 Comments
Filed under: ,
Wednesday, August 16, 2006 11:19 AM

Summary of Join Properties

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 with an index on the inner table on the join key.
Posted by craigfr | 4 Comments
Filed under:
Thursday, August 10, 2006 8:01 AM

Hash Join

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
Posted by craigfr | 7 Comments
Filed under:
Thursday, August 03, 2006 7:32 AM

Merge Join

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
Posted by craigfr | 4 Comments
Filed under:
 
Page view tracker