Oracle JOIN Tables 3 - Which Way Is Effective or Is There Any Difference At All

Question

I see how people make connections in different ways.

select a.acc, b.acc, c.acc from a, b, c where a.acc = b.acc and c.acc = a.acc;

and

select a.acc, b.acc, c.acc JOIN on a.acc = b.acc JOIN on c.acc = a.acc;

Is there any difference? I suppose not.

Answer 1

In terms of performance, there is no difference.

I prefer the second maintainability approach. More explicit is what conditions are used to join these tables, and it is easier to see if you missed a join condition.

Answer 2

Mark , , , SQL 99 , , , USING, ON,

SELECT <<list of columns>>
  FROM a JOIN b USING( acc )
         JOIN c USING( acc )

, , . , -

SELECT <<list of columns>>
  FROM a,
       a_to_b,
       b
 WHERE a.a_id = a_to_b.a_id
   AND b.b_id = a_to_b.a_id -- a_to_b.**a_id** rather than a_to_b.**b_id**

SELECT <<list of columns>>
  FROM a,
       a_to_b,
       b
 WHERE a.a_id = a_to_b.a_id
   AND b.b_id = a_to_b.b_id

. , , .

, , , - , , , , , - , . , USING

SELECT <<list of columns>>
  FROM a JOIN a_to_b USING (a_id)
         JOIN b      USING (b_id)

Answer 3

if your second choice is to read select a.acc, b.acc, c.acc from a JOIN b on a.acc=b.acc JOIN c on c.acc = a.acc;, then there is no difference.