技術文章 » SQL-JOIN全解析

SQL-JOIN全解析

2020-10-10 17:00:04

SQL-JOIN全解析

一、SQL JOIN的作用是什麼？

SQL JOIN的作用就是把來自多個表的資料行，根據一定的規則連線起來，形成一張大的資料表。

例如下面這張用爛了的圖，可以幫你快速理解每個join用法的效果：

這張圖描述了left join（左連線）、right join（右連線） 、inner join（內連線）、outer join（外連線）相關的7種用法。

在這裡插入圖片描述

我改了一版：

感覺更方便理解了

在這裡插入圖片描述

可以關注我公眾號，回覆「mysql」，可以拿到高清大圖

二、四種JOIN的區別

  • 1、INNER JOIN：如果表中有至少一個匹配，則返回行；
  • 2、LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行；
  • 3、RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行；
  • 4、FULL JOIN：只要其中一個表中存在匹配，則返回行
    。

三、如何使用各種join

（一）準備測試資料

測試的資料很簡單，依舊拿來在課堂上，書本上用到的老一套的資料表，學生表和成績表來實現。

1、學生表：

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '學號',
  `sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '學生姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '202001', '張三');
INSERT INTO `student` VALUES (2, '202002', '李四');
INSERT INTO `student` VALUES (3, '202003', '王五');
INSERT INTO `student` VALUES (4, '202004', '趙六');
INSERT INTO `student` VALUES (5, '202005', '小明');
INSERT INTO `student` VALUES (6, '202006', '小紅');
INSERT INTO `student` VALUES (7, '202007', '小剛');
INSERT INTO `student` VALUES (8, '202008', '小李');

SET FOREIGN_KEY_CHECKS = 1;

2、成績表：

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sno` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '學號',
  `courseName` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '課程名',
  `grade` double(3, 0) NULL DEFAULT NULL COMMENT '成績',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES (1, '202001', '高數一', 90);
INSERT INTO `grade` VALUES (2, '202003', '高數二', 88);
INSERT INTO `grade` VALUES (3, '202003', '英語一', 77);
INSERT INTO `grade` VALUES (4, '202004', '英語二', 79);
INSERT INTO `grade` VALUES (5, '202002', 'C++語言設計', 87);
INSERT INTO `grade` VALUES (6, '202005', 'Java物件導向基礎', 98);
INSERT INTO `grade` VALUES (7, '202006', '演演算法分析與實現', 76);
INSERT INTO `grade` VALUES (8, '202007', '軟體工程A', 65);
INSERT INTO `grade` VALUES (9, '202007', '計算機應用與基礎', 59);

SET FOREIGN_KEY_CHECKS = 1;

現在的資料如下：

mysql> select * from grade;
+----+--------+------------------+-------+
| id | sno    | courseName       | grade |
+----+--------+------------------+-------+
|  1 | 202001 | 高數一           |    90 |
|  2 | 202003 | 高數二           |    88 |
|  3 | 202003 | 英語一           |    77 |
|  4 | 202004 | 英語二           |    79 |
|  5 | 202002 | C++語言設計      |    87 |
|  6 | 202005 | Java物件導向基礎 |    98 |
|  7 | 202006 | 演演算法分析與實現   |    76 |
|  8 | 202007 | 軟體工程A        |    65 |
|  9 | 202007 | 計算機應用與基礎 |    59 |
+----+--------+------------------+-------+
9 rows in set (0.12 sec)

mysql> 
mysql> select * from student;
+----+--------+-------+
| id | sno    | sname |
+----+--------+-------+
|  1 | 202001 | 張三  |
|  2 | 202002 | 李四  |
|  3 | 202003 | 王五  |
|  4 | 202004 | 趙六  |
|  5 | 202005 | 小明  |
|  6 | 202006 | 小紅  |
|  7 | 202007 | 小剛  |
|  8 | 202008 | 小李  |
+----+--------+-------+
8 rows in set (0.12 sec)

mysql>

資料結構如下：

mysql> 
mysql> desc grade;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno        | varchar(20) | YES  |     | NULL    |                |
| courseName | varchar(20) | YES  |     | NULL    |                |
| grade      | double(3,0) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.09 sec)

mysql> 
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| sno   | varchar(20) | YES  |     | NULL    |                |
| sname | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.10 sec)

mysql>

（二）左連線

在7種join的用法中，左連線的用法有兩種，如下圖所示：
在這裡插入圖片描述

第一種：

mysql> select * from student t1 
    -> left join grade t2 
    -> on t1.sno=t2.sno;
+----+--------+-------+------+--------+------------------+-------+
| id | sno    | sname | id   | sno    | courseName       | grade |
+----+--------+-------+------+--------+------------------+-------+
|  1 | 202001 | 張三  |    1 | 202001 | 高數一           |    90 |
|  3 | 202003 | 王五  |    2 | 202003 | 高數二           |    88 |
|  3 | 202003 | 王五  |    3 | 202003 | 英語一           |    77 |
|  4 | 202004 | 趙六  |    4 | 202004 | 英語二           |    79 |
|  2 | 202002 | 李四  |    5 | 202002 | C++語言設計      |    87 |
|  5 | 202005 | 小明  |    6 | 202005 | Java物件導向基礎 |    98 |
|  6 | 202006 | 小紅  |    7 | 202006 | 演演算法分析與實現   |    76 |
|  7 | 202007 | 小剛  |    8 | 202007 | 軟體工程A        |    65 |
|  7 | 202007 | 小剛  |    9 | 202007 | 計算機應用與基礎 |    59 |
|  8 | 202008 | 小李  | NULL | NULL   | NULL             | NULL  |
+----+--------+-------+------+--------+------------------+-------+
10 rows in set (0.10 sec)

mysql>

從上面結果中可以看到我們學生中有小李，但是成績表中並沒有小李的成績。所以會出現null的情況。


這也驗證了我們前面所述的一句話：
LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行；

即使成績表中沒有匹配的資料，也從左表返回所有的行。

那麼在大多數情況下，我們是不讓顯示null的資料的，那該怎麼辦？
很簡單，可以調換一下t1和t2的位置即可，如下實驗效果：

mysql> 
mysql> select * from grade t1 
    -> left join student t2 
    -> on t1.sno=t2.sno;
+----+--------+------------------+-------+----+--------+-------+
| id | sno    | courseName       | grade | id | sno    | sname |
+----+--------+------------------+-------+----+--------+-------+
|  1 | 202001 | 高數一           |    90 |  1 | 202001 | 張三  |
|  5 | 202002 | C++語言設計      |    87 |  2 | 202002 | 李四  |
|  2 | 202003 | 高數二           |    88 |  3 | 202003 | 王五  |
|  3 | 202003 | 英語一           |    77 |  3 | 202003 | 王五  |
|  4 | 202004 | 英語二           |    79 |  4 | 202004 | 趙六  |
|  6 | 202005 | Java物件導向基礎 |    98 |  5 | 202005 | 小明  |
|  7 | 202006 | 演演算法分析與實現   |    76 |  6 | 202006 | 小紅  |
|  8 | 202007 | 軟體工程A        |    65 |  7 | 202007 | 小剛  |
|  9 | 202007 | 計算機應用與基礎 |    59 |  7 | 202007 | 小剛  |
+----+--------+------------------+-------+----+--------+-------+
9 rows in set (0.16 sec)

mysql>

此處是重點：在Mysql5.7的官方手冊中也提及到，這個優化的方式：

At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed such that this right join:

在解析器階段，具有右外部連線的查詢會被轉換為僅包含左連線操作的相等查詢。
在一般情況下，左連線會轉換成右連線

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

Becomes this equivalent left join:
變成下面這個等價的左連線：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

可以看到在轉換的時候，會把t1變成t2，把t2的位置換成t1的位置。

第二種：

mysql> 
mysql> select * from student t1 
    -> left join grade t2 
    -> on t1.sno=t2.sno
    -> where t2.sno is null;
+----+--------+-------+------+------+------------+-------+
| id | sno    | sname | id   | sno  | courseName | grade |
+----+--------+-------+------+------+------------+-------+
|  8 | 202008 | 小李  | NULL | NULL | NULL       | NULL  |
+----+--------+-------+------+------+------------+-------+
1 row in set (19.59 sec)

mysql>

從結果上看，很清楚，只查出來了sno為null的資料。
如果not null呢？

mysql> 
mysql> select * from student t1 
    left join grade t2 
    on t1.sno=t2.sno
    where t2.sno is NOT null;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 張三  |  1 | 202001 | 高數一           |    90 |
