November 4, 2011 Leave a comment
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 CountryAbbreviation CA WHERE 1 = 1 AND CA.DefaultAbbreviation IN (SELECT DefaultAbbreviation FROM CountryInformation)
SELECT CA.DefaultAbbreviation FROM CountryAbbreviation CA INNER JOIN CountryInformation CI ON CI.DefaultAbbreviation = CA.DefaultAbbreviation WHERE 1 = 1
Both queries are executed, and the Execution Plans look like this:
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.