Which of two ways of coding an Inner join is faster?

Question

I much prefer to code in t-sql using what is in effect an in-line join, rather than have a long list of the joins at the end of the stored procedure or view.

For example, I code:

SELECT      PKey  ,    Billable, 
    (SELECT LastName  FROM Contact.dbo.Contacts WHERE (Pkey = Contacts_PKey)),
    (SELECT Description FROM Common.dbo.LMain WHERE (PKey= DType)),  
    (SELECT  TaskName  FROM  Common.dbo.LTask WHERE  (PKey =  TaskType)) ,  
    StartTime,  EndTime,  SavedTime
FROM   dbo.TopicLog   where  StartTime > '7/9/09'  ORDER BY  StartTime

Rather than

SELECT t.PKey, t.Billable, c.LastName, m.Description, lt.TaskName, t.StartTime, t.EndTime, t.SavedTime
FROM dbo.TopicLog AS t     
inner join  Contact.dbo.Contacts as c   on  c.Pkey = t.Contacts_PKey and t.StartTime > '7/9/09'
inner join  Common.dbo.LMain  as m  on  m.PKey = t.DType
inner join  Common.dbo.LTask  as lt on lt.PKey = t.TaskType
ORDER BY t.StartTime

I prefer this type of syntax because it is so much less confusing when writing or debugging, especially when there are many tables being joined or other stuff going on (case statements, t-sql functions, self joins, etc)

But my question is - am taking a performance hit by querying the database this way.

I do not have enough data collected yet to be able to measure a difference, but I will at some point down the road.

I would like to find out before I proceed further. I would not want to have to go back later and recode everything to improve performance.

Answer 1

The second one (the actual inner join), generally. The first one (subqueries) does 3 queries for every row, but this is generally managed by the compiler so that the differences are mitigated.

Best yet: Check the query execution plans for yourself!

Since you're getting slow performance, my guess is that your tables aren't indexed properly. You should have clustered indexes on all of your primary keys, and non-clustered indexes on the foreign keys (those that you use to make the joins).

I should note that these two queries are equivalent if and only if you have matching values in all of your join conditions (i.e.-always returns all of the rows from the main table). Otherwise, you'll get null from the subquery if there's no match. Inner joins actively filter out any rows that don't match the join conditions. The subquery approach is actually equivalent (in results, not speed or execution) to a left outer join.

Answer 2

The first method is not an inner join at all, it is a correlated subquery. And they are more like left outer joins than inner joins as they will return NULLs when there is no matching value.