CODEWITHSUNDEEP
sqloracle-databasejoin
Vanchinathan Chandrasekaran
Vanchinathan Chandrasekaran

Reputation: 5834

Oracle JOIN 3 tables - Which way is efficient or is there any difference at all

I can see people doing joins 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.

Upvotes: 3

Views: 48807

Answers (3)

Justin Cave
Justin Cave

Reputation: 231651

In addition to Mark's point that using the latter syntax helps ensure that you don't inadvertently miss a join condition, one nice thing about the SQL 99 syntax is that if your column naming convention is that the name of the column in the parent and child table matches, you can use the USING clause rather than the ON clause, i.e.

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

This can improve the readability of the code and decrease the probability that you introduce errors by inadvertently joining tables incorrectly. If I had a nickel for every time I accidentally wrote something like

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**

when I really meant

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

I'd be a rich man. Or at least have enough for a good sushi dinner.

Most of the time, of course, it's immediately obvious that you've done something wrong because the data is completely screwy but it occasionally happens that the results are sufficiently close that it's not immediately obvious that you've done something wrong and it's not until much later that you discover the bug and track down the culprit query. It's basically impossible to make that sort of mistake if you write the query with the USING clause

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

Upvotes: 7

Ren&#233; Nyffenegger
Ren&#233; Nyffenegger

Reputation: 40489

if your second select 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.

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 837966

In terms of performance there is no difference.

I prefer the second approach for maintainability. It is more explicit which conditions are used to join which tables and it is easier to see whether or not you have missed out a join condition.

Upvotes: 7

Related Questions