MySQL中关于JOIN的用法全解

一、一张图看懂 MySQL 的各种 JOIN 用法

二、准备表和数据，测试

1、创建两个表测试

CREATE TABLE `forlan_class`(
	`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`class_name` varchar(200) DEFAULT NULL COMMENT '班级名称',
	 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='班级信息表';


CREATE TABLE `forlan_student`(
	`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
	`student_name` varchar(200) DEFAULT NULL COMMENT '学生名称',
	`class_type` bigint(20) NOT NULL DEFAULT -1 COMMENT '班级类型',
	 PRIMARY KEY (`id`) USING BTREE,
	 KEY `idx_class`(`class_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='学生信息表';

2、插入forlan_class数据

±—±-----------+
| id | class_name |
±—±-----------+
| 1 | 初级班 |
| 2 | 中级班 |
| 3 | 高级班 |
| 4 | 大师班 |
| 5 | 成神班 |
| 6 | 神仙班 |
±—±-----------+

3、插入forlan_student数据

±—±-------------±-----------+
| id | student_name | class_type |
±—±-------------±-----------+
| 1 | 小伟 | 1 |
| 2 | 大伟 | 5 |
| 3 | 小明 | 2 |
| 4 | 小红 | 3 |
| 5 | 小白 | 4 |
| 6 | 小黑 | 4 |
| 7 | 小燕 | 2 |
| 8 | 黑化 | 100 |
±—±-------------±-----------+

三、常用类型

1、内连接

四种方式：INNER JOIN、JOIN、WHERE、STRAIGHT_JOIN

SELECT * FROM forlan_student A INNER JOIN forlan_class B ON A.class_type=B.id;
SELECT * FROM forlan_student A JOIN forlan_class B ON A.class_type=B.id;
SELECT * FROM forlan_student A,forlan_class B WHERE A.class_type=B.id;
SELECT * FROM forlan_student A STRAIGHT_JOIN forlan_class B ON A.class_type=B.id;

±—±-------------±-----------±—±-----------+
| id | student_name | class_type | id | class_name |
±—±-------------±-----------±—±-----------+
| 1 | 小伟 | 1 | 1 | 初级班 |
| 3 | 小明 | 2 | 2 | 中级班 |
| 7 | 小燕 | 2 | 2 | 中级班 |
| 4 | 小红 | 3 | 3 | 高级班 |
| 5 | 小白 | 4 | 4 | 大师班 |
| 6 | 小黑 | 4 | 4 | 大师班 |
| 2 | 大伟 | 5 | 5 | 成神班 |
±—±-------------±-----------±—±-----------+
7 rows in set (0.08 sec)

2、左连接：Left JOIN

SELECT * FROM forlan_student A LEFT JOIN forlan_class B ON A.class_type=B.id;

±—±-------------±-----------±-----±-----------+
| id | student_name | class_type | id | class_name |
±—±-------------±-----------±-----±-----------+
| 1 | 小伟 | 1 | 1 | 初级班 |
| 3 | 小明 | 2 | 2 | 中级班 |
| 7 | 小燕 | 2 | 2 | 中级班 |
| 4 | 小红 | 3 | 3 | 高级班 |
| 5 | 小白 | 4 | 4 | 大师班 |
| 6 | 小黑 | 4 | 4 | 大师班 |
| 2 | 大伟 | 5 | 5 | 成神班 |
| 8 | 黑化 | 100 | NULL | NULL |
±—±-------------±-----------±-----±-----------+
8 rows in set (0.04 sec)

3、右连接：Right JOIN

SELECT * FROM forlan_student A RIGHT JOIN forlan_class B ON A.class_type=B.id;

±-----±-------------±-----------±—±-----------+
| id | student_name | class_type | id | class_name |
±-----±-------------±-----------±—±-----------+
| 1 | 小伟 | 1 | 1 | 初级班 |
| 3 | 小明 | 2 | 2 | 中级班 |
| 7 | 小燕 | 2 | 2 | 中级班 |
| 4 | 小红 | 3 | 3 | 高级班 |
| 5 | 小白 | 4 | 4 | 大师班 |
| 6 | 小黑 | 4 | 4 | 大师班 |
| 2 | 大伟 | 5 | 5 | 成神班 |
| NULL | NULL | NULL | 6 | 神仙班 |
±-----±-------------±-----------±—±-----------+
8 rows in set (0.04 sec)

4、外连接

Mysql不支持Outer JOIN，有些地方叫Full JOIN

SELECT * FROM forlan_student A Full JOIN forlan_class B ON A.class_type=B.id WHERE A.Key IS NULL OR B.Key IS NULL;

采用（A LEFT JOIN B）UNION（A RIGHT JOIN B）
如果是3张以上表，以此类推
（A LEFT JOIN B LEFT JOIN C）UNION（A LEFT JOIN B RIGHT JOIN C）UNION（A RIGHT JOIN B RIGHT JOIN C）
说明：没有all关键字，mysql会在查询的时候给临时表加上distinct的关键字

SELECT * FROM forlan_student A LEFT JOIN forlan_class B ON A.class_type=B.id
UNION
SELECT * FROM forlan_student A RIGHT JOIN forlan_class B ON A.class_type=B.id;

±-----±-------------±-----------±-----±-----------+
| id | student_name | class_type | id | class_name |
±-----±-------------±-----------±-----±-----------+
| 1 | 小伟 | 1 | 1 | 初级班 |
| 3 | 小明 | 2 | 2 | 中级班 |
| 7 | 小燕 | 2 | 2 | 中级班 |
| 4 | 小红 | 3 | 3 | 高级班 |
| 5 | 小白 | 4 | 4 | 大师班 |
| 6 | 小黑 | 4 | 4 | 大师班 |
| 2 | 大伟 | 5 | 5 | 成神班 |
| 8 | 黑化 | 100 | NULL | NULL |
| NULL | NULL | NULL | 6 | 神仙班 |
±-----±-------------±-----------±-----±-----------+
9 rows in set (0.13 sec)

5、左连接-内连接

SELECT * FROM forlan_student A LEFT JOIN forlan_class B ON A.class_type=B.id WHERE B.id IS NULL;

±—±-------------±-----------±-----±-----------+
| id | student_name | class_type | id | class_name |
±—±-------------±-----------±-----±-----------+
| 8 | 黑化 | 100 | NULL | NULL |
±—±-------------±-----------±-----±-----------+
1 row in set (0.07 sec)

6、右连接-内连接

SELECT * FROM forlan_student A RIGHT JOIN forlan_class B ON A.class_type=B.id WHERE A.id IS NULL;

±-----±-------------±-----------±—±-----------+
| id | student_name | class_type | id | class_name |
±-----±-------------±-----------±—±-----------+
| NULL | NULL | NULL | 6 | 神仙班 |
±-----±-------------±-----------±—±-----------+
1 row in set (0.06 sec)

7、外连接-内连接

SELECT * FROM forlan_student A LEFT JOIN forlan_class B ON A.class_type=B.id WHERE B.id IS NULL 
UNION
SELECT * FROM forlan_student A RIGHT JOIN forlan_class B ON A.class_type=B.id WHERE A.id IS NULL;

±-----±-------------±-----------±-----±-----------+
| id | student_name | class_type | id | class_name |
±-----±-------------±-----------±-----±-----------+
| 8 | 黑化 | 100 | NULL | NULL |
| NULL | NULL | NULL | 6 | 神仙班 |
±-----±-------------±-----------±-----±-----------+
2 rows in set (0.11 sec)

四、拓展

1、迪卡尔积：CROSS JOIN

将A，B表的每一条记录拼在一起，如果A表有8条记录，B表有6条记录，笛卡尔积产生的结果就有8*6条记录

SELECT * FROM forlan_student CROSS JOIN forlan_class;
SELECT * FROM forlan_student,forlan_class;
SELECT * FROM forlan_student INNER JOIN forlan_class;
SELECT * FROM forlan_student NATURE JOIN forlan_class;
SELECT * FROM forlan_student NATURA JOIN forlan_class;

±—±-------------±-----------±—±-----------+
| id | student_name | class_type | id | class_name |
±—±-------------±-----------±—±-----------+
| 1 | 小伟 | 1 | 1 | 初级班 |
| 1 | 小伟 | 1 | 2 | 中级班 |
| 1 | 小伟 | 1 | 3 | 高级班 |
| 1 | 小伟 | 1 | 4 | 大师班 |
| 1 | 小伟 | 1 | 5 | 成神班 |
| 1 | 小伟 | 1 | 6 | 神仙班 |
| 2 | 大伟 | 5 | 1 | 初级班 |
| 2 | 大伟 | 5 | 2 | 中级班 |
| 2 | 大伟 | 5 | 3 | 高级班 |
| 2 | 大伟 | 5 | 4 | 大师班 |
| 2 | 大伟 | 5 | 5 | 成神班 |
| 2 | 大伟 | 5 | 6 | 神仙班 |
| 3 | 小明 | 2 | 1 | 初级班 |
| 3 | 小明 | 2 | 2 | 中级班 |
| 3 | 小明 | 2 | 3 | 高级班 |
| 3 | 小明 | 2 | 4 | 大师班 |
| 3 | 小明 | 2 | 5 | 成神班 |
| 3 | 小明 | 2 | 6 | 神仙班 |
| 4 | 小红 | 3 | 1 | 初级班 |
| 4 | 小红 | 3 | 2 | 中级班 |
| 4 | 小红 | 3 | 3 | 高级班 |
| 4 | 小红 | 3 | 4 | 大师班 |
| 4 | 小红 | 3 | 5 | 成神班 |
| 4 | 小红 | 3 | 6 | 神仙班 |
| 5 | 小白 | 4 | 1 | 初级班 |
| 5 | 小白 | 4 | 2 | 中级班 |
| 5 | 小白 | 4 | 3 | 高级班 |
| 5 | 小白 | 4 | 4 | 大师班 |
| 5 | 小白 | 4 | 5 | 成神班 |
| 5 | 小白 | 4 | 6 | 神仙班 |
| 6 | 小黑 | 4 | 1 | 初级班 |
| 6 | 小黑 | 4 | 2 | 中级班 |
| 6 | 小黑 | 4 | 3 | 高级班 |
| 6 | 小黑 | 4 | 4 | 大师班 |
| 6 | 小黑 | 4 | 5 | 成神班 |
| 6 | 小黑 | 4 | 6 | 神仙班 |
| 7 | 小燕 | 2 | 1 | 初级班 |
| 7 | 小燕 | 2 | 2 | 中级班 |
| 7 | 小燕 | 2 | 3 | 高级班 |
| 7 | 小燕 | 2 | 4 | 大师班 |
| 7 | 小燕 | 2 | 5 | 成神班 |
| 7 | 小燕 | 2 | 6 | 神仙班 |
| 8 | 黑化 | 100 | 1 | 初级班 |
| 8 | 黑化 | 100 | 2 | 中级班 |
| 8 | 黑化 | 100 | 3 | 高级班 |
| 8 | 黑化 | 100 | 4 | 大师班 |
| 8 | 黑化 | 100 | 5 | 成神班 |
| 8 | 黑化 | 100 | 6 | 神仙班 |
±—±-------------±-----------±—±-----------+
48 rows in set (0.08 sec)

2、自然链接：NATURAL JOIN

自然连接就是USING子句的简化版，找出两个表中相同的列作为连接条件进行连接

SELECT * FROM forlan_student NATURAL JOIN forlan_class;

±—±-------------±-----------±-----------+
| id | student_name | class_type | class_name |
±—±-------------±-----------±-----------+
| 1 | 小伟 | 1 | 初级班 |
| 2 | 大伟 | 5 | 中级班 |
| 3 | 小明 | 2 | 高级班 |
| 4 | 小红 | 3 | 大师班 |
| 5 | 小白 | 4 | 成神班 |
| 6 | 小黑 | 4 | 神仙班 |
±—±-------------±-----------±-----------+
6 rows in set (0.10 sec)