Thursday, March 25, 2010

Gail Shaw's JOIN Performance Testing (of March 2010)

SQL MVP Gail Shaw does some performance testing on comparing join types, with surprising results.
A brief summary of her findings with her final quotes for each page >

IN vs INNER JOIN (IN wins by a nose)
"If all you need is to check for matching rows in the other table but don’t need any columns from that table, use IN. If you do need columns from the second table, use Inner Join."

EXISTS vs IN (little difference)
"IN and EXISTS appear to perform identically both when there are no indexes on the matching columns and when there are, and this is true regardless of whether of not there are nulls in either the subquery or in the outer table."

NOT EXISTS vs NOT IN   (no difference)

"On non-nullable columns, the behaviour and performance of NOT IN and NOT EXISTS are the same, so use whichever one works better for the specific situation."

LEFT OUTER JOIN vs NOT EXISTS
"If you need to find rows that don’t have a match in a second table, and the columns are nullable, use NOT EXISTS. If you need to find rows that don’t have a match in a second table, and the columns are not nullable, use NOT EXISTS or NOT IN."

No comments: