SQL-JOIN全決議
- 一、SQL JOIN的作用是什么？
- 二、四種JOIN的區別
- 三、如何使用各種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
CSDN認證博客專家
Linux
分布式
Java
不荒廢現在，不畏懼未來！我認為把知識給別人講會，講明白，自己才徹底明白，努力把文章寫好，寫明白每一篇文章，分享給更多人，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/167422.html
標籤：其他
上一篇：2020-10-08