標準：
■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;