Joins

標準：

■SQL:2006　Syntax:

　　SELECT table1.column, table2.column

   FROM table1

   [NATURAL JOIN table2] |

   [JOIN table2 USING (column_name)] |

   [JOIN table2 ON (table1.column_name = table2.column_name)] |

   [LEFT | RIGHT | FULL OUTER JOIN table2

   ON (table1.column_name = table2.column_name)] |

   [CROSS JOIN table2];

■Oracle Proprietary Syntax:

    SELECT table1.column, table2.column

    FROM table1, table2

    [WHERE (table1.column_name = table2.column_name)] |

    [WHERE (table1.column_name(+)= table2.column_name)] |

    [WHERE (table1.column_name)= table2.column_name) (+)] ;

■

･Natrual　Join:(same name& same data types)

   NATURAAL JOIN

   USING  (①Do not qualify a column that is used in the Using clause

　　　　　　②if the same column is used elsewhere in SQL statement do not alias it)

   ON    (Additional Join Conditions（Use the AND clause or the WHERE clause to               apply aditional conditions）

     eg:

  ･select d.department_name from departments d

join locations l on (l.LOCATION_ID=d.LOCATION_ID)

where d.department_name like 'P%'

   ･select d.department_name from departments d

join locations l on

(l.LOCATION_ID=d.LOCATION_ID and d.department_name like 'P%')

･Nonequijoins

　　　　SELECT table1.column, table2.column

FROM table1

[JOIN table2 ON (table1.column_name < table2.column_name)]|

[JOIN table2 ON (table1.column_name > table2.column_name)]|

[JOIN table2 ON (table1.column_name <= table2.column_name)]|

[JOIN table2 ON (table1.column_name >= table2.column_name)]|

[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]|

･Outer joins:

   LEFT OUTER JOIN

   RIGHT OUTER JOIN

   FULL OUTER JOIN

･Cartesian product 笛卡尔积

　　A join condition is omitted

   A join condition is invalid

   All rows in the first table are joined to all rows in the second table

　　○the CROSS JOIN clause produces the cross -product of two tables;This is also     called a Cartesian product between the two tables;

    ○ SELECT table1.column, table2.column

  FROM table1

CROSS JOIN table2;