December 12, 2017 at 9:26 am
Can someone tell me if the DBMS treats old- and new-style INNER JOINs differently? I learnt the old style and while I have since switched I've always been under the impression that the two styles produce exactly the same result. This is what I mean by old and new:
Old Style:
select 1 from t1, t2 where t1.EmployeeId = t2.EmployeeId
New Style:
select 1 from t1 INNER JOIN t2 on t1.EmployeeId = t2.EmployeeId
A colleague believes that the old style syntax is equivalent to doing an OUTER APPLY between the two tables but I've never heard that before. They both generate the same execution plan.
December 12, 2017 at 9:38 am
Where did he ever get the idea that either style would default to an implicit outer apply, apply in general is newer than either join syntax....
But no both styles should produce the same results and the same execution plan.
December 12, 2017 at 9:56 am
ZZartin - Tuesday, December 12, 2017 9:38 AMWhere did he ever get the idea that either style would default to an implicit outer apply, apply in general is newer than either join syntax....But no both styles should produce the same results and the same execution plan.
I'm actually mis-quoting him slightly. What he said is that the cause of our 'duplicate data' issue is the syntax 'select 1 from t1, t2' because it creates a cartesian product between the two tables, and that if that's what we actually wanted to do then it should be rewritten as an OUTER APPLY. However, I pointed out that the inner join is in the WHERE clause and that while I agree the old syntax is less readable, the DBMS treats it the same as the new INNER JOIN. The cause of our duplicate data was actually an OUTER JOIN.
He's under the impression that the old-style syntax is starting to be deprecated but I'm not aware of any DBMS's considering that.
He's claiming that the DBMS does a cartesian product and then filters in the WHERE clause though. Is that true?
December 12, 2017 at 10:28 am
cthendy - Tuesday, December 12, 2017 9:56 AMZZartin - Tuesday, December 12, 2017 9:38 AMWhere did he ever get the idea that either style would default to an implicit outer apply, apply in general is newer than either join syntax....But no both styles should produce the same results and the same execution plan.
I'm actually mis-quoting him slightly. What he said is that the cause of our 'duplicate data' issue is the syntax 'select 1 from t1, t2' because it creates a cartesian product between the two tables, and that if that's what we actually wanted to do then it should be rewritten as an OUTER APPLY. However, I pointed out that the inner join is in the WHERE clause and that while I agree the old syntax is less readable, the DBMS treats it the same as the new INNER JOIN. The cause of our duplicate data was actually an OUTER JOIN.
He's under the impression that the old-style syntax is starting to be deprecated but I'm not aware of any DBMS's considering that.
He's claiming that the DBMS does a cartesian product and then filters in the WHERE clause though. Is that true?
1) The two syntaxes are logically equivalent, and they will always produce the same results.
2) The two will always produce the same execution plan, because they are interpreted the same way.
3) A Cartesian product is a CROSS JOIN (not an OUTER APPLY).
4) The logical processing of both forms is to do a CROSS JOIN and then filter. The timing of the filtering is slightly different, but the two are still logically equivalent.
5) The physical processing of the queries does not have to match the logical processing as long as the results are guaranteed to be the same.
6) The physical processing is outlined in the execution plan.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 12, 2017 at 10:36 am
My research appears to indicate that the old style joins were only partially discontinued.
The old ANSI style LEFT OUTER (*=) and RIGHT OUTER (=*) were Depricated in SQL 2008, and Discontinued in SQL 2012
However, it appears that the INNER JOIN syntax is not on the deprication list.
Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply