CODEWITHSUNDEEP
sqldatabaseoracle-databasejoinoracle10g
hsuk
hsuk

Reputation: 6860

Joining tables or to select from multiple tables

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

Upvotes: 7

Views: 410

Answers (3)

sudarshan
sudarshan

Reputation: 41

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

  1. Equi join-inner join outer join-left right full
  2. Non equi join
  3. Self join
  4. Cross join

Upvotes: 2

HLGEM
HLGEM

Reputation: 96572

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.

Upvotes: 2

GlinesMome
GlinesMome

Reputation: 1629

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).

Upvotes: 1

Related Questions