What is difference between JOIN and a.ID=b.ID

Question

I have always been using JOINS but today I saw a simple code that was like that:

SELECT Name FROM customers c, orders d WHERE c.ID=d.ID

It is just the old way?

see here http://stackoverflow.com/questions/8311096/whats-the-difference-between-where-clause-and-on-clause-when-table-left-join — PSR, Jun 06 '13 at 16:12

score 2 · Accepted Answer · answered Jun 06 '13 at 16:11

2

There is no difference, the execution plan will be the same using that method or JOIN

answered Jun 06 '13 at 16:11

Tom Studee

10,316
4
38
42

score 1 · Answer 2 · answered Jun 06 '13 at 16:14

1

These 2 queries are semantically identical. With an join, predicates can be specified in either the JOIN or WHERE clauses.

answered Jun 06 '13 at 16:14

PSR

39,804
41
111
151

score 0 · Answer 3 · answered Jun 06 '13 at 16:24

When doing just inner joins, there isn't much difference between the implicit style and ANSI joins. Likely, there is no difference as the database will execute them the same.

But it can quickly get complex, at least when you are allowed to do implicit outer joins. There is a few things that joins can do that you cannot do the old way. I believe the following statement cannot be expressed with implicit joins (taken from the below link).

SELECT *
FROM T1 LEFT OUTER JOIN T2
ON (T1.SOME_VALUE = 11 and T1.ID = T2.ID)
WHERE T1.OTEHR_VALUE > 3;

Read this for more info.

There appears to be some confusion about equivalence between ANSI outer join and Oracle outer join syntaxes. The following examples explain the equivalences and inequivalences of these two syntaxes.

What is difference between JOIN and a.ID=b.ID

3 Answers3