Home > OS >  Oracle SQL "from" syntax
Oracle SQL "from" syntax

Time:05-04

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?

CodePudding user response:

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)
  • Related