引言

本篇文章承接《數據庫與SQL語句》專欄，進入DQL的重要環節，可以說，這一部分的內容應該占據SQL語言的大部分使用場景。

本篇的連接查詢知識，和后面的一些重要的查詢知識總結，共同構成了在工作中80%的MySQL應用場景。應該算是基礎且重要的部分。

同時，我還希望能夠通過更加簡潔的語言總結和歸納出從需求快速定位SQL模板的口訣，比如有一個非常復雜的查詢需求，那么如何通過有效的思考路徑快速寫出準確無誤的SQL語句，也將是查詢語句知識總結的重點！

一、笛卡爾積

首先，多表存儲的目的是為了節省存儲空間，避免不必要的重復數據存儲于多個表中，同時便于維護。因此往往會通過“相同字段”相互關聯的方式設計數據庫表，這種關聯是人為定義的，并不需要MySQL來進行某種支持。

基于此，在數學中有個叫做笛卡爾積的數學概念，它指的是一個集合中的元素和另一個集合中的元素分別匹配組成不同的條目，從而構成更大的集合。

在多表查詢的時候，如何完全不加任何條件，只是單純的將兩表數據進行查詢，就會出現笛卡爾乘積現象，即一張表中的所有數據去逐條匹配另一張表中的所有數據，其最終結果的記錄條數一定是兩表的記錄數的乘積。比如：

SELECT * FROM student, class

上述SQL語句會將兩個表的所有記錄全部匹配。

二、連接查詢的分類

SQL標準根據不同的年份，分別推出了兩套標準：SQL92 和 SQL 99。

SQL92，1992年推出，它包含的連接有：

內連接（等值、非等值、自連接）

外連接（廢棄）

SQL99，1999年推出，它包含的連接有：

交叉連接（笛卡爾積）

自然連接（不推薦）

內連接（等值、非等值、自連接）

外連接（最常用左外連，不常用右外連，MySQL不支持全外連）

各連接查詢結果示意圖，紅色部分代表查詢結果：

2.1 SQL92等值連接

等值連接是內連接的一種。是基于笛卡爾積實現的一種最基本的多表查詢方式。如：

SELECT * FROM emp e, dept d WHERE e.`dept_id` = d.`id`;

其特點是，先通過笛卡爾積將兩表相乘，然后通過篩選條件進行等值篩選。

但通過等值篩選最好指定具體的查詢列表，否則會將含義重復的列都查詢出來，如上圖中的 dept_id 和 id 都代表部門 id。查詢列表的字段可以不指定表名，但效率低，另外注意，如果兩表某個字段名相同，必須指定表名。形式是“表名.字段名”。

2.2 SQL92非等值連接

非等值連接也是內連接的一種。同樣基于笛卡爾積。如，有 emp （左）和 salary_grade （薪水級別，右）表：

篩選薪水在 B 級別以上的員工：

SELECT e.*,s.`grade_name`,s.`salary` 標準 FROMemp e,salary_grade s WHERE e.`salary` >= s.`salary` AND s.`grade_name` = 'B' ;

非等值連接用于表與表之間沒有明確的對應關系，且通常會進行一個范圍的篩選的情況。

2.3 SQL92自連接

自連接也是內連接的一種，其含義是表與其自身做笛卡爾積。emp 表如下：

其中 ，manager_id 代表上級領導的 emp_id ，查詢員工及其上級領導的信息：

SELECT e.`emp_id` AS 員工id,e.`emp_name` AS 員工姓名,m.`emp_name` AS 上級姓名,m.`emp_id` AS 上級id,e.`salary` AS 員工薪水,e.`dept_id` AS 部門id FROMemp e,emp m WHERE e.`manager_id` = m.`emp_id` ;

自連接通常在表中的記錄本身存在級聯關系的情況下使用，如省市表、員工表等。

2.4 SQL92 外連接

SQL92標準的外連接目前已經基本廢棄，簡單了解即可，mysql無法執行這樣的語句。

