SQL Server Performance Forum – Threads Archive

ANSI-style JOIN

I found this reference athttp://www.microsoft.com/sql/techinfo/tips/development/July23.asp
Does this mean the old style syntax has better performance than the new syntax. Note I am talking about performance and assume that both the queries have been written to return the same data. Has anyone tested before? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

This is talking specifically about outer joins. I think that they are saying that select t1.col1,
t2.col1
from table1 t1,
table2 t2
where
t1.col1 *= t2.col1
and t2.col2 = ‘asd’ returns different results than select t1.col1,
t2.col1
from table1 t1
left outer join table2 t2
on t1.col1 = t2.col1
where t2.col2 = ‘asd’ Don’t have SQL on my laptop at the moment so I can’t test this theory… Cheers
Twan

---

See all the old syntax queries can be re-written in ANSI format go give the same results. The point of post was to see if there are any performance differences in the same. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

I have not formally tested this, but I ‘m pretty sure that as long as you write equivalent statements then no there won’t be a performance benefit. However the following query may run slower than the one below it select t1.col1
from table1 t1
inner join table2 t2
on t2.col1 = t1.col1
where t1.col2 = ‘asd’
and t2.col3 = ‘wer’
select t1.col1
from table1 t1
inner join table2 t2
on t2.col1 = t1.col1
and t2.col3 = ‘wer’
where t1.col2 = ‘asd’ since in the first case SQL will do the join before applying the selection criteria, whereas the second case gives SQL the choice depending on suitable indexes, etc. So you still have to know how to use the syntax optimally… Cheers
Twan

---

I have seen it happenning with outer join. I remember clearly it was returning different results set! I actually was going mad trying to figure out what was going on… <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Bambola.

---

i recall that on some older version of sql server (6.5 and before) that the optimizer would have more options to try with the older style , but this is nolonger the case,
also, a where clause condition on an outer join table causes the outer join to become an inner join unless the condition is a NULL
put left join search arguments in the join condition itself

---

]]>