最近一直忙着写SQLAlchemy的教程，但是在看到关系查询时，需要引入join的概念。也只怪的当初数据库没有学好，感觉不太能理解join机制的概念。故这里也尝试写一下对join的理解。本文是基于JOIN的Wiki。
在关系型数据库中，JOIN本质上是基于涉及的两个或者多个表进行结合重构的过程点。其创造的结果可以被保存为一个表(table)或者是作为一个表来使用。这个结合的过程的基础，或者说联系点，是存在于两个表之间的共通的列。一般来说，ANSI标准的SQL定义了如下这些JOIN操作类型：
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- CROSS
例表
关系型数据库通常数据关系的抽象来减少数据冗余。例如，一个Department会同多个Employ关联。
Employ表如下
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Heisenberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| Williams | Null |
Department表如下
| DepartmentID | DepartmentName |
|---|---|
| 31 | Sales |
| 33 | Engineering |
| 34 | Clerical |
| 35 | Marketing |
其中Employ表中的DepartmentID是Department表中的主键。将不同的表JOIN起来就是以一定的规则（例如开头我们提到的五种），将两者的信息结合起来构成一张大表。（毕竟Join过程需要耗费时间，有时候为了提高查询性能，会直接将这两个表的Join结果作为一个大表存储在数据库中，不过这会耗费更多的存储空间）。
下面是创建这些两个表的SQL指令
CREATE TABLE department
(
DepartmentID INT,
DepartmentName VARCHAR(20)
);
CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT
);
INSERT INTO department VALUES(31, 'Sales');
INSERT INTO department VALUES(33, 'Engineering');
INSERT INTO department VALUES(34, 'Clerical');
INSERT INTO department VALUES(35, 'Marketing');
INSERT INTO employee VALUES('Rafferty', 31);
INSERT INTO employee VALUES('Jones', 33);
INSERT INTO employee VALUES('Heisenberg', 33);
INSERT INTO employee VALUES('Robinson', 34);
INSERT INTO employee VALUES('Smith', 34);
INSERT INTO employee VALUES('Williams', NULL);
CROSS JOIN
CROSE JOIN返回两张表的笛卡尔积，也就是不指定结合规则，让两表中的元素直接两两组合。
SELECT *
FROM employee CROSS JOIN department;
下面这个SQL指令本质是隐式的CROSE JOIN:
SELECT *
FROM employee, department;
以开头的例表为例，执行结果为：
CROSE JOIN
CROSE JOIN没有应用任何筛选条件来控制返回的结果。当然其结果可以进一步通过WHERE来控制，从而产生等效于INNER JOIN的结果。通过这个语句用来检验数据库性能。
INNER JOIN
INNER JOIN要求在两个待JOIN的表（A和B）拥有有匹配的记录。这是一个非常用的JOIN方法，但是并非在每种情况下都是最优选择。和CROSE JOIN不同，INNER JOIN引入了predicate来指明结合的规则。查询指令最终对A中的每一行逐一和B中的各行进行比对，找到所有符合筛选条件的组合。这些组合被用来构造输出的新表。
上述过程也可以理解为对两表的笛卡尔积进行筛选后得到的结果。但是这样无疑性能会比较差，占用内存较多。实际的数据库实现中一般会采用其他的方法，如hash joins或者sorted-merge joins。
和CROSE JOIN类似，INNER JOIN也有显式和隐式两种写法。不过1992年隐式写法被deprecated掉了，也许现在有一些数据处于兼容性的考虑仍然支持这种写法。
显式的INNER JOIN会使用JOIN关键字，前面可以加INNER来指名JOIN种类，不加也可以。为了指明进行JOIN的规则，需要使用ON这个关键字。如下面这个例子:
SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
隐式写法没有用到JOIN关键字，而是采用的对CROSE JOIN的结果进行筛选的方式。下面是隐式写法的一个例子：
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
上面两种写法的效果是一样的。上面例子中的查询，数据库使用两表中的Department列来进行join。来自两表的记录的DepartmentID吻合时，数据库会将LastName, DepartmentID以及DepartmentName 等两个表的属性结合起来构造出新表中的一行。如果DepartmentID不吻合，则不会产生新行。
上面的SQL语句的执行结果如下：
INNER JOIN
注意到名为William的雇员和名为Marketing的部门没有出现在上面的查询结果中。William没有关联的部门，Marketing没有关联的雇员。有时候我们不希望派车这些记录，此时就要使用OUTER JOIN了。+
注意：程序们在JOIN包含有NULL值的表时要特别注意，NULL不会和任何值匹配，包括自身（也就是NULL!=NULL)，除非显示的在筛选条中记性控制（加入对NULL的判断）。在有些数据库中，强制启用了referential integrity，保证了不会出现NULL值，此时INNER JOIN可以安全的使用。由于数据库的实现细节各有不同，依靠数据库本身特性来避免NULL的问题不是很可靠，推荐的方法是尽量避免设置NULL，而是定义一个无意义的空值来代替。如字符串用""来代表NULL。
OUTER JOIN
使用OUTER JOIN时可以保证指定表的每条记录都出现----即使没有匹配。OUER JOIN又可以分为LEFT OUTER JOIN, RIGHT OUTER JOIN 和 FULL OUTER JOIN。取决于你想要保留的表是哪一个。
OUTER JOIN不存在隐式表达法。
LEFT OUTER JOIN
表A和表B的LEFT OUTER JOIN结果中总会保留左侧的A表的所有记录，即便A中的一些行没有B中的行与之对应。此时构造的新行中，所有的原B中的列的值会被填充为NULL。
如下例（OUTER关键字可以省略）：
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
筛选结果如下：
LEFT OUTER JOIN
RIGHT OUTER JOIN
这个就好理解了，和LEFT OUTER JOIN是反着来的。如下面的例子。
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
查询的结果如下图：
RIGH OUTER JOIN
FULL OUTER JOIN
FULL OUTER JOIN同时保留两个表的列.
SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
FULL OUTER JOIN
有些数据库不支持FULL OUTER JOIN，不过你可以通过INNER JOIN加上UNION ALL来实现同样的功能。下面给出了一个例子：
SELECT employee.LastName, employee.DepartmentID,
department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.LastName, employee.DepartmentID,
cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
SELECT * FROM department
WHERE employee.DepartmentID = department.DepartmentID)
UNION ALL
SELECT cast(NULL as varchar(20)), cast(NULL as integer),
department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
SELECT * FROM employee
WHERE employee.DepartmentID = department.DepartmentID)
Self-JOIN
一个特殊的情况是要JOIN的表其实是同一个表。例如，考虑下面这个表：
Self JOIN Table
我们想要查找出来有哪些用户是来自同一个国家。也就是要讲上面这个表和它自身以Coutry这一列为基础进行JOIN。下面是一个例子：
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
查询结果如下：
Self JOIN Result
SQLAlchemy的教程第三部分快要出来了，欢迎大家关注~
网友评论