【Mysql】图解左、右、内与全连接

一、前言

使用学生表与成绩表来演示Mysql中的各种连接查找

学生表的建表语句如下：

CREATE TABLE student(
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增序号',
  st_id int(11) DEFAULT NULL COMMENT '学生id',
  st_name varchar(255) DEFAULT NULL COMMENT '学生姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;

成绩表的建表语句如下：

CREATE TABLE score(
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  st_id int(11) DEFAULT NULL COMMENT '学生id',
  subject varchar(255) DEFAULT NULL COMMENT '学科名称',
  grade int(11) DEFAULT NULL COMMENT '学科成绩',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

初始数据如下：

二、内连接

按照关联字段取出两个表中的记录，保留的是两个表的交集。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	INNER JOIN score ON student.st_id = score.st_id;

执行结果：

对于关联字段st_id，左表与右表都有1001与1002。

三、左连接

按照关联字段取出两个表中的记录，保留左表所有的记录，以及满足连接条件的右表记录，右表中不满足连接条件的会被置为null。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	LEFT JOIN score ON student.st_id = score.st_id;

执行结果：

对于关联字段st_id，展示左表所有的记录。由于右表缺少1003，则1003这行的subject与grade的值被置为null。

四、右连接

按照关联字段取出两个表中的记录，保留右表所有的记录，以及满足连接条件的左表记录，左表中不满足连接条件的会被置为null。正好与左连接相反。

例如：

SELECT
	student.st_id,
	student.st_name,
	score.subject,
	score.grade 
FROM
	student
	RIGHT JOIN score ON student.st_id = score.st_id;

执行结果：

对于关联字段st_id，展示右表所有的记录。由于左表缺少1005，即执行结果的最后一行的st_id与st_name的值被置为null。

五、全连接

按照关联字段取出两个表中的记录，保留左右两表中所有的记录，不满足连接条件的均被置为null。

当然，oracle可以直接使用full join来完成全连接，而mysql则需要借助union。

例如：

select student.st_id,student.st_name,score.subject,score.grade 
from student left join score on student.st_id=score.st_id
union
select student.st_id,student.st_name,score.subject,score.grade 
from student right join score on student.st_id=score.st_id;

执行结果：

可以看到，已经取出了两个表中的所有数据。

六、获取交集以外的部分

按照关联字段取出两个表中的记录，保留交集以外的数据。去除交集相当于全连接-内连接。

例如：

select student.st_id,student.st_name,score.subject,score.grade 
from student left join score on student.st_id=score.st_id 
where score.st_id is null
union
select student.st_id,student.st_name,score.subject,score.grade 
from student right join score on student.st_id=score.st_id 
where student.st_id is null;

执行结果：

第一条数据属于student表，第二条数据属于score表。