CODEWITHSUNDEEP
sqloracle-database
Student
Student

Reputation: 28345

Oracle SQL "from" syntax

What does it mean when I select from many tables with comma separated tables?

select * from table1,table2,table3

is it the same as join of left join or any other? How does it know how to join if I'm not specifying any id to join?

Upvotes: 0

Views: 48

Answers (1)

MT0
MT0

Reputation: 167962

It is Oracle's legacy comma-join syntax. The ANSI/ISO equivalent is CROSS JOIN:

SELECT *
FROM   table1
       CROSS JOIN table2
       CROSS JOIN table3

However, if you have a WHERE clause then there may be more appropriate join conditions. For example:

SELECT *
FROM   table1,table2,table3
WHERE  table1.id = table2.id
AND    table2.id = table3.id (+)

Converts to:

SELECT *
FROM   table1
       INNER JOIN table2 ON (table1.id = table2.id)
       LEFT OUTER JOIN table3 ON (table2.id = table3.id)

Upvotes: 2

Related Questions