Search code examples
sqlsql-serversql-server-2008joindatabase-performance

Inner join vs separate statements

I am trying to get some more context on this out of curiosity. So far when I run 2 separate sql statements I find in SQL Profiler that I have no CPU cycles, less reads and less duration than taking the script and using Inner join. Is this a typical case, I am looking for help to understand this better.

Simple example:

SELECT *  FROM dbo.ChargeCode 

SELECT *  FROM dbo.ChargeCodeGroup

vs

SELECT *  
FROM dbo.ChargeCode c 
INNER JOIN dbo.ChargeCodeGroup cc ON c.ChargeCodeGroupID = cc.ChargeCodeGroupID

From what I guess is that inner join cost extra CPU cycles because its doing a nested loop. Am I on the right track with this?

Solution

  • The simple answer is that you're doing two different things here. In your 1st example you're retrieving 2 separate entities. In your second example, your asking the RDBMS to combine (join) 2 entities into a single result set.

    A join is one of the most powerful capabilities of an RDBMS - and it will (usually) do it as efficiently as it possibly can - but that's not to say it's free or cheap.