Mysql—inner join/left join/right join等join的用法详解
站长
· 阅读数 18
关注微信公众号：CodingTechWork，一起学习进步。
引言
一直以来对join的几种用法都混淆，这次在别人的hive sql中看到join用法便研究总结了一下，方便后续查阅和使用。
sql join介绍
概念
交并集图
sql join示例
创建表
- 创建表tab_01
CREATE TABLE tab_01(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32)
);
- 创建表tab_02
CREATE TABLE tab_02(
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32),
en_name VARCHAR(32),
age INT(1)
);
初始化数据
- 初始化表tab_01
INSERT INTO tab_01 VALUES (1, "小明");
INSERT INTO tab_01 VALUES (2, "小王");
INSERT INTO tab_01 VALUES (3, "小红");
INSERT INTO tab_01 VALUES (4, "小陈");
- 初始化表tab_02
INSERT INTO tab_02 VALUES (1, "小明","A",9);
INSERT INTO tab_02 VALUES (2, "小王","B",10);
INSERT INTO tab_02 VALUES (3, "小红","C",11);
INSERT INTO tab_02 VALUES (4, "小青","D",10);
INSERT INTO tab_02 VALUES (5, "小元","E",7);
INSERT INTO tab_02 VALUES (6, "小云","F",10);
查询数据
mysql> select * from tab_01;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
| 2 | 小王 |
| 3 | 小红 |
| 4 | 小陈 |
+----+--------+
4 rows in set (0.00 sec)
mysql> select * from tab_02;
+----+--------+---------+------+
| id | name | en_name | age |
+----+--------+---------+------+
| 1 | 小明 | A | 9 |
| 2 | 小王 | B | 10 |
| 3 | 小红 | C | 11 |
| 4 | 小青 | D | 10 |
| 5 | 小元 | E | 7 |
| 6 | 小云 | F | 10 |
+----+--------+---------+------+
6 rows in set (0.00 sec)
cross join
介绍
笛卡尔积，表tab_01的行数乘以表tab_02的行数等于笛卡尔积结果集的大小。
用法
mysql> select * from tab_01 cross join tab_02;
+----+--------+----+--------+---------+------+
| id | name | id | name | en_name | age |
+----+--------+----+--------+---------+------+
| 4 | 小陈 | 1 | 小明 | A | 9 |
| 3 | 小红 | 1 | 小明 | A | 9 |
| 2 | 小王 | 1 | 小明 | A | 9 |
| 1 | 小明 | 1 | 小明 | A | 9 |
| 4 | 小陈 | 2 | 小王 | B | 10 |
| 3 | 小红 | 2 | 小王 | B | 10 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 1 | 小明 | 2 | 小王 | B | 10 |
| 4 | 小陈 | 3 | 小红 | C | 11 |
| 3 | 小红 | 3 | 小红 | C | 11 |
| 2 | 小王 | 3 | 小红 | C | 11 |
| 1 | 小明 | 3 | 小红 | C | 11 |
| 4 | 小陈 | 4 | 小青 | D | 10 |
| 3 | 小红 | 4 | 小青 | D | 10 |
| 2 | 小王 | 4 | 小青 | D | 10 |
| 1 | 小明 | 4 | 小青 | D | 10 |
| 4 | 小陈 | 5 | 小元 | E | 7 |
| 3 | 小红 | 5 | 小元 | E | 7 |
| 2 | 小王 | 5 | 小元 | E | 7 |
| 1 | 小明 | 5 | 小元 | E | 7 |
| 4 | 小陈 | 6 | 小云 | F | 10 |
| 3 | 小红 | 6 | 小云 | F | 10 |
| 2 | 小王 | 6 | 小云 | F | 10 |
| 1 | 小明 | 6 | 小云 | F | 10 |
+----+--------+----+--------+---------+------+
24 rows in set (0.00 sec)
inner join
介绍
查出表tab_01和表tab_02的完全匹配的部分。（交集）
用法
mysql> select * from tab_01 inner join tab_02 on tab_01.name = tab_02.name;;
+----+--------+----+--------+---------+------+
| id | name | id | name | en_name | age |
+----+--------+----+--------+---------+------+
| 1 | 小明 | 1 | 小明 | A | 9 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 3 | 小红 | 3 | 小红 | C | 11 |
+----+--------+----+--------+---------+------+
3 rows in set (0.00 sec)
full join
介绍
全连接，匹配两个表所有数据。（并集）
用法
mysql> select * from tab_01 full outer join tab_02 on tab_01.name = tab_02.name;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join tab_02 on tab_01.name = tab_02.name' at line 1
mysql中使用full join on报错，不支持，需要使用union all替换
mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name
-> union all
-> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name;
+------+--------+------+--------+---------+------+
| id | name | id | name | en_name | age |
+------+--------+------+--------+---------+------+
| 1 | 小明 | 1 | 小明 | A | 9 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 3 | 小红 | 3 | 小红 | C | 11 |
| 4 | 小陈 | NULL | NULL | NULL | NULL |
| 1 | 小明 | 1 | 小明 | A | 9 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 3 | 小红 | 3 | 小红 | C | 11 |
| NULL | NULL | 4 | 小青 | D | 10 |
| NULL | NULL | 5 | 小元 | E | 7 |
| NULL | NULL | 6 | 小云 | F | 10 |
+------+--------+------+--------+---------+------+
10 rows in set (0.00 sec)
left join
介绍
左连接，将表tab_01的所有数据以及表tab_02所匹配到的数据进行连接输出。
用法
- 取出左表A中的所有数据。
mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name;
+----+--------+------+--------+---------+------+
| id | name | id | name | en_name | age |
+----+--------+------+--------+---------+------+
| 1 | 小明 | 1 | 小明 | A | 9 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 3 | 小红 | 3 | 小红 | C | 11 |
| 4 | 小陈 | NULL | NULL | NULL | NULL |
+----+--------+------+--------+---------+------+
4 rows in set (0.00 sec)
- 取出左表A中不包含右表B的数据。
mysql> select * from tab_01 left join tab_02 on tab_01.name = tab_02.name where tab_02.name is null;
+----+--------+------+------+---------+------+
| id | name | id | name | en_name | age |
+----+--------+------+------+---------+------+
| 4 | 小陈 | NULL | NULL | NULL | NULL |
+----+--------+------+------+---------+------+
1 row in set (0.00 sec)
right join
介绍
右连接，将表tab_02的所有数据以及表tab_01中匹配到的连接数据输出。
用法
- 取出所有右表B中的数据。
mysql> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name;
+------+--------+----+--------+---------+------+
| id | name | id | name | en_name | age |
+------+--------+----+--------+---------+------+
| 1 | 小明 | 1 | 小明 | A | 9 |
| 2 | 小王 | 2 | 小王 | B | 10 |
| 3 | 小红 | 3 | 小红 | C | 11 |
| NULL | NULL | 4 | 小青 | D | 10 |
| NULL | NULL | 5 | 小元 | E | 7 |
| NULL | NULL | 6 | 小云 | F | 10 |
+------+--------+----+--------+---------+------+
6 rows in set (0.00 sec)
- 取出右表B中不包含左表A的数据。
mysql> select * from tab_01 right join tab_02 on tab_01.name = tab_02.name where tab_01.name is null;
+------+------+----+--------+---------+------+
| id | name | id | name | en_name | age |
+------+------+----+--------+---------+------+
| NULL | NULL | 4 | 小青 | D | 10 |
| NULL | NULL | 5 | 小元 | E | 7 |
| NULL | NULL | 6 | 小云 | F | 10 |
+------+------+----+--------+---------+------+
3 rows in set (0.00 sec)
转载自:https://juejin.cn/post/7098322233386352677