-- 左外連 SELECT * FROM emp e, dept d WHERE e.`dept_id` = d.`id`(+);-- 右外連 SELECT * FROM emp e, dept d WHERE e.`dept_id`(+) = d.`id`;

2.5 SQL99 交叉連接

交叉連接是SQL99 標準下的笛卡爾積實現，采用 CROSS JOIN 關鍵字：

SELECT * FROM emp e CROSS JOIN dept d;

等價于：

SELECT * FROM emp e, dept d;

同時，SQL99? 的連接使用 ON 關鍵字進行等值篩選：

SELECT * FROMemp e CROSS JOIN dept d ON e.`dept_id` = d.`id` ;

2.6 SQL99 自然連接

自然連接關鍵字是 NATURAL JOIN ，會按照同名、同值字段，自動進行等值連接。但是限制較多，且經常需要配合 USING 關鍵字指定具體字段來使用，這里忽略介紹，不推薦使用。

2.7 SQL99 內連接

SQL99 對使用 INNER JOIN 來描述等值、非等值和自連接，其中 INNER 可以省略。連接條件必須使用 ON 來約束，下面以自連接進行舉例，等值與非等值省略。（表結構見2.3 節），

SELECT e.`dept_id`,e.`emp_name`,m.`emp_name`,m.`emp_id`,e.`salary`,e.`dept_id` FROMemp e INNER JOIN emp m ON e.`manager_id` = m.`emp_id` ;

2.8 SQL99 外連接（重點）

SQL99 的外連接總共分為三類：左外連接、右外連接、全外連接。使用關鍵字 OUTER JOIN 連接兩表，其中 OUTER 可以省略。

左外聯使用 LEFT JOIN ... ON ... 來關聯兩表，右外聯使用 RIGHT JOIN ... ON ... 來關聯兩表，全外聯使用 FULL JOIN ... ON ...

來關聯兩表，但是全外聯 MySQL不支持，可以通過：左外連 UNION 右外連 替代。

左外連會將 JOIN 左邊的表作為主表，將右邊的表中與主表有關聯的數據查詢出來，沒有關聯關系的，則不查詢；右外連正好相反。來看下面的兩張表，還是 emp (員工表) 和 dept (部門表)：

? ? ? ? ??? ? ? ??

左外連：

SELECT * FROMemp e LEFT JOIN dept d ON e.`dept_id` = d.`id` ;

emp 作為主表被全查了出來，并且將 dept 中相關聯記錄查詢出，沒有關聯的兩個部門：財務、市場部并沒有被查詢出來。

右外連：

SELECT * FROMemp e RIGHT JOIN dept d ON e.`dept_id` = d.`id` ;

dept 作為主表自然也是全部查出，就連沒有員工的兩個部門：財務、市場部，也一并被查詢出來。

因此，我們可以看出，一般情況下，只用 LEFT JOIN ，就可以完成兩種不同的效果，只需要將表的前后位置調換即可。在實際開發當中 LEFT JOIN 也是要比 RIGHT JOIN 使用頻率更多。

總結

1、笛卡爾積是實現連接查詢的數學模型，它代表兩表相乘。

2、SQL92語法可以查詢最簡單的等值、非等值和自連接，三者都被稱為內連接，即兩表集合的交集。

3、SQL99語法全面支持了外連接，可讀性更強，其中左外連接查詢是學習重點。

4、SQL99 的連接查詢必須通過 ON 關鍵字來指定連接條件，與SQL99 的連接查詢不同，將連接條件從 WHERE 子句中剝離出來是SQL 99 語法的重要標志。

鳴謝：

《SQL92&SQL99實現多表查詢》

《MySQL基礎系列教程》

《Mysql實現全外部連接（mysql無法使用full join的解決辦法）》

《MySql（十二）Sql92和Sql99的區別》

《sql92和sql99》

總結

以上是生活随笔為你收集整理的MySQL 基础 ———— 连接查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。