神奇的 SQL 之團結的力量 → JOIN

前言

　　開心一刻

　 　　閨蜜家暴富，買了一棟大別野，喊我去吃飯，菜挺豐盛的，筷子有些不給力，銀筷子，好重，我說換個竹子的，閨蜜說，這種銀筷子我家總共才五雙，只有貴賓才能用~我咬著牙享受著貴賓待遇，終于，在第三次夾蝦排滑落盤子時，我爆發了：去它喵的貴賓，我要蝦排……不是……我要竹筷子！

連接

　　簡單來說，就是將其他表中的列添加過來，進行"添加列"的運算，如下圖所示，

　　為什么需要進行"添加列"的操作 了？ 因為我們在設計資料庫的時候，往往需要滿足范式（具體滿足范式幾，無法一概而論，這里不做細究），會導致我們某個需求的全部列分散在不同的表中，所以為了滿足需求，我們需要將某些表的列進行連接，我們來看個簡單例子，假如我們有兩張表（t_user，t_login_log）：

DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  sex TINYINT(1) NOT NULL COMMENT '性別, 1:男，0:女',
  age TINYINT(3) UNSIGNED NOT NULL COMMENT '年齡',
  phone_number VARCHAR(11) NOT NULL DEFAULT '' COMMENT '電話號碼',
  email VARCHAR(50) NOT NULL DEFAULT '' COMMENT '電子郵箱',
  create_time datetime NOT NULL COMMENT '創建時間',
  update_time datetime NOT NULL COMMENT '更新時間',
  PRIMARY KEY (id)
) COMMENT='用戶表';

DROP TABLE IF EXISTS t_login_log;
CREATE TABLE t_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
  user_name VARCHAR(50) NOT NULL COMMENT '用戶名',
  ip VARCHAR(15) NOT NULL COMMENT '登錄IP',
  client TINYINT(1) NOT NULL COMMENT '登錄端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '創建時間',
  PRIMARY KEY (id)
) COMMENT='登錄日志';

INSERT INTO t_user(user_name, sex, age, phone_number,email,create_time,update_time) VALUES
('Bruce Lee', 1, 32, '15174480987', '[email protected]', NOW(), NOW()),
('Jackie Chan', 1, 65, '15174481234', '[email protected]', NOW(), NOW()),
('Jet Li', 1, 56, '15174481245', '[email protected]', NOW(), NOW()),
('Jack Ma', 1, 55, '15174481256', '[email protected]', NOW(), NOW()),
('Pony', 1, 48, '15174481278', '[email protected]', NOW(), NOW()),
('Robin Li', 1, 51, '15174481290', '[email protected]', NOW(), NOW());

INSERT INTO t_login_log(user_name, ip, client, create_time) VALUES
('Jackie Chan', '10.53.56.78',2, '2019-10-12 12:23:45'),
('Jackie Chan', '10.53.56.78',2, '2019-10-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2018-08-12 22:23:45'),
('Jet Li', '10.53.56.12',1, '2019-10-19 10:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-05-12 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2018-11-11 22:23:45'),
('Jack Ma', '198.11.132.198',2, '2019-06-18 22:23:45'),
('Robin Li', '220.181.38.148',3, '2019-10-21 09:45:56'),
('Robin Li', '220.181.38.148',3, '2019-10-26 22:23:45'),
('Pony', '104.69.160.60',4, '2019-10-12 10:23:45'),
('Pony', '104.69.160.60',4, '2019-10-15 20:23:45');

　　如果我們需要展示如下串列（需求：展示用戶串列，并顯示其最近登錄時間、最近登錄 IP），那么就需要 t_user 和 t_login_log 連表查了

　　連接的型別有很多種，細分如下圖

交叉連接

　　講交叉連接之前了，我們先來看看笛卡爾積，假設我們兩個集合，集合A={a, b}，集合B={0, 1, 2}，則A與B的笛卡爾積為{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}，表示為AxB，也就是集合A中的任一元素與集合B的每個元素組合后的新集合則為A與B的笛卡爾積（AxB），數學上的笛卡爾積反映到資料庫中就是交叉連接（CROSS JOIN），結合上述的案例如下：

SELECT * FROM t_user CROSS JOIN t_login_log;

-- 與 CROSS JOIN 得到的結果相同
-- 過時的寫法，不符合 SQL標準，能讀懂就好，不推薦使用
SELECT * FROM t_user, t_login_log;

　　t_user 中有 6 條記錄， t_login_log 中有 11 條記錄，t_user CROSS JOIN t_login_log 的結果是 66（ 6 乘以 11） 條記錄

　　交叉連接就是對兩張表中的全部記錄進行交叉組合，因此其結果是兩張表的乘積，這也是為什么交叉連接無法使用內連接或外連接中所使用的 ON 子句的原因，交叉連接基本不會應用到實際業務之中，原因有兩個，一是其結果沒有實用價值，二是結果行數太多，需要花費大量的運算時間和硬體資源，雖說交叉連接的實際使用場景幾乎沒有，但還是有它的理論價值的，交叉連接是其他所有連接運算的基礎，內連接是交叉連接的一部分，其結果是交叉連接的一部分（子集），外連接有點特殊，其結果包含交叉連接之外的內容；更多詳情，我們接著往下看，

內連接

　　只回傳兩張表匹配的記錄，就叫內連接，直觀的表現就是關鍵字：INNER JOIN ... ON，ON 表示兩張表連接所使用的列（連接鍵）；而內連接中又屬等值連接最常用

　　等值連接

　　　　簡單點來說，就是連接鍵相等

