MySQL多表查詢的實(shí)現(xiàn)過程

更新時(shí)間：2025年10月09日 14:53:20 作者：洛克大航海

文章講解了SQL多表查詢的核心概念,包括笛卡爾積（交叉連接）、等值/非等值連接、自連接、內(nèi)/外連接分類,強(qiáng)調(diào)避免笛卡爾積需添加連接條件,區(qū)分列名并使用JOIN/ON語(yǔ)法,同時(shí)對(duì)比了UNION與UNION?ALL的區(qū)別,并提及MySQL中滿外連接的替代方法

多表查詢

1.1 笛卡爾積（或交叉連接）的理解

笛卡爾乘積是一個(gè)數(shù)學(xué)運(yùn)算。

假設(shè)我有兩個(gè)集合 X 和 Y，那么 X 和 Y 的笛卡爾積就是 X 和 Y 的所有可能組合，也就是第一個(gè)對(duì)象來自于 X，第二個(gè)對(duì)象來自于 Y 的所有可能。

組合的個(gè)數(shù)即為兩個(gè)集合中元素個(gè)數(shù)的乘積數(shù)。

SQL92中，笛卡爾積也稱為交叉連接，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉連接。

它的作用就是可以把任意表進(jìn)行連接，即使這兩張表不相關(guān)。

笛卡爾積的錯(cuò)誤會(huì)在下面條件下產(chǎn)生：

省略多個(gè)表的連接條件（或關(guān)聯(lián)條件）
連接條件（或關(guān)聯(lián)條件）無效
所有表中的所有行互相連接為了避免笛卡爾積，

可以在 WHERE 加入有效的連接條件。

在 WHERE子句中寫入連接條件。

在表中有相同列時(shí)，在列名之前加上表名前綴。

1.2 多表查詢分類講解

分類1：等值連接 vs 非等值連接

等值連接

拓展1：多個(gè)連接條件與 AND 操作符
拓展2：區(qū)分重復(fù)的列名多個(gè)表中有相同列時(shí)，必須在列名之前加上表名前綴。在不同表中具有相同列名的列可以用表名加以區(qū)分。
拓展3：表的別名使用別名可以簡(jiǎn)化查詢。列名前使用表名前綴可以提高查詢效率。
拓展4：連接多個(gè)表，總結(jié)：連接 n個(gè)表,至少需要n-1個(gè)連接條件。比如，連接三個(gè)表，至少需要兩個(gè)連接條件。

非等值連接

WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

分類2：自連接 vs 非自連接

自連接

當(dāng)table1和table2本質(zhì)上是同一張表，只是用取別名的方式虛擬成兩張表以代表不同的意義。然后兩個(gè)表再進(jìn)行內(nèi)連接，外連接等查詢

非自連接

分類3：外連接vs內(nèi)連接

內(nèi)連接: 合并具有同一列的兩個(gè)以上的表的行, 結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行
外連接: 兩個(gè)表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行，這種連接稱為左（或右）外連接。沒有匹配的行時(shí), 結(jié)果表中相應(yīng)的列為空(NULL)。
如果是左外連接，則連接條件中左邊的表也稱為主表，右邊的表稱為從表。
如果是右外連接，則連接條件中右邊的表也稱為主表，左邊的表稱為從表。

SQL99語(yǔ)法實(shí)現(xiàn)多表查詢使用JOIN…ON子句創(chuàng)建連接的語(yǔ)法結(jié)構(gòu)：

SELECT table1.column, table2.column,table3.column
FROM table1
   JOIN table2 ON table1 和 table2 的連接條件
   JOIN table3 ON table2 和 table3 的連接條件

語(yǔ)法說明：

可以使用 ON 子句指定額外的連接條件。

這個(gè)連接條件是與其它條件分開的。

ON 子句使語(yǔ)句具有更高的易讀性。

關(guān)鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內(nèi)連接

內(nèi)連接(INNER JOIN)的實(shí)現(xiàn)

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

外連接(OUTER JOIN)的實(shí)現(xiàn)
左外連接(LEFT OUTER JOIN)

```mysql
#實(shí)現(xiàn)查詢結(jié)果是A
 SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;
```

右外連接(RIGHT OUTER JOIN)

FROM A表 RIGHT JOIN B表
ON 關(guān)聯(lián)條件
WHERE 等其他子句;

滿外連接(FULL OUTER JOIN)

滿外連接的結(jié)果 = 左右表匹配的數(shù)據(jù) + 左表沒有匹配到的數(shù)據(jù) + 右表沒有匹配到的數(shù)據(jù)。

SQL99是支持滿外連接的。使用FULL JOIN 或 FULL OUTER JOIN來實(shí)現(xiàn)。需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION和UNION ALL的使用

UNION：會(huì)執(zhí)行去重操作
UNION ALL：不會(huì)執(zhí)行去重操作
注意：執(zhí)行UNION ALL語(yǔ)句時(shí)所需要的資源比UNION語(yǔ)句少。如果明確知道合并數(shù)據(jù)后的結(jié)果數(shù)據(jù) 不存在重復(fù)數(shù)據(jù)，或者不需要去除重復(fù)的數(shù)據(jù)，則盡量使用UNION ALL語(yǔ)句，以提高數(shù)據(jù)查詢的效率

7中JOIN的實(shí)現(xiàn)

#中圖：內(nèi)連接 A∩B
 SELECT employee_id,last_name,department_name
 FROM employees e JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
#左上圖：左外連接
SELECT employee_id,last_name,department_name
 FROM employees e LEFT JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
  #右上圖：右外連接
SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`;
 
  #左中圖：A - A∩B
 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
 
  #右中圖：B-A∩B
 SELECT employee_id,last_name,department_name
 FROM employees e RIGHT JOIN departments d
 ON e.`department_id` = d.`department_id`
 WHERE e.`department_id` IS NULL
 
  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 d
 ON e.`department_id` = d.`department_id`;
 
  #右下圖
#左中圖 + 右中圖  A ∪B- A∩B 或者 (A -  A∩B) ∪ （B - A∩B）
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 d
 ON e.`department_id` = d.`department_id`
 WHERE e.`department_id` IS NULL