Shulou(Shulou.com)06/01 Report--

Oracle table join supports standard writing, but there is also a special oracle writing method. These two writing methods may be different in some scenarios. It is recommended to use standard writing method. Here we only introduce the standard syntax of table join and understand the special writing method of oracle.

Standard connection syntax:

Select table1.column, table2.column

From table1

[corss join table2]

[national jon table2]

[join table2 using (column)]

[join table2 on (table1.column=table2.column)]

[left | right | full outer join table2 on (table1.column=table2.column)]

In practice, the connection field after the on keyword can be used normally without parentheses.

Multi-table connection:

-- first connect table4 and table5 and name their result set table2, then connect to table1 select table1.column,table2.column from table1 inner join (select table4.column,table5.column from table4 inner join table5 on table4.column=table5.column) as table2 on table1.column=table2.column; is equivalent to select table1.column, table2.columnfrom table1, (select table4.column,table5.column from table4,table5 where table4.column=table5.column) as table2where table1.column=table2.column -- connecting table1,table2,table3. No connection order. Select table1.column,table2.column,table3.column from table1 inner join table2 on table1.column=table2.column inner join table3 on table1.column=table3.column; equals select table1.column,table2.column,table3.column from table1,table2.table3where table1.column=table2.column and table1.column=table3.column.

Internal connection:

Standard writing:

Select table.column, table2.column from table1 inner join table2 on (table1.column=table2.column)

Oracle is specially written as follows:

Select table.column, table2.columnfrom table1, table2where table1.column=table2.column

Left connection:

Standard writing:

Select table.column, table2.column from table1 left join table2 on (table1.column=table2.column)

Oracle is specially written as follows:

Select table.column, table2.column from table1, table2where table1.column=table2.column (+)

Right connection:

Standard writing:

Select table.column, table2.column from table1 right join table2 on (table1.column=table2.column)

Oracle is specially written as follows:

Select table.column, table2.column from table1, table2where table1.column (+) = table2.column

Fully connected:

Standard writing:

Select table.column, table2.column from table1 full join table2 on (table1.column=table2.column)

Oracle is specially written as follows:

Select table.column, table2.column from table1, table2where table1.column (+) = table2.column (+)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.