【JavaWeb后端学习笔记】MySQL多表查询（内连接、外连接、子查询）

多表查询有两大类：连接查询和子查询。
连接查询又分为隐式/显式内连接和左/右外连接。
子查询又分为标量子查询、列子查询、行子查询和表子查询。
多表查询是建立在单表查询的基础之上的，因此需要熟练单表查询。可以参考：【JavaWeb后端学习笔记】MySQL的数据查询语言（Data Query Language，DQL）
本文案例所需资料来自黑马程序员：多表查询
开始前需要运行01.数据准备.sql脚本中的代码，准备表和数据。

1、连接查询

1.1 内连接

内连接相当于查询A、B两个表交集部分数据。
内连接有两种：隐式内连接、显示内连接。他们的语法如下：

-- 隐式内连接语法
select 字段列表 from 表1,表2 where 连接条件...;-- 显示内连接语法
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

由于现在是多表查询的情况，因此在返回字段列表中，需要使用表名.字段名的格式。
连接条件是用来消除多余的笛卡尔积。一般会使用逻辑外键消除笛卡尔积。

下面分别使用隐式内连接和现实内连接的方式查询员工的姓名以及所属部门名称。

-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
-- tb_emp表中的dept_id字段是关联到tb_dept表的逻辑外键，可以通过逻辑外键消除多余的笛卡尔积。
select tb_emp.name as name, tb_dept.name as department from tb_emp, tb_dept where tb_emp.dept_id = tb_dept.id;-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
-- 显式内连接只是在代码编写上将内连接展现出来，效果与隐式内连接相同
-- 显示内连接的连接条件写在 on 后面。
select tb_emp.name as name, tb_dept.name as department from tb_emp inner join tb_dept on tb_emp.dept_id = tb_dept.id;

这里可以注意一点，员工表中有17条数据，但是查出来的结果只有16条，缺少了员工姓名为“陈友谅”的数据，这是因为“陈友谅”的dept_id为null，他没有对应的部门，在部门表中没有id为null的数据，因此在内连接查询中，连接条件是不成立的，所以查询不到“陈友谅”的数据。但是为了保证员工数据的完整性，虽然“陈友谅”没有工作，也任然需要把他查询出来，这个时候就需要使用外连接查询。

1.2 外连接

外连接分为：左外连接和右外连接。
左外连接：查询左表所有数据（包括两张表的交集）；
右外连接：查询右表所有数据（包括两张表的交集）；
外连接语法如下：

-- 左外连接语法
select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;-- 右外连接语法
select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

外连接的连接条件也是写在 “on” 后面。
下面分别使用左外连接和右外连接查询员工表所有员工的姓名, 和对应的部门名称：

-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
-- 使用左外连接，为了查询出所有员工数据，将员工表tb_emp放在左边
select tb_emp.name as 员工姓名, tb_dept.name as 部门名称 from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id;-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
-- 使用右外连接，为了查询出所有员工数据，将员工表tb_emp放在右边
select tb_emp.name as 员工姓名, tb_dept.name as 部门名称 from tb_dept right join tb_emp on tb_emp.dept_id = tb_dept.id;

从查询结果可以发现，虽然“陈友谅”的dept_id为null，但是该员工任然查询出来了。这是因为外连接保证了查询出某一边表格的所有数据。

2、子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
子查询语法：

-- 子查询语法(形式不固定)
select * from t1 where column1 = (select column2 from t2 ...);select * from (select * from t1 where 条件查询条件) t left join t2 on 连接条件;......

子查询外部的语句可以是insert/update/delete/select的任何一个，最常见的是select

子查询	返回结果及常用操作符
标量子查询	返回的结果为单个值；常用操作符：= 、<>、>、>=、<、<=
列子查询	返回的结果为一列；常用操作符：in、not in等
行子查询	返回的结果为一行；常用操作符：=、<>、in、not in
表子查询	返回的结果为多行多列；常用操作符：in

2.1 标量子查询

标量子查询返回的结果为单个值。
可以与返回结果比较大小，因此常用操作符有：= 、<>、>、>=、<、<=。

案例1：查询 “教研部” 的所有员工信息。
分析：首先需要查询出教研部的id，然后使用员工表中的dept_id与教研部id判断是否相等。

-- 查询 "教研部" 的所有员工信息
-- (select id from tb_dept where name = '教研部') 标量子查询查询出教研部id
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');

