1. 前言

• 多表查詢，也稱為關(guān)聯(lián)查詢.指兩個(gè)或兩個(gè)以上的表一起完成查詢操作.
• 前提條件 : 這些一起查詢的表之間是有關(guān)系的(一對(duì)一/一對(duì)多).他們之間一定是有關(guān)聯(lián)字段，這個(gè)關(guān)聯(lián)字段可能建立了外鍵，也可能沒有建立外鍵.

2. 笛卡爾積現(xiàn)象(交叉連接)

(1).例 :?

如果我們?cè)趦蓚€(gè)表中未進(jìn)行條件關(guān)聯(lián)，直接查找，可能會(huì)出現(xiàn)笛卡爾積現(xiàn)象.即第一張表的一個(gè)數(shù)據(jù)需要跟第二張表的所有數(shù)據(jù)匹配.又稱交叉連接.

• 圖中2889條數(shù)據(jù)＝第一張表的記錄數(shù)X第二張表的記錄數(shù).
• CROSS JOIN的作用就是可以把任意表進(jìn)行連接，即使這兩張表不相關(guān).
• 為了避免出現(xiàn)笛卡爾積現(xiàn)象，我們可以在WHERE子句中加入有效的條件.

3. 帶有連接條件的多表查詢

例 :?

• 從第一張表的第一條記錄開始，與第二章表的所有記錄進(jìn)行條件關(guān)聯(lián)，剩下的是滿足關(guān)聯(lián)條件的記錄. 只剩下106條記錄.因?yàn)閑mployees表中第一條記錄的departmentid字段為null.
• FROM子句中，可以給表起別名.一旦起了別名，后續(xù)WHERE子句中，就不能使用以前的表名了.相當(dāng)于表的別名對(duì)原先的表名進(jìn)行了覆蓋.但字段的別名不會(huì)對(duì)原字段進(jìn)行覆蓋.

4. 多表查詢的分類

• 等值連接與非等值連接
• 自連接與非自連接
• 內(nèi)連接與外連接

5. 等值連接與非等值連接

(1).等值連接 :?

上述例就是等值連接.因?yàn)檫B接條件是＝運(yùn)算符.

(2). 非等值連接 :?

自然而然，連接條件不是＝運(yùn)算符的，即是非等值連接.比如 :?

• 當(dāng)查詢的字段是兩個(gè)表中的共有字段時(shí)，需要指定是哪個(gè)表中的字段.不然會(huì)報(bào)錯(cuò).
• 對(duì)于數(shù)據(jù)庫(kù)中表記錄的查詢和變更，只要涉及多個(gè)表，都需要在列名前加表的別名進(jìn)行限定(如d.locationid).

6. 自連接與非自連接

(1). 自連接

例 :?

• 自連接顧名思義，自己的表與自己連接查詢.
• emp與mana本質(zhì)上是一張表(物理磁盤上只有一張表)，只是用取別名的方式在邏輯上虛擬成兩個(gè)表代表不同的意義，然后兩個(gè)表進(jìn)行內(nèi)連接，外連接.

(2) 非自連接

上述例均為非自連接.

7. 內(nèi)連接與外連接

(1). 內(nèi)連接?: (數(shù)學(xué)中的交集)

上述我們涉及到的全部例子均是內(nèi)連接.

合并具有同一列的兩個(gè)以上的行，結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行.

格式為 :

SELECT 字段列表

# INNER可省略.

FROM A表 JOIN B表

ON 關(guān)聯(lián)條件

WHERE 等其他子句.

例 :?

只能查詢到滿足關(guān)聯(lián)條件的記錄，不能查詢到不滿足條件的記錄.

還可以多個(gè)表內(nèi)連接.

(2). 外連接

• 外連接分為 : 左外連接，右外連接，滿外連接.
• 左外連接(LEFT OUTER JOIN / LEFT JOIN) : 結(jié)果集中不僅包含了兩個(gè)表中滿足連接條件的記錄，還包含了左表中不滿足連接條件的記錄.
• 右外連接(RIGHT?OUTER JOIN / RIGHT JOIN) : 結(jié)果集中不僅包含了兩個(gè)表中滿足連接條件的記錄，還包含了右表中不滿足連接條件的記錄.
• 滿外連接 : 結(jié)果集中不僅包含了兩個(gè)表中滿足條件的記錄，還包含了左表中不滿足條件的記錄+右表中不滿足條件的記錄.

8. 左外連接與右外連接

(1). 左外連接

例 :?

可以看到有107條記錄，而上述內(nèi)連接的情況下只有106條記錄.可知左外連接包含了左表不滿足條件的記錄.

(2). 右表連接

右表連接與左表連接類似.

格式 :?

SELECT 查詢字段

FROM A表

RIGHT JOIN B表

ON 連接條件

WHERE 其他子句.

9. 滿外連接與UNION關(guān)鍵字

• SQL99是支持滿外連接的.即使用FULL JOIN/FULL OUTER JOIN來實(shí)現(xiàn)
• 但MySQL并不支持這種寫法.但可以使用LEFT JOIN UNION RIGHT JOIN代替.

10. UNION的使用

(1). 合并查詢結(jié)果 : 利用UNION關(guān)鍵字，可以給出多條SELECT語句，并將他們的結(jié)果組合成單個(gè)結(jié)果集.合并時(shí)，兩個(gè)表對(duì)應(yīng)的列數(shù)和數(shù)據(jù)類型必須相同.并且相互對(duì)應(yīng).各個(gè)SELECT語句間使用UNION/UNION ALL關(guān)鍵字分隔.

(2). UNION操作符

返回兩個(gè)查詢的結(jié)果集的并集，并去重復(fù)記錄.

(3). UNION ALL操作符

返回兩個(gè)查詢的結(jié)果集的并集，但并不去重.

?

注意 : 執(zhí)行UNION ALL語句時(shí)所需要的資源比UNION語句少.如果明知合并數(shù)據(jù)候的結(jié)果集不存在重復(fù)數(shù)據(jù)，或不需要去重，則盡量使用UNION ALL語句，以提高查詢效率.

(4). 例 : UNION ALL實(shí)現(xiàn)連接 ： 對(duì)應(yīng)下表 左中?UNION ALL 右上 ---> 左下

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #沒有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`;

11. 七種SQL JOINS的實(shí)現(xiàn)

如圖 :?

• 左上 : 左外連接 LEFT JOIN
• 左中 : 左外連接+WHERE過濾匹配的行
• 左下 : 左外連接+WHERE過濾匹配的行 UNION ALL 右外連接 / 右外連接+WHERE過濾匹配的行 UNION ALL 左外連接
• 中上 : 內(nèi)連接 僅有匹配的行
• 右下 :?左外連接+WHERE過濾匹配的行 UNION ALL 右外連接+WHERE過濾匹配的行
• 右上 : 右外連接 RIGHT JOIN
• 右中 : 右外連接+WHERE過濾匹配的行