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.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: