请参考：http://en.wikipedia.org/wiki/Join_(SQL)#Sample_tables
inner JOINS
An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product is very inefficient.
注意：innner查询（默认的连接查询方式），是先查询“Cartesian”生成中间表，再根据where条件筛选结果；但此方法非常低效，SQL具体的实现可能是 Hash join or a Sort-merge join 。
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.
SELECT *
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
The following example shows a query which is equivalent to the one from the previous example.
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).
Example of a left outer join, with the additional result row italicized:
SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| Jones |
33 |
Engineering |
33 |
| Rafferty |
31 |
Sales |
31 |
| Robinson |
34 |
Clerical |
34 |
| Smith |
34 |
Clerical |
34 |
| John |
NULL |
NULL |
NULL |
| Steinberg |
33 |
Engineering |
33
|
Example right outer join, with the additional result row italicized:
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| Smith |
34 |
Clerical |
34 |
| Jones |
33 |
Engineering |
33 |
| Robinson |
34 |
Clerical |
34 |
| Steinberg |
33 |
Engineering |
33 |
| Rafferty |
31 |
Sales |
31 |
| NULL |
NULL |
Marketing |
35
|
Example full outer join: （mysql is not support）
SELECT *
FROM employee
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
| Smith |
34 |
Clerical |
34 |
| Jones |
33 |
Engineering |
33 |
| Robinson |
34 |
Clerical |
34 |
| John |
NULL |
NULL |
NULL |
| Steinberg |
33 |
Engineering |
33 |
| Rafferty |
31 |
Sales |
31 |
| NULL |
NULL |
Marketing |
35
|
Self-join
A query to find all pairings of two employees in the same country is desired.
An example solution query could be as follows:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
Which results in the following table being generated.
Employee Table after Self-join by Country
| EmployeeID | LastName | EmployeeID | LastName | Country |
| 123 |
Rafferty |
124 |
Jones |
Australia |
| 123 |
Rafferty |
145 |
Steinberg |
Australia |
| 124 |
Jones |
145 |
Steinberg |
Australia |
| 305 |
Smith |
306 |
John |
Germany
|
Join algorithms
Three fundamental algorithms exist for performing a join operation: Nested loop join, Sort-merge join and Hash join.