案例2：查询在 “方东白” 入职之后的员工信息。
分析：首先查询出姓名为 "方东白"的员工的入职时间，然后与该入职时间比较。

-- 查询在 "方东白" 入职之后的员工信息
-- (select entrydate from tb_emp where name = '方东白') 标量子查询查询出"方东白" 入职时间
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

2.2 列子查询

列子查询返回的结果为一列。
列子查询返回结果相当于一个列表或者集合，可以判断某个字段值是否在这个列表之中，因此常用操作符有：in、not in等。

案例：查询 “教研部” 和 “咨询部” 的所有员工信息。
分析：需要查询出"教研部" 和 "咨询部"的id，组成一个集合。通过判断员工所属的dept_id是否在这个集合内来查询结果。

-- 查询 "教研部" 和 "咨询部" 的所有员工信息。
-- (select id from tb_dept where tb_dept.name in ('教研部', '咨询部') 列子查询返回'教研部' 和 '咨询部'的id集合
select * from tb_emp where dept_id in (select id from tb_dept where tb_dept.name in ('教研部', '咨询部'));

2.3 行子查询

行子查询返回的结果为一行。
行子查询返回结果为表中一行完整数据或部分数据，可以与这一行数据的任意字段进行比较，常用操作符有：=、<>、in、not in。

案例：查询与 “韦一笑” 的入职日期 及 职位都相同的员工信息 。
分析：首先需要查询出 “韦一笑” 的入职日期 及 职位。然后根据这些信息进行判断。由于要对两个字段同时进行判断，因此可以用 ( ) 将这两个字段括起来同时判断。

-- 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;-- 首先尝试采用标量子查询，分别查询出"韦一笑"的入职日期 及 职位，但是这样就需要两次子查询，性能较低。
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑') and name != '韦一笑';-- 采用行子查询，查询出的结果为一行，可以将需要比较的字段用 ( ) 括起来进行比较
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑') and name != '韦一笑';

2.4 表子查询

表子查询返回的结果为多行多列。
常用操作符为：in。

案例：查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门名称
分析：有两种思路。第一种，先建立员工表与部门表的外连接，通过逻辑外键消除多余的笛卡尔积，然后判断入职时间。第二种，先通过select返回入职时间在 “2006-01-01” 之后的员工子表，然后建立外连接，通过逻辑外键消除多余的笛卡尔积。推荐使用外连接，保证员工信息完整。

--查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称-- 先建立外连接，后判断入职时间
select tb_emp.*, tb_dept.name as 部门名称 from tb_emp left join tb_dept on tb_emp.dept_id = tb_dept.id where tb_emp.entrydate > '2006-01-01';-- 表子查询。先查询出入职时间在 "2006-01-01" 之后员工信息子表，然后建立外连接。
select e.*, d.name 部门名称 from (select * from tb_emp where entrydate > '2006-01-01') e left join tb_dept d on e.dept_id = d.id;

3、多表查询案例

准备数据：执行01.多表查询数据准备.sql脚本。
从案例-多表查询.vsdx中可以看到四张表的详细信息。

-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 子查询
select d.name 名称, d.price 价格, c.name 分类 
from (select * from dish where price < 10) d left join category c on d.category_id = c.id;
-- 内连接
select d.name 菜品名称, d.price 价格, c.name 菜品分类 
from dish d, category c 
where d.category_id = c.id and d.price < 10;-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
-- 子查询
select d.name 名称, d.price 价格, c.name 分类 
from (select * from dish where price between 10 and 50 and status = 1) d left join category c on d.category_id = c.id;
-- 左外连接
select d.name 菜品名称, d.price 价格, c.name 菜品分类 
from dish d left join category c on d.category_id = c.id 
where d.price between 10 and 50 and d.status = 1;-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select c.name 分类名称, max(d.price) 最大价格 
from dish d, category c 
where d.category_id = c.id 
group by c.name;-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
select c.name 分类名称 
from dish d, category c 
where d.category_id = c.id and d.status = 1 
group by c.name 
having count(*) >= 3;-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
select s.name 套餐名称,s.price 套餐价格,d.name 菜品名称,d.price 菜品价格,sd.copies 菜品份数
from dish d,setmeal_dish sd,setmeal s
where d.id = sd.dish_idand sd.setmeal_id = s.idand s.name = '商务套餐A';-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select name, price 
from dish 
where price < (select avg(price) from dish);