最近一直忙著寫SQLAlchemy的教程，但是在看到關(guān)系查詢時，需要引入join的概念。也只怪的當初數(shù)據(jù)庫沒有學(xué)好，感覺不太能理解join機制的概念。故這里也嘗試寫一下對join的理解。本文是基于JOIN的Wiki。
在關(guān)系型數(shù)據(jù)庫中，JOIN本質(zhì)上是基于涉及的兩個或者多個表進行結(jié)合重構(gòu)的過程點。其創(chuàng)造的結(jié)果可以被保存為一個表(table)或者是作為一個表來使用。這個結(jié)合的過程的基礎(chǔ)，或者說聯(lián)系點，是存在于兩個表之間的共通的列。一般來說，ANSI標準的SQL定義了如下這些JOIN操作類型：
- INNER
- LEFT OUTER
- RIGHT OUTER
- FULL OUTER
- CROSS
例表
關(guān)系型數(shù)據(jù)庫通常數(shù)據(jù)關(guān)系的抽象來減少數(shù)據(jù)冗余。例如，一個Department會同多個Employ關(guān)聯(lián)。
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起來就是以一定的規(guī)則（例如開頭我們提到的五種），將兩者的信息結(jié)合起來構(gòu)成一張大表。（畢竟Join過程需要耗費時間，有時候為了提高查詢性能，會直接將這兩個表的Join結(jié)果作為一個大表存儲在數(shù)據(jù)庫中，不過這會耗費更多的存儲空間）。
下面是創(chuàng)建這些兩個表的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返回兩張表的笛卡爾積，也就是不指定結(jié)合規(guī)則，讓兩表中的元素直接兩兩組合。
SELECT *
FROM employee CROSS JOIN department;
下面這個SQL指令本質(zhì)是隱式的CROSE JOIN:
SELECT *
FROM employee, department;
以開頭的例表為例，執(zhí)行結(jié)果為：
CROSE JOIN沒有應(yīng)用任何篩選條件來控制返回的結(jié)果。當然其結(jié)果可以進一步通過WHERE來控制，從而產(chǎn)生等效于INNER JOIN的結(jié)果。通過這個語句用來檢驗數(shù)據(jù)庫性能。
INNER JOIN
INNER JOIN要求在兩個待JOIN的表（A和B）擁有有匹配的記錄。這是一個非常用的JOIN方法，但是并非在每種情況下都是最優(yōu)選擇。和CROSE JOIN不同，INNER JOIN引入了predicate來指明結(jié)合的規(guī)則。查詢指令最終對A中的每一行逐一和B中的各行進行比對，找到所有符合篩選條件的組合。這些組合被用來構(gòu)造輸出的新表。
上述過程也可以理解為對兩表的笛卡爾積進行篩選后得到的結(jié)果。但是這樣無疑性能會比較差，占用內(nèi)存較多。實際的數(shù)據(jù)庫實現(xiàn)中一般會采用其他的方法，如hash joins或者sorted-merge joins。
和CROSE JOIN類似，INNER JOIN也有顯式和隱式兩種寫法。不過1992年隱式寫法被deprecated掉了，也許現(xiàn)在有一些數(shù)據(jù)處于兼容性的考慮仍然支持這種寫法。
顯式的INNER JOIN會使用JOIN關(guān)鍵字，前面可以加INNER來指名JOIN種類，不加也可以。為了指明進行JOIN的規(guī)則，需要使用ON這個關(guān)鍵字。如下面這個例子:
SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
隱式寫法沒有用到JOIN關(guān)鍵字，而是采用的對CROSE JOIN的結(jié)果進行篩選的方式。下面是隱式寫法的一個例子：
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
上面兩種寫法的效果是一樣的。上面例子中的查詢，數(shù)據(jù)庫使用兩表中的Department列來進行join。來自兩表的記錄的DepartmentID吻合時，數(shù)據(jù)庫會將LastName, DepartmentID以及DepartmentName 等兩個表的屬性結(jié)合起來構(gòu)造出新表中的一行。如果DepartmentID不吻合，則不會產(chǎn)生新行。
上面的SQL語句的執(zhí)行結(jié)果如下：
注意到名為William的雇員和名為Marketing的部門沒有出現(xiàn)在上面的查詢結(jié)果中。William沒有關(guān)聯(lián)的部門，Marketing沒有關(guān)聯(lián)的雇員。有時候我們不希望派車這些記錄，此時就要使用OUTER JOIN了。+
注意：程序們在JOIN包含有NULL值的表時要特別注意，NULL不會和任何值匹配，包括自身（也就是NULL!=NULL)，除非顯示的在篩選條中記性控制（加入對NULL的判斷）。在有些數(shù)據(jù)庫中，強制啟用了referential integrity，保證了不會出現(xiàn)NULL值，此時INNER JOIN可以安全的使用。由于數(shù)據(jù)庫的實現(xiàn)細節(jié)各有不同，依靠數(shù)據(jù)庫本身特性來避免NULL的問題不是很可靠，推薦的方法是盡量避免設(shè)置NULL，而是定義一個無意義的空值來代替。如字符串用""來代表NULL。
OUTER JOIN
使用OUTER JOIN時可以保證指定表的每條記錄都出現(xiàn)----即使沒有匹配。OUER JOIN又可以分為LEFT OUTER JOIN, RIGHT OUTER JOIN 和 FULL OUTER JOIN。取決于你想要保留的表是哪一個。
OUTER JOIN不存在隱式表達法。
LEFT OUTER JOIN
表A和表B的LEFT OUTER JOIN結(jié)果中總會保留左側(cè)的A表的所有記錄，即便A中的一些行沒有B中的行與之對應(yīng)。此時構(gòu)造的新行中，所有的原B中的列的值會被填充為NULL。
如下例（OUTER關(guān)鍵字可以省略）：
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
篩選結(jié)果如下：
RIGHT OUTER JOIN
這個就好理解了，和LEFT OUTER JOIN是反著來的。如下面的例子。
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
查詢的結(jié)果如下圖：
FULL OUTER JOIN
FULL OUTER JOIN同時保留兩個表的列.
SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
有些數(shù)據(jù)庫不支持FULL OUTER JOIN，不過你可以通過INNER JOIN加上UNION ALL來實現(xiàn)同樣的功能。下面給出了一個例子：
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的表其實是同一個表。例如，考慮下面這個表：
我們想要查找出來有哪些用戶是來自同一個國家。也就是要講上面這個表和它自身以Coutry這一列為基礎(chǔ)進行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;
查詢結(jié)果如下：
SQLAlchemy的教程第三部分快要出來了，歡迎大家關(guān)注~
