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

mysql left join on后面怎么用in mysql join on用法_mysql

二、准备表和数据，测试

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、内连接

mysql left join on后面怎么用in mysql join on用法_mysql_02


四种方式：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

mysql left join on后面怎么用in mysql join on用法_主键_03

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

mysql left join on后面怎么用in mysql join on用法_mysql_04

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 left join on后面怎么用in mysql join on用法_数据库_05


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、左连接-内连接

mysql left join on后面怎么用in mysql join on用法_数据库_06

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、右连接-内连接

mysql left join on后面怎么用in mysql join on用法_主键_07

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、外连接-内连接

mysql left join on后面怎么用in mysql join on用法_sql_08

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)