DevJef's Mumbo-Jumbo

«There's a bit of SQL in all of us»


News


If you work with SQL Server you will eventually encounter a JOIN statement. This JOIN statement can be stated as an ordinary JOIN, or as a sub select. In most cases I try to use JOINS (even though it’s just for readability), but in some cases it’s quicker to do it in a sub select (for example with production code, in which you don’t want to change the output but filter output).

 

Curious about the performance differences between the two, I tried to create a test. I created 2 tables with country information:

 

 

 

 

 

 

 

 

 

In this case I will JOIN both tables, based on DefaultAbbreviation. The queries I used look like this:

 

SELECT CA.UNAbbreviation
FROM TST_CountryAbbreviation CA
WHERE 1 = 1
AND CA.DefaultAbbreviation IN
    (SELECT DefaultAbbreviation
     FROM TST_CountryInformation)

 

SELECT CA.DefaultAbbreviation
FROM TST_CountryAbbreviation CA
INNER JOIN TST_CountryInformation CI
    ON CI.DefaultAbbreviation = CA.DefaultAbbreviation
WHERE 1 = 1


 

Both queries are executed, and the Execution Plans look like this:

 

Query 1:

  

And for query 2:

  

As you can see the Execution Plans are practically the same. But if you look at the Execution Plan if you execute both statements simultaneously:

 

 

 

 

 

 

So in this case it proves that a JOIN is slower than a sub select. But what if you want a specific value…? I used the same statements, except in both versions I ask for the specific “CountryProper” value “Netherlands”:

 

 

  

 

So the Execution Plans show that if you fetch a specific value, the costs of both queries are the same.

 

The conclusion is the same as always: check the Execution Plan if you’re not sure about the performance of your statements.

 

Sources:

CreateSources.sql

SelectList.sql

SelectSpecificValue.sql


Comments

Gravatar # re: Join vs Sub Query
Posted by michel on 11/1/2011 9:22 PM
Nice!
Smart to execute them simultaneously and look at how much resource of the batch they take.

And as always... have nice day
Post A Comment
Title:
Name:
Email:
Comment:
Verification: