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.







Gravatar # re: Join vs Sub Query
Posted by michel on 11/1/2011 9:22 PM
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