Geeks With Blogs

News
DevJef's Mumbo-Jumbo «There's a bit of SQL in all of us»

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

Posted on Tuesday, November 1, 2011 2:26 PM SQL Scripts , SQL Server | Back to top


Comments on this post: Join vs Sub Query

# re: Join vs Sub Query
Requesting Gravatar...
Nice!
Smart to execute them simultaneously and look at how much resource of the batch they take.

And as always... have nice day
Left by michel on Nov 01, 2011 9:22 PM

Your comment:
 (will show your gravatar)


Copyright © DevJef | Powered by: GeeksWithBlogs.net | Join free