SQL: JOIN

http://superman-chenzs.itpub.net/post/29327/276049

笛卡尔积：
cross join

Join Types:
1. Inner Join
- Equal-Join
- Non-Equal-Join
2. Outer Join
- Left-Outer-Join
- Right-Outer-Join
3. Self Join
- Join a table to itself
- Using the table's alias name to complete the self join.
- attention the repeat lines
-----------------------------------------------------------------------------------------------------------------
Joining Tables Using Oracle Syntax:
- Write the join condition in the WHERE clause.
- Prefix the column name with the table name when the same column name appears in more than one table.
- Additional Search Conditions Using the AND Operator
- To join n tables together,you need a minimun of n-1 join conditions.

-----------------------------------------------------------------------------------------------------------------
Qualifying Ambiguous Column Names:
- Use table prefixes to qualify column names that are in multiple tables.
- Improve performance by using table prefixes.
- Distinguish columns that have identical names but reside in different tables by using column aliases.

-----------------------------------------------------------------------------------------------------------------
Outer Join:
- You use an outer join to also see rows that do not meet the join condition.
- The Outer join operator is the plus sign (+).
- The sign '(+)' is on the ritht,the type is Left-Outer-Join
- The sign '(+)' is on the left ,the type is Right-Outer-Join

eg:
----------------------------------
the Right-Outer-Join:
--->
select empno,ename,d.deptno
from emp e,dept d
where e.deptno(+) = d.deptno

EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
40
----------------------------------
the Left-Outer-Join:
--->
select empno,ename,d.deptno
from emp e, dept d
where e.deptno = d.deptno(+);

EMPNO ENAME DEPTNO
--------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10



**********************************************************************************************************************
Joining Tables Using SQL: 1999 Syntax
Syntax:
SELECT table1.column,table2.column
FROM table1
[CROSS JOIN table2] |
[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)] ;

-----------------------------------------------------------------------------------------------------------------------
Creating Cross Joins:
- The CROSS JOIN clause produces the cross-product of two tables.
- This is the same as a Cartesian product between the two tables.(迪卡尔集)
eg:
select empno,ename,d.deptno from emp e CROSS JOIN dept d;
===
select empno,ename,d.deptno from emp e,dept d;

-----------------------------------------------------------------------------------------------------------------------
Creating Natural Joins:
- The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
- It selects rows from the two tables that have equal values in all matched columns.
- If the columns having the same names have different data types,an error is returned.
- Do not use a table name or alias in the referenced columns.
eg:
select empno,ename,deptno from emp NATURAL JOIN dept;
===
SELECT empno,ename,d.deptno from emp e,dept d where e.deptno=d.deptno;

-----------------------------------------------------------------------------------------------------------------------
Creating Join with the USING Clause:
- If several columns have the same names but the data types do not match,the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin.
- Use the USING clause to match only one column when more than one column matches.
- Do not use a table name or alias in the referenced columns.
- The NATURAL JOIN and USING clauses are mutually exclusive(相互排斥).

-----------------------------------------------------------------------------------------------------------------------
Creating Joins with the ON Clause:
- The join condition for the natural join is basically an equijoin of all columns with the same name.
- To specify arbitrary(任意的) conditions or specify columns to join ,the ON clause is used.
- The join condition is separated from other search conditions.
- The ON clause makes code easy to understand.
eg:
select e.empno,e.ename,d.deptno from emp e join dept d on e.deptno=d.deptno;
===
select e.empno,e.ename,d.deptno from emp e ,dept d where e.deptno=d.deptno;
- Creating Three-Way Joins with the ON Clause,eg:
------
SELECT employee_id,city,department_name
FROM employees e
JOIN departments d ON d.department_id=e.department_id
JOIN locations l ON d.location_id=l.location_id;
- that means N JOINS must have N-1 join_conditions.

-----------------------------------------------------------------------------------------------------------------------
INNER JOIN & OUTER JOIN:
- In SQL:1999, the join fo two tables returning only matched rows is an inner join.
- A join between two tables that returns the results of the inner join as well as unmatched rows left (or right) tables is a left (or right) outer join.
- A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.
- The Left-Outer-Join: show all the value in the leftside table, even the rightside's value is null.
- The Right-Outer-Join: show all the value in the rightside table, even the leftside's value is null.
--------
eg:
--------
LEFT JOIN:
select ename,d.deptno from emp e left outer join dept d ON e.deptno=d.deptno;
===
select ename,d.deptno from emp e ,dept d where e.deptno=d.deptno(+);
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

已选择14行
--------
RIGHT JOIN:
select ename,d.deptno from emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
===
select ename,d.deptno from emp e,dept d where e.deptno(+)=d.deptno;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
40

已选择15行
--------
FULL OUTER JOIN:
select ename,d.deptno from emp e FULL OUTER JOIN dept d ON e.deptno=d.deptno;
ENAME DEPTNO
---------- ----------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
40