MySQL表的内外连接

MySQL表的内外连接

表的内外连接是通过 join ... on 关键字，对两个表形成的笛卡儿积进行筛选。内连接和外连接都是从多个表中检索信息，它们的本质差别在于如何显示没有匹配记录的数据。

1. 表的设计

create table students(id int auto_increment primary key,name varchar(10),gender enum('男','女')
);create table scores(id int,chinese tinyint,math tinyint,english tinyint,foreign key (id) references students(id)
);

insert into students (name, gender) values ('张伟', '男');
insert into students (name, gender) values ('王芳', '女');
insert into students (name, gender) values ('李娜', '女');
insert into students (name, gender) values ('刘洋', '男');
insert into students (name, gender) values ('陈杰', '男');
insert into students (name, gender) values ('赵敏', '女');
insert into students (name, gender) values ('孙强', '男');
insert into students (name, gender) values ('周静', '女');
insert into students (name, gender) values ('吴磊', '男');
insert into students (name, gender) values ('郑丽', '女');insert into scores (id, chinese, math, english) values (1, 85, 90, 78);
insert into scores (id, chinese, math, english) values (2, null, 88, 95);
insert into scores (id, chinese, math, english) values (3, 76, null, 80);
insert into scores (id, chinese, math, english) values (4, 88, 76, null);
insert into scores (id, chinese, math, english) values (5, null, null, null);
-- 注意只有前5号有成绩记录
insert into scores (id, chinese, math, english) values (null, 89, 87, 90);
insert into scores (id, chinese, math, english) values (null, 76, 81, 79);

2. 内连接

使用内连接查询两个表中的数据时，没有匹配连接条件的记录会被过滤。在实际开发中使用最多的连接方法就是内连接，在复合查询文档中，虽然没有使用 inner join ... on ，但实际它们也都属于内连接。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

显示学生的信息和成绩

数据库中只有前五个学生有成绩记录，所以内连接只会显示五条记录，没有成绩记录的学生自动过滤。

select students.id,name,gender,chinese,math,english from students inner join scores on students.id = scores.id;

3. 外连接

外连接分为左外连接和右外连接。左外连接允许左表的数据与右表的数据没有匹配，并显示出来，但右表与左表没有匹配则过滤掉，右外连接反之。

3.1 左外连接

使用左外连接显示学生的信息和成绩

数据库中只有前五个学生有成绩记录，后五个学生的成绩记录则全为 NULL 。

select students.id,name,gender,chinese,math,english from students left join scores on students.id = scores.id;

3.2 右外连接

使用左外连接显示学生的信息和成绩

数据库中有两条成绩记录没有匹配的学生 id，故右外连接会显示这两条记录。

select students.id,name,gender,chinese,math,english from students right join scores on students.id = scores.id;