join query question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

join query question

There are 2 queries:
SELECT *
FROM T1
LEFT JOIN T2 ON T1.ID = T2.T1_ID AND T2.T1_ID IS NOT NULL
and
SELECT *
FROM T1
LEFT JOIN T2 ON T1.ID = T2.T1_ID
WHERE T2.T1_ID IS NOT NULL
Could you explain me which one will perform better and why?
(I thought I knew that but I am beginning to think I am wrong, so I do not want to suggest anything.) — Marek ‘chopeen’ Grzenkowicz, MCP
Poland
Well, for starters, neither is effecient for what they have in them. A left join inplies that there can be data not in the table on the right (t2), but then the query explictly only selects where there is data on the right. You would get identical results, with better performance (for this example) by running this: SELECT *
FROM T1
JOIN T2 ON T1.ID = T2.T1_ID From BOL
"The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions."
This says that the first could be more efficient. But SQL will try to optimize the query and it may run both the same way. I ran display estimated query plan on both queries on a couple of system tables (I know, dont use them, but this way anyone else can test this too). They came back with an identical query plan. Which says that in this case, SQL picked the optimum path as the same, dispite the way it was written. SELECT *
FROM sysobjects
LEFT JOIN syscolumns (nolock) ON sysobjects.ID = syscolumns.ID AND syscolumns.ID IS NOT NULL SELECT *
FROM sysobjects
LEFT JOIN syscolumns (nolock) ON sysobjects.ID = syscolumns.ID
WHERE syscolumns.ID IS NOT NULL
Syntactically though, the first is usually a better way to write it. You dont normally check for nulls on your joining column, but rather on another column. This needs to be in the join, not the where or it would only evaluate full matches. So these 2 could produce different results.
SELECT *
FROM T1
LEFT JOIN T2 ON T1.ID = T2.T1_ID AND T2.Field2 IS NOT NULL
SELECT *
FROM T1
LEFT JOIN T2 ON T1.ID = T2.T1_ID
WHERE T2.Field IS NOT NULL
]]>