November 27, 2018 at 4:28 am
Hi All,
Is there any difference between joining tables using inner join or specifying the join conditions after the where clause.
Example:
select column1,column2 from table1 inner join table2 on table1.field1 = table2.field2
versus
select column1,column2 from table1,table2 where table1.field1 = table2.field2
Thanks in advance.
Nader
November 27, 2018 at 4:40 am
Technically, no, however, the latter is the ANSI-89 syntax and some features of it are no longer supported (OUTER joins for example). The other is the ANSI-92 and you should really be using that; it's been about for over 25 years now, and there's no good reason why you shouldn't be using it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 27, 2018 at 4:45 am
Thom A - Tuesday, November 27, 2018 4:40 AMTechnically, no, however, the latter is the ANSI-89 syntax and some features of it are no longer supported (OUTER joins for example). The other is the ANSI-92 and you should really be using that; it's been about for over 25 years now, and there's no good reason why you shouldn't be using it.
Thank you for your reply, actually i am not using it but we have some obsolete programs still using that old technique so i wanted to know if it will cause any problems.
Thanks again.
Nader
November 27, 2018 at 4:46 am
nadersam - Tuesday, November 27, 2018 4:28 AMHi All,
Is there any difference between joining tables using inner join or specifying the join conditions after the where clause.
Example:
select column1,column2 from table1 inner join table2 on table1.field1 = table2.field2
versus
select column1,column2 from table1,table2 where table1.field1 = table2.field2Thanks in advance.
Nader
In this particular case I think both queries are same. select column1,column2 from table1,table2
query becomes cross join or Cartesian product and then where clause filter out the records to become inner join.
Where vs ON clause doesn't matter for inner joins but it is different for other joins such as left outer join .
Saravanan
November 27, 2018 at 4:49 am
Thank you.
November 27, 2018 at 6:04 am
It does make the code harder to read though. While it doesn't matter from a technical or performance stand point with INNER JOINs as was already pointed out, it does just make the code sloppy. Logically separating filtering criteria from relational criteria makes for neater, more easily understood, code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply