案例理解LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法

本文将着重分析下图中联合查询的用法：

图片来源： 一张图看懂 SQL 的各种 JOIN 用法 。

建立相关表

在理解之前，我们首先建立相关的表，首先建立学生表：

create table student(
	stuNo	char(5) primary key, -- 学生学号
	stuName varchar(32) not null, -- 学生姓名
	gender char(1) not null	default '男', -- 学生性别
	age int not null -- 学生年龄
)charset='utf8';

之后插入数据：

-- 添加学生数据
insert into student(stuNo,stuName,gender,age) values('001','李志','男',14);
insert into student(stuNo,stuName,gender,age) values('002','宋东野','男',23);
insert into student(stuNo,stuName,gender,age) values('003','赵雷','男',34);
insert into student(stuNo,stuName,gender,age) values('004','马頔','男',32);
insert into student(stuNo,stuName,gender,age) values('005','陈粒','女',18);
insert into student(stuNo,stuName,gender,age) values('006','筠子','女',4);
insert into student(stuNo,stuName,gender,age) values('007','寸铁','男',56);
insert into student(stuNo,stuName,gender,age) values('008','狗毛','男',47);
insert into student(stuNo,stuName,gender,age) values('009','崔健','男',39);
insert into student(stuNo,stuName,gender,age) values('010','草东','男',40);
insert into student(stuNo,stuName,gender,age) values('011','张悬','女',36);
insert into student(stuNo,stuName,gender,age) values('012','撒娇','女',25);
insert into student(stuNo,stuName,gender,age) values('013','宋东野','男',35);

再建立分数表，此处的分数表不严谨，因为并没有相关课程的信息，并且应该和学生表有一个外键关联，但为了简单的理解联合查询以及需要插入一个特殊的数据，此处就不弄那么复杂了：

create table score(
	id int not null AUTO_INCREMENT primary key,
	sNo char(5) not null , -- 教师号
	score double not null -- 成绩
	-- foreign key(sNo) references student(stuNo)
);

之后插入数据：

-- 添加成绩数据
insert into score(sNo,score) values('001',56);
insert into score(sNo,score) values('002',87);
insert into score(sNo,score) values('004',92);
insert into score(sNo,score) values('005',76);
insert into score(sNo,score) values('007',90);
insert into score(sNo,score) values('008',53);
insert into score(sNo,score) values('010',67);
insert into score(sNo,score) values('011',69);
insert into score(sNo,score) values('012',78);
insert into score(sNo,score) values('014',80); -- 此数据如果有外键关联则无法插入

此时就得到了学生表和分数表，删除重建命令如下：

-- 删除表
drop table if exists score;
drop table if exists student;

七大JOIN

现在我们假设学生表为A，分数表为B。

两张表的详细信息如下：

Inner JOIN

查询学生中参加考试的学生的分数

select s.stuNo, s.stuName, sc.score FROM student as s inner join score as sc on s.stuNo=sc.sNo ;

可得结果：

Left JOIN

查询所有学生的分数（包含缺考的）

select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;

可得结果：

Left Excluding JOIN

查询缺考的学生

select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL order by s.stuNo ASC;

可得结果如下：

Right JOIN

查询所有分数对应的学生

select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo order by s.stuNo ASC;

可得结果如下：

Right Excluding JOIN

查询有分数但不在学生表的分数

select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL order by s.stuNo ASC;

可得结果如下：

FULL Outer JOIN

我们需要注意， Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现。 UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

查询所有的学生和所有的分数

select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo
UNION
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo;

可得结果如下：

FULL Outer Excluding JOIN

查询分数为空的学生和学生为空的分数

select s.stuNo, s.stuName, sc.score FROM student as s left join score as sc on s.stuNo=sc.sNo where sc.score is NULL 
UNION
select s.stuNo, s.stuName, sc.score FROM student as s right join score as sc on s.stuNo=sc.sNo where s.stuNo is NULL ;

可得结果如下：