Join vs Sub Query

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:

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

Follow

Get every new post delivered to your Inbox.

Join 50 other followers