-- 等值連接
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- INNER JOIN 可以簡寫成 JOIN
SELECT * FROM t_user tu JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

-- 不加連接鍵, 結果與 CROSS JOIN 一樣
SELECT * FROM t_user tu INNER JOIN t_login_log ttl

　　　　等值連接的結果中，每一條記錄的連接鍵的列的值是想等的，如上圖中的 user_name 和 user_name1（為了區別于第一個user_name，資料庫系統自動取的別名，我們可以顯示的指定）

　　不等值連接

　　　　連接鍵的比較謂詞除了 = 之外的所有情況，比如 >、<、<>（!=）；不等值連接使用場景比較少，反正我在實際作業中幾乎沒用到過

SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name <> ttl.user_name;
SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name > ttl.user_name;

　　自然連接

　　　　不需要指定連接條件，資料庫系統會自動用相同的欄位作為連接鍵，直觀的表現就是關鍵字：NATURAL JOIN，NATURAL LEFT JOIN、NATURAL RIGHT JOIN；

　　　　連接鍵不直觀，需要去看兩張表中相同的欄位有哪些；對于自然連接，了解即可，不推薦使用，反正我作業這么久，一次都沒用過，

外連接

　　外連接的使用方式與內連接一樣，也是通過 ON 使用連接鍵將兩張表連接，從結果中獲取我們想要的資料，但是回傳的結果與內連接有區別，具體我們往下看

　　左連接

　　　　回傳匹配的記錄，以及左表多余的記錄，關鍵字：LEFT JOIN（LEFT OUTER JOIN 的簡寫）

SELECT * FROM t_user tu LEFT OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- LEFT JOIN 是 LEFT OUTER JOIN 的簡寫
SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　　　上圖中，前 11 條記錄是匹配的記錄，而第 12 條是不匹配、左表的記錄

　　右連接

　　　　回傳匹配的記錄，以及表 B 多余的記錄，關鍵字：RIGHT JOIN（RIGHT OUTER JOIN 的簡寫）

SELECT * FROM t_login_log ttl RIGHT OUTER JOIN t_user tu ON tu.user_name = ttl.user_name;
-- RIGHT JOIN 是 RIGHT OUTER JOIN 的簡寫
SELECT * FROM t_login_log ttl RIGHT JOIN t_user tu ON tu.user_name = ttl.user_name;

　　　　由于我們習慣了從左往右（閱讀方式、寫作方式），因此在實際專案中，基本上用的都是左連接

　　全連接

　　　　回傳匹配的記錄，以及左表和右表各自的多余記錄，關鍵字：FULL JOIN （FULL OUTER JOIN 的簡寫）

SELECT * FROM t_user tu FULL OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- FULL JOIN 是 FULL OUTER JOIN 的簡寫
SELECT * FROM t_user tu FULL JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　　　注意：MySQL 不支持 全連接，我們可以通過 左連接、右連接之后，再 UNION 來實作全連接

自連接

　　一張表，自己連接自己，簡單點來理解就是，左表、右表是同一張表；連接方式可以是內連接、也可以是外連接

　　更多詳情大家可以去看：專案上線后，談一下感觸比較深的一點：查詢優化

需求：展示用戶串列，并顯示最近登錄時間、最近登錄 IP

　　對于此需求，大家會如何來寫這個 SQL ？ 也許大家很容易想到左連接，如下所示

SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　可結果如下：

　　顯示的是每個用戶的所有登錄日志，不是我們想要的結果；原因是 t_user 中的一條記錄在 t_login_log 對應的記錄有多種情況：0 條對應、1 條對應、多條對應，那這個 SQL 要怎么寫呢，方式有多種，不局限于如下實作

-- 1、連接配合子查詢，注意 Bruce Lee 從未登陸過
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN t_login_log tll ON tu.user_name = tll.user_name
WHERE tll.id = (SELECT MAX(id) FROM t_login_log WHERE user_name = tu.user_name) OR tll.user_name IS NULL;

-- 2、t_login_log分組統計出各個用戶的最近一次登錄資訊后，再與 t_user 聯表
SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
FROM t_user tu LEFT JOIN (
    SELECT tb.* FROM(
        SELECT user_name, MAX(id) id FROM t_login_log GROUP BY user_name
    ) ta LEFT JOIN t_login_log tb ON ta.id = tb.id
) tll ON tu.user_name = tll.user_name;

　　具體的實作還得結合具體的業務和需求來實作，那樣才能寫出高效的 SQL；另外結合執行計劃來建立合適的索引，總之，沒有一成不變的、通用的高效 SQL，結合具體的業務才能寫出最合適的 SQL，

總結

　　1、連接的描述方式

　　　　常用的維恩圖，描述如下

　　　　維恩圖描述有他的優勢，但它不好表示交叉連接，同時容易讓人誤解成 SQL 中的集合操作；這里推薦另外一種描述方式，我覺得描述的更準確

　　　　CROSS JOIN

 　　　　常用 JOIN

 　　　　上圖中，顏色表示匹配關系，顏色相同表示匹配，回傳結果中，如果另一張表沒有匹配的記錄，則用 null 填充， 在上圖中則表示為空白，

　　2、連接中 ON 指定連接鍵，連接鍵可以指定多個，而 WHERE  還是平時的作用，用來指定過濾條件；不推薦將連接鍵放于 WHERE 后；

　　3、實際作業中，用的最多的是 左連接 和 等值連接，其他的用的特別少

參考

　　《SQL基礎教程》

　　《SQL進階教程》

標籤：MySQL

上一篇：MySQL Execution Plan--COUNT相關測驗

下一篇：Python外星人入侵groupcollide函式問題