各种连接（等值连接、内连接、外连接、左连接、右连接、全连接）

演示数据

学生表和学生数据

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `snum` int(11) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `sage` tinyint(4) DEFAULT NULL,
  `sclass` smallint(6) NOT NULL,
  PRIMARY KEY (`snum`),
  UNIQUE KEY `student_num` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sql`.`student`
(`snum`,
`sname`,
`sage`,
`sclass`)
VALUES
(20201101,'张三',20,150),
(20201102,'李四',18,151),
(20201103,'王五',19,151),
(20201104,'赵六',18,150),
(20201105,'钱七',21,151),
(20201106,'孙八',20,152);

课程表和课程数据

create table course(
cid int not null auto_increment,
cnum int not null primary key,
cname varchar(20) not null,
unique key course_num (cid)
) engine = innodb auto_increment = 1 default charset = utf8;

INSERT INTO `sql`.`course`
(`cnum`,
`cname`)
VALUES
(101, '数据结构'),
(102, '编译原理'),
(103, '计算机网络'),
(105, '计算机组成原理'),
(106, '数据库');

学生课程关系表

CREATE TABLE `sc` (
  `scid` int(11) NOT NULL AUTO_INCREMENT,
  `snum` int(11) NOT NULL,
  `cnum` int(11) NOT NULL,
  `grade` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`snum`,`cnum`),
  UNIQUE KEY `scid` (`scid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sql`.`sc`
(`snum`,
`cnum`,
`grade`)
VALUES
(20201101, 101, 85),
(20201102, 101, 55),
(20201103, 101, 90),
(20201101, 102, 88),
(20201102, 102, 75),
(20201103, 102, 58),
(20201101, 103, 72),
(20201102, 103, 80),
(20201103, 103, 92);

等值连接

等值连接在进行多表联合查询时通过“=”（等号）来连接多张表之间字段对应的值，其产生的结果会出现重复列。

可以理解为多张表做笛卡尔积之后根据 where 条件过滤出结果集。

和内连接效果是一样的。

select *
from course,sc 
where course.cnum = sc.cnum;

select *
from course inner join sc 
on course.cnum = sc.cnum;

上面两个SQL会得到相同的结果：

生成缩略图出错：无法将缩略图保存到目标地点

自连接

自连接是一种特殊的连接，它的本质是将一个表与它自身做连接，若要在一个表中查找具有相同列值的行，则可以使用自连接。

使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

select *
from student stu1, student stu2
where stu1.snum = stu2.snum

生成缩略图出错：无法将缩略图保存到目标地点

其实，自连接的场景一般如：

菜单表

create table menu(
menu_id int not null auto_increment,
menu_num int not null primary key,
menu_name varchar(20) not null,
p_num int null,
unique key menu_id (menu_id)
) engine = innodb auto_increment = 1 default charset = utf8;

insert into menu(menu_num, menu_name, p_num)
values (0, '一级菜单',  null), 
(1, '二级菜单', 0), 
(2, '三级菜单', 1);

menu_num 是菜单编号，p_num 是父级菜单编码

那么现在要查询所有菜单的子菜单，就用到了自连接：

select *
from menu l1, menu l2
where l1.menu_num = l2.p_num

自然连接

自然连接是一种特殊的等值连接，在进行多表联合查询时通过“natural join”关键字来连接多张表之间相同字段对应的值，其产生的结果会将重复列只保留一个。

如果对多张表进行自然连接操作，那么前提要求是这多张表之间必须有相同的字段名。

select *
from course natural join sc;

生成缩略图出错：无法将缩略图保存到目标地点

如果自然连接两个不存在相同字段名的表：

select *
from course natural join student;

得到的就是单纯的笛卡尔积：

生成缩略图出错：无法将缩略图保存到目标地点

同名字段的值在其中一张表中不存在对应值的记录不会出现，比如：

原本在表 course 表中 并不存在一条 cnum 是 104 的记录，现在插入一条：

insert into course (cnum, cname)
value (104, 'C语言');

然后再执行自然连接的查询，发现查询结果中并没有 cnum = 104 的记录，向 student 表中插入 course 表 cnum 字段中不存在的值的记录也是一样。

外连接

外连接（outer join）分为三种：

• 左外连接/左连接（left outer join 或 left join）
• 右外连接/右连接（right outer join 或 right join）
• 全外连接/全连接（full outer join 或 full join）

左外连接/左连接

左外连接在进行多表联合查询时通过“left join …… on”关键字来连接多张表之间符合条件的字段，其查询的结果会返回左表的所有数据，以及左右表交集的数据，若右表记录不足，则使用 NULL 来表示需要筛选的右表的值。

/*下面两种写法效果是一样的*/
select *
from course left join sc
on course.cnum = sc.cnum;

select *
from course left outer join sc
on course.cnum = sc.cnum;

生成缩略图出错：无法将缩略图保存到目标地点

sc表中没有cnum是104、105、106的记录，所以查询结果中对应的scid、snum、cnum、grade 这些字段均为NULL。

如果只查询左表有且右表没有的数据，可以再加一个 where 条件：

select *
from course left join sc
on course.cnum = sc.cnum
where sc.cnum is null;

生成缩略图出错：无法将缩略图保存到目标地点

右外连接/右连接

右外连接在进行多表联合查询时通过“right join……on”关键字来连接多张表之间符合条件的字段，其查询的结果会返回右表的所有数据，以及左右表交集的数据，若左表记录不足，则使用 NULL 来表示需要筛选的左表的值。

select *
from sc right join course
on sc.cnum = course.cnum

如果要只查询右表有且左表没有的数据，可以再加一个 where 条件：

select *
from sc right join course
on sc.cnum = course.cnum
where sc.cnum is null;

生成缩略图出错：无法将缩略图保存到目标地点

全外连接/全连接（去除重复行）

全连接在进行多表联合查询时通过“full join……on”关键字来连接多张表之间符合条件的字段，其查询的结果除了会返回左右表匹配的数据，还会返回两个表的所有行。

但是 MySQL 中没有全连接，所以需要使用“union”连接左右外连接来实现全连接的功能：

select *
from course left join sc
on course.cnum = sc.cnum
union
select *
from course right join sc
on course.cnum = sc.cnum

生成缩略图出错：无法将缩略图保存到目标地点

union all（存在重复行）

select *
from course left join sc
on course.cnum = sc.cnum
union all
select *
from course right join sc
on course.cnum = sc.cnum

与 union 不同的是，union 会去除重复行，union all 会保留重复的行

内连接

内连接在进行多表联合查询时通过“inner join……on”关键字来连接多张表之间符合条件的字段，其查询结果是左右表都匹配的数据。

和等值连接效果是一样的。

select *
from course inner join sc
where course.cnum = sc.cnum;

生成缩略图出错：无法将缩略图保存到目标地点