|  3 | 202003 | 王五  |  2 | 202003 | 高數二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英語一           |    77 |
|  4 | 202004 | 趙六  |  4 | 202004 | 英語二           |    79 |
|  2 | 202002 | 李四  |  5 | 202002 | C++語言設計      |    87 |
|  5 | 202005 | 小明  |  6 | 202005 | Java物件導向基礎 |    98 |
|  6 | 202006 | 小紅  |  7 | 202006 | 演演算法分析與實現   |    76 |
|  7 | 202007 | 小剛  |  8 | 202007 | 軟體工程A        |    65 |
|  7 | 202007 | 小剛  |  9 | 202007 | 計算機應用與基礎 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.46 sec)

mysql>

這就很神奇了，居然和我們上一種想要的最終結果一樣。那麼這個sql就是捨棄掉了為null的資料。

（三）右連線

在這裡插入圖片描述

這個的用法和左連線正好相反，可以在腦子中想想一下。

不羅嗦了，直接看效果吧。

mysql> 
mysql> select * from student t1 
    right join grade t2 
    on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 張三  |  1 | 202001 | 高數一           |    90 |
|  2 | 202002 | 李四  |  5 | 202002 | C++語言設計      |    87 |
|  3 | 202003 | 王五  |  2 | 202003 | 高數二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英語一           |    77 |
|  4 | 202004 | 趙六  |  4 | 202004 | 英語二           |    79 |
|  5 | 202005 | 小明  |  6 | 202005 | Java物件導向基礎 |    98 |
|  6 | 202006 | 小紅  |  7 | 202006 | 演演算法分析與實現   |    76 |
|  7 | 202007 | 小剛  |  8 | 202007 | 軟體工程A        |    65 |
|  7 | 202007 | 小剛  |  9 | 202007 | 計算機應用與基礎 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (19.49 sec)

mysql>

從上面效果上可以看到只匹配到了成績表中有的資料，小李就沒有顯示。
也驗證了：
RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行；

（四）內連線

在這裡插入圖片描述

INNER JOIN：如果表中有至少一個匹配，則返回行；

mysql> select * from student t1 
    inner join grade t2 
    on t1.sno=t2.sno;
+----+--------+-------+----+--------+------------------+-------+
| id | sno    | sname | id | sno    | courseName       | grade |
+----+--------+-------+----+--------+------------------+-------+
|  1 | 202001 | 張三  |  1 | 202001 | 高數一           |    90 |
|  3 | 202003 | 王五  |  2 | 202003 | 高數二           |    88 |
|  3 | 202003 | 王五  |  3 | 202003 | 英語一           |    77 |
|  4 | 202004 | 趙六  |  4 | 202004 | 英語二           |    79 |
|  2 | 202002 | 李四  |  5 | 202002 | C++語言設計      |    87 |
|  5 | 202005 | 小明  |  6 | 202005 | Java物件導向基礎 |    98 |
|  6 | 202006 | 小紅  |  7 | 202006 | 演演算法分析與實現   |    76 |
|  7 | 202007 | 小剛  |  8 | 202007 | 軟體工程A        |    65 |
|  7 | 202007 | 小剛  |  9 | 202007 | 計算機應用與基礎 |    59 |
+----+--------+-------+----+--------+------------------+-------+
9 rows in set (0.12 sec)

mysql>

（五）外連線

這一種在Mysql中是不支援的，可以在SQL Server上測試。這裡就不測試了。

四、總結

  • 1、INNER JOIN：如果表中有至少一個匹配，則返回行；
  • 2、LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行；
  • 3、RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行；
  • 4、FULL JOIN：只要其中一個表中存在匹配，則返回行
    。

歡迎一起學習，一起交流，一起進步。

關注我微信公眾號第一時間推播給你精彩內容哦：

回覆選單，更有好禮，驚喜在等著你。

在這裡插入圖片描述

快來我粉絲群：每天歡快的玩耍（微信掃描二維條碼即可加入，群馬上滿，抓緊啦！！！）
在這裡插入圖片描述

TrueDei CSDN認證部落格專家 Linux 分散式 Java
不荒廢現在，不畏懼未來！我認為把知識給別人講會，講明白，自己才徹底明白。努力把文章寫好，寫明白每一篇文章，分享給更多人。