Joining tables or to select from multiple tables

Question

Which is better in between joining a table or selecting from multiple tables ?

For instance, lets assume the following similar scenario:

Using join:

SELECT COALESCE(SUM(SALARY),0) FROM X
JOIN Y ON X.X_ID=Y.Y_X_ID

OR

By selecting from multiple tables

SELECT COALESCE(SUM(SALARY),0) FROM X, Y
WHERE X.X_ID=Y.Y_X_ID

They are the same thing. See the execution plans. The second is also a join(is Oracle syle join). You should however use the first style which is more explicit and worldwide used. — Florin Ghita, Jan 23 '13 at 09:30
Speaking of ANSI and compatibility, try to get out of the habit of using Nvl(). Coalesce() is the standard function, and it's more flexible and some cases performs better than Nvl() due to short-circuit evaluation — David Aldridge, Jan 23 '13 at 10:52
possible duplicate of [SQL left join vs multiple tables on FROM line?](http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line) — Ben, Mar 17 '13 at 13:16

score 2 · Answer 1 · answered Apr 01 '13 at 20:15

Both are joins. The first is an explicit join and the second one is an implicit join and is a SQL antipattern.

The second one is bad because it is easy to get an accidental cross join. It is also bad becasue when you want a cross join, it is not clear if your did want that or if you have an accidental one.

Further in the second style if you ned to convert to an outer join, you need to change all joins in the query or risk getting incorrect results. So the second style is harder to maintain.

Explcit joins were institututed in the last century, why anyone is still using error-prone and hard to maintain implicit joins is beyond me.

score 2 · Answer 2 · edited Apr 06 '13 at 08:07

2

mainly join is used to retrieve data from multiple tables so in sql there are 3 types join are available

Equi join-inner join outer join-left right full
Non equi join
Self join
Cross join

edited Apr 06 '13 at 08:07

martin clayton

76,436
32
213
198

answered Apr 06 '13 at 07:32

sudarshan

41
2

score 1 · Answer 3 · edited May 23 '17 at 11:43

1

You should use the JOIN syntax for a lot of reasons which can be found here.

Moreover this syntax has the advantage to give some hints to the query optimizer (during the computation of weights, weights computed directly by the facts mentionned in this syntax are more favorably weighted than the others).

edited May 23 '17 at 11:43

Community

1
1

answered Apr 01 '13 at 18:37

GlinesMome

1,549
1
22
35

Joining tables or to select from multiple tables

3 Answers3