mysql多表连接查询优化 mysql多表连接查询语句

关键词：左外连接    右外连接    内连接

 

---

多表连接查询，多张表进行数据的联合查询。分类有内连接、外连接、自连接。

---

█ 相关概念

• 内连接

语法: 
SELECT 列名... FROM 表1
INNER JOIN 表2
ON 表1.列 = 表2.列
[WHERE/ORDER BY语句]

根据ON后面的条件语句，筛选出两张表中都符合条件、存在的数据。A INNER JOIN B，其中A表在语法词的左边称为左表，B表在语法词的右边称为右表。

如图A、B两张表进行内连接查询。语句：A INNER JOIN B ON A.ID = B.UID，即找出A表和B表中的数据，需要满足A表的ID等于B表的UID。

• 外连接

筛选获取只在一方表中的数据。外连接分为左外连接（左连接）和右外连接（右连接）

①左外连接

语法: 
SELECT 列名... FROM 表1
LEFT OUTER JOIN 表2
ON 表1.列 = 表2.列
[WHERE/ORDER BY语句]
/*其中的OUTER可以省略，写成 LEFT JOIN*/

结果是获取到左边表的所有数据，以及根据ON后面的条件，筛选出符合条件的右边表的数据。（其中左边表和右边表是相对LEFT OUTER JOIN的位置来说的，在其左边就是左边表，在其右边就是右边表）。

如图A、B两张表进行左外连接查询，A为左表，B为右表。语句：A LEFT OUTER JOIN B ON A.ID = B.UID，即找出A表的所有数据和满足条件的B表数据。

②右外连接

语法: 
SELECT 列名... FROM 表1
RIGHT OUTER JOIN 表2
ON 表1.列 = 表2.列
[WHERE/ORDER BY语句]
/*其中的OUTER可以省略，写成 RIGHT JOIN*/

左外连接会获得左边表的所有数据，那右外连接就会获得右边表的所有数据，以及根据ON后面的条件，筛选出符合条件的左边表的数据。

如图A、B两张表进行右外连接查询，A为左表，B为右表。语句：A RIGHT OUTER JOIN B ON A.ID = B.UID，即找出B表的所有数据和满足条件的A表数据。

• 自连接

自连接是对同一张表进行连接，自己与自己进行连接查询。可以进行内连接和外连接，只不过，左表和右边都是同一张表。可以在使用中添加别名来区分成两张表使用。

（关于左外连接和右外连接，A 左外连接 B就相当于 B 右外连接 A。）

---

 █ 使用

①创建两张表，课程表和学生表

/*课程表*/
create table class(
    id int primary key auto_increment,
    name varchar(10)
);

/*学生表*/
create table student(
    id int primary key auto_increment,
    name varchar(10),
    class_id int
);

②分别往两张表中插入数据

insert into class(name) values('语文');
insert into class(name) values('数学');
insert into class(name) values('英语');
insert into class(name) values('体育');

insert into student(name, class_id) values('张三', 1);
insert into student(name, class_id) values('李四', 2);
insert into student(name, class_id) values('王五', 3);
insert into student(name) values('赵六');

完成上述（1）（2）操作后，两张表的数据分别为：

class表

student表

从两张表的数据可以看出，体育课是没有任何学生选的，学生赵六是没有选择任何一门课的。

③内连接查询

查询出学生和课程之间的对应关系

select stu.name as 学生名, cl.name as 课程名 
from student stu
inner join class cl on stu.class_id = cl.id;

查询结果：

结果中并没有出现赵六和体育课的记录，因为他们俩都不满足on后面的条件。

④左外连接查询

查询所有学生和他对应的课程

select stu.name as 学生名, cl.name as 课程名 
from student stu
left join class cl on stu.class_id = cl.id;

查询结果：

结果中出现了赵六的记录，并且他对应的课程名是空的。

⑤右外连接查询

查询所有课程及其对应的学生

select stu.name as 学生名, cl.name as 课程名 
from student stu
right join class cl on stu.class_id = cl.id;

查询结果：

结果中出现了体育，并且其对应的学生为空。