来自wikipedia。仅作总结。
假设有两张表:
★Cross Join: 即笛卡尔乘积(Cartesian Product)。标准语法为:
SELECT *
FROM employee CROSS JOIN department;
也可以隐式地写为:
SELECT *
FROM employee, department;
得到的表结构为(LastName, DepartmentID, DepartmentID, DepartmentName)
★Inner Join: 不标准地说,Inner Join就是Cross Join加上了一个where子句,如:
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
当然,这也是隐式的写法。标准语法为:
SELECT *
FROM employee INNER JOIN department
ON employee.DepartmentID = department.DepartmentID;
★★Equi-Join: 是Inner Join的特殊形式,特指where子句中的条件为Equality Comparison,如上面的例子。不过它也有自己独特的语法:
SELECT *
FROM employee INNER JOIN department
USING (DepartmentID)
注意using子句会消除掉一个DepartmentID列,即得到(LastName, DepartmentID, DepartmentName),而不是(LastName, DepartmentID, DepartmentID, DepartmentName)。可以把using子句看做是特殊的where子句。
★★★★Natural Join: 是使用using子句的Equi-Join的特殊形式。Natural Join不用指定Equality Comparison的Column,它会自动查找做Natural Join的表中同名的Column,隐式地在这个Column上使用using子句。它的标准语法为:
SELECT *
FROM employee NATURAL JOIN department;
如果有多个同名Column(或者USING(Column1, ..., Column N)),则必须这多个Column都相等的列才被检入Join的结果中。
★Outer Join: Outer Join的定义不太好描述,我们先来看看Inner Join的示意图:
由于(John, NULL)和(35, Marketing)在对面表中没有匹配的记录,所以它们不会出现在Inner Join的结果中,而Outer Join就可以让这些没有匹配的记录出现在结果中。
★★Left Outer Join: 指“LEFT OUTER JOIN”关键字左方的表中的记录都会出现在结果中,如:
SELECT *
FROM employee LEFT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
得到的结果是:
示意图为:
★★Right Outer Join: 指“RIGHT OUTER JOIN”关键字右方的表中的记录都会出现在结果中,如:
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
得到的结果是:
示意图为:
★★Full Outer Join: 全外联结,即左外联结和右外联结的综合(并集)。如:
SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
得到的结果为:
★Self Join: 指表与自身的联结。