MySQL 联合查询

联合查询是工作中用的最多的查询,而且面试的时候也非常爱考,因为SQL没啥考的难点,联合查询在SQL中稍微复杂。

一、联合查询的简单理解

联合查询是联合多个表进行查询，设计数据是把表进行拆分，为了消除表中的字段的依赖关系，比如部分函数依赖，传递依赖，这时会导致一条SQL查出来的数据，对于业务来说是不完整的，我们就可以使用联合查询把关系中的数据全部查出来，在一个数据行中显示详细信息。

这个结果集才是我们想要的

笛卡尔积现象：当两张表进行连接查询时，没有任何条件进行限制，最终查询结果条数是：两张表记录的乘积。

怎么避免笛卡尔积现象？添加连接条件，过滤。

联合查询时MYSQL是如何执行的？

1.取多张表的笛卡尔积

对多张表进行笛卡尔积的过程：

1. 先从第一张表中取一条记录，然后再与第二张表的第一条记录进行组合，生成一条新的记录
2. 先从第一张表中取一条记录，然后再与第二张表的第二条记录进行组合，生成一条新的记录
3. ……
4. 最后得到的结果就是一个全排列结果集

mysql> select * from student,class; +----+---------+----------+----+---------+ | id | name | class_id | id | name | +----+---------+----------+----+---------+ | 3 | 张三 | 1 | 2 | java112 | | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 2 | java112 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 5 | 王五 | 2 | 1 | java113 | | 7 | 钱七 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 1 | java113 | | 9 | 钱七1 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 1 | java113 | +----+---------+----------+----+---------+ 10 rows in set (0.00 sec)

通过观察，两张表取笛卡尔积之后，有些数据是无效数据

如何过滤掉这些无效数据？

2.通过连接条件过滤掉无效数据

两个表之间是有主外键关系，只需要判断两个表中主外键字段是否相等即可

mysql> select * from student,class where student.class_id=class.id; +----+---------+----------+----+---------+ | id | name | class_id | id | name | +----+---------+----------+----+---------+ | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 2 | java112 | +----+---------+----------+----+---------+ 5 rows in set (0.00 sec)

可以通过表名.列名的方式来解决这个问题

3.通过指定列查询，来精减结果集

查询列表中通过表名.列名的方式指定要查询字段

mysql> select student.id,student.name,class.name from student,class where student.class_id=class.id; +----+---------+---------+ | id | name | name | +----+---------+---------+ | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | +----+---------+---------+ 5 rows in set (0.00 sec)

通过给表名起别名的方式来简化SQL语句

mysql> select s.id,s.name,c.name from student s,class c where s.class_id=c.id; +----+---------+---------+ | id | name | name | +----+---------+---------+ | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | +----+---------+---------+ 5 rows in set (0.00 sec)

联合查询也叫表连接查询

1. 首先确定那几张表要参与查询
2. 根据表与表之间的主外键关系，确定过滤条件
3. 精减查询字段，得到想要的结果

二、内连接

内连接：符合条件的数据都查询出来，这个结果就是内连接的结果。

（一）语法

1select字段from表1别名1,表2别名2where连接条件and其他条件;

2select字段from表1别名1[inner]join表2别名2on连接条件where其他条件;

mysql> select s.id,s.name,c.name from student s inner join class c on s.class_id=c.id; +----+---------+---------+ | id | name | name | +----+---------+---------+ | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | +----+---------+---------+ 5 rows in set (0.00 sec)

inner 可以省略，join两边是参与查询的表，on后面跟的是连接条件

mysql> select s.id,s.name,c.name from student s join class c on s.class_id=c.id; +----+---------+---------+ | id | name | name | +----+---------+---------+ | 3 | 张三 | java113 | | 4 | 李四 | java113 | | 5 | 王五 | java112 | | 7 | 钱七 | java112 | | 9 | 钱七1 | java112 | +----+---------+---------+ 5 rows in set (0.00 sec)

（二）示例

1. 首先确定那几张表要参与查询
2. 根据表与表之间的主外键关系，确定过滤条件
3. 精减查询字段，得到想要的结果

• 查询“唐三藏”同学的成绩；

mysql> select s.name, sc.score from student s join score sc on sc.student_id = s.id where s.name = '唐三藏'; +-----------+-------+ | name | score | +-----------+-------+ | 唐三藏 | 70.5 | | 唐三藏 | 98.5 | | 唐三藏 | 33 | | 唐三藏 | 98 | +-----------+-------+ 4 rows in set (0.00 sec)

• 查询所有同学的总成绩及同学的个⼈信息

mysql> select s.name, sum(sc.score) from student s, score sc where sc.student_id = s.id group by (s.id); +-----------+---------------+ | name | sum(sc.score) | +-----------+---------------+ | 唐三藏 | 300 | | 孙悟空 | 119.5 | | 猪悟能 | 200 | | 沙悟净 | 218 | | 宋江 | 118 | | 武松 | 178 | | 李逹 | 172 | +-----------+---------------+ 7 rows in set (0.00 sec)

mysql> select * from class; +----+---------+ | id | name | +----+---------+ | 1 | java113 | | 2 | java112 | | 3 | java114 | +----+---------+ 3 rows in set (0.00 sec) mysql> select * from student; +----+---------+----------+ | id | name | class_id | +----+---------+----------+ | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | +----+---------+----------+ 5 rows in set (0.00 sec)

当前学生表中的记录，并没有一个学生的班级是java114

mysql> select * from student,class where student.class_id=class.id; +----+---------+----------+----+---------+ | id | name | class_id | id | name | +----+---------+----------+----+---------+ | 3 | 张三 | 1 | 1 | java113 | | 4 | 李四 | 1 | 1 | java113 | | 5 | 王五 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 9 | 钱七1 | 2 | 2 | java112 | +----+---------+----------+----+---------+ 5 rows in set (0.00 sec)

使用内连接时并没有java114班的数据

（一）语法

--左外连接，表1完全显⽰

select字段名from表名1leftjoin表名2on连接条件;

--右外连接，表2完全显⽰

select字段from表名1rightjoin表名2on连接条件;

mysql> select * from student right join class on student.class_id=class.id; +------+---------+----------+----+---------+ | id | name | class_id | id | name | +------+---------+----------+----+---------+ | 4 | 李四 | 1 | 1 | java113 | | 3 | 张三 | 1 | 1 | java113 | | 9 | 钱七1 | 2 | 2 | java112 | | 7 | 钱七 | 2 | 2 | java112 | | 5 | 王五 | 2 | 2 | java112 | | NULL | NULL | NULL | 3 | java114 | +------+---------+----------+----+---------+ 6 rows in set (0.00 sec)

class：右连接是以join右边的表为基准，这个表中的数据会全部显示出来，左边的表没有与之匹配的记录全部分NULL去填充。

（二）示例

• 查询没有参加考试的同学信息

1. 在同学表中有记录
2. 在分数表中没有该同学对应的记录

# 左连接以JOIN左边的表为基准，左表显⽰全部记录，右表中没有匹配的记录⽤NULL填充 mysql> select s.id, s.name, s.sno, s.age, sc.* from student s LEFT JOIN score sc on sc.student_id = s.id; +----+--------------+--------+------+------+-------+------------+-----------+ | id | name | sno | age | id | score | student_id | course_id | +----+--------------+--------+------+------+-------+------------+-----------+ | 1 | 唐三藏 | 100001 | 18 | 1 | 70.5 | 1 | 1 | | 1 | 唐三藏 | 100001 | 18 | 2 | 98.5 | 1 | 3 | | 1 | 唐三藏 | 100001 | 18 | 3 | 33 | 1 | 5 | | 1 | 唐三藏 | 100001 | 18 | 4 | 98 | 1 | 6 | | 2 | 孙悟空 | 100002 | 18 | 5 | 60 | 2 | 1 | | 2 | 孙悟空 | 100002 | 18 | 6 | 59.5 | 2 | 5 | | 3 | 猪悟能 | 100003 | 18 | 7 | 33 | 3 | 1 | | 3 | 猪悟能 | 100003 | 18 | 8 | 68 | 3 | 3 | | 3 | 猪悟能 | 100003 | 18 | 9 | 99 | 3 | 5 | | 4 | 沙悟净 | 100004 | 18 | 10 | 67 | 4 | 1 | | 4 | 沙悟净 | 100004 | 18 | 11 | 23 | 4 | 3 | | 4 | 沙悟净 | 100004 | 18 | 12 | 56 | 4 | 5 | | 4 | 沙悟净 | 100004 | 18 | 13 | 72 | 4 | 6 | | 5 | 宋江 | 200001 | 18 | 14 | 81 | 5 | 1 | | 5 | 宋江 | 200001 | 18 | 15 | 37 | 5 | 5 | | 6 | 武松 | 200002 | 18 | 16 | 56 | 6 | 2 | | 6 | 武松 | 200002 | 18 | 17 | 43 | 6 | 4 | | 6 | 武松 | 200002 | 18 | 18 | 79 | 6 | 6 | | 7 | 李逹 | 200003 | 18 | 19 | 80 | 7 | 2 | | 7 | 李逹 | 200003 | 18 | 20 | 92 | 7 | 6 | | 8 | 不想毕业 | 200004 | 18 | NULL | NULL | NULL | NULL | +----+--------------+--------+------+------+-------+------------+-----------+ 21 rows in set (0.00 sec) # 过滤参加了考试的同学 mysql> select s.* from student s LEFT JOIN score sc on sc.student_id = s.id where sc.score is null; +----+--------------+--------+------+--------+-------------+----------+ | id | name | sno | age | gender | enroll_date | class_id | +----+--------------+--------+------+--------+-------------+----------+ | 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 1 | +----+--------------+--------+------+--------+-------------+----------+ 1 row in set (0.00 sec)

四、自连接

（一）应用场景

自连接：一张表看作两张表，自己与自己进行表连接

可以把行转化为列，再查询的时候可以使用where条件进行过滤，也就是说可以实现行与行之间的比较功能

mysql> select * from exam; +------+--------------+---------+------+---------+ | id | name | chinese | math | english | +------+--------------+---------+------+---------+ | 1 | 唐三藏 | 67.0 | 98.0 | 56.0 | | 3 | 猪悟能 | 88.0 | 98.0 | 90.0 | | 4 | 曹孟德 | 70.0 | 60.0 | 67.0 | | 5 | 刘玄德 | 55.5 | 85.0 | 45.0 | | 6 | 孙权 | 70.0 | 73.0 | 78.5 | | 7 | 宋公明 | 75.0 | 65.0 | 30.0 | | 8 | 孙行者 | 87.5 | 78.0 | 77.0 | | 13 | 赵云 | 40.0 | 80.0 | 90.0 | | 14 | 黄忠 | 60.0 | 70.0 | 50.0 | | NULL | 测试用户 | 90.0 | 80.0 | NULL | +------+--------------+---------+------+---------+ 10 rows in set (0.00 sec)

这样的表设计，可以在一行中进行列与列之间的比较

（二）示例

• 案例：找出每个员工的直属领导，要求显示员工名、领导名。

1. 确定涉及的表 员工表，领导表
2. 取笛卡尔积

select e.ename 员工名, l.ename 领导名 from emp e join emp l on e.mgr = l.empno;

思路：
将emp表当做员工表 e

将emp表当做领导表 l

五、子查询

子查询是把一条SQL的查询结果，当做另一条SQL的查询条件，可以嵌套很多很多层，也叫嵌套查询。

子查询可以出现在哪里？

select...(select)

from...(select)

where...(select)

（一）语法

select*fromtable1wherecol_name1 {= |IN} (

selectcol_name1fromtable2wherecol_name2 {= |IN} [(

select...)

] ...

)

可以看出子查询是由很多条SQL语句组成的，也可以把子查询分成一条一条单独的语句去执行，最后再把结果和条件拼接在一起，得到查询结果。

（二）单行子查询

• ⽰例：查询与"不想毕业"同学的同班同学

mysql> select * from student where class_id = (select class_id from student where name = '不想毕业'); +----+--------------+--------+------+--------+-------------+----------+ | id | name | sno | age | gender | enroll_date | class_id | +----+--------------+--------+------+--------+-------------+----------+ | 5 | 宋江 | 200001 | 18 | 1 | 2000-09-01 | 2 | | 6 | 武松 | 200002 | 18 | 1 | 2000-09-01 | 2 | | 7 | 李逹 | 200003 | 18 | 1 | 2000-09-01 | 2 | | 8 | 不想毕业 | 200004 | 18 | 1 | 2000-09-01 | 2 | +----+--------------+--------+------+--------+-------------+----------+ 4 rows in set (0.00 sec)

（三）多行子查询

返回多行记录的子查询~~返回的是一个集合，集合包含多个对象

select * from table1 where table1.id IN (select id from table2 where xxx=...);

• 示例：查询"MySQL"或"Java"课程的成绩信息

mysql> select * from score where course_id in (select id from course where name = 'Java' or name = 'MySQL'); +----+-------+------------+-----------+ | id | score | student_id | course_id | +----+-------+------------+-----------+ | 1 | 70.5 | 1 | 1 | | 5 | 60 | 2 | 1 | | 7 | 33 | 3 | 1 | | 10 | 67 | 4 | 1 | | 14 | 81 | 5 | 1 | | 2 | 98.5 | 1 | 3 | | 8 | 68 | 3 | 3 | | 11 | 23 | 4 | 3 | +----+-------+------------+-----------+ 8 rows in set (0.00 sec)

（四）在from子句中使用子查询

将查询结果当做一张临时表

• 案例：找出每个部门的平均工资的等级。

1. 找出每个部门的平均工资

select deptno, avg(sal) avgsal from emp group by deptno;

2.将以上查询结果当做临时表t，t表和salgrade表进行连接查询。条件：t.avgsal between s.losal and s.hisal

select t.*,s.grade from (select deptno, avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;

（五）exists、not exists

语法：select * from 表名 where exists (select * from 表名1)；

exists后面括号中的查询语句，如果有结果返回，则执行外层的查询，如果返回的是一个空结果，则不执行外层的查询

内层查询返回非空结果集

mysql> select * from student where id=3; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 3 | 张三 | 1 | +----+--------+----------+ 1 row in set (0.00 sec)

mysql> select * from student where exists ( select * from student where id=3); +----+---------+----------+ | id | name | class_id | +----+---------+----------+ | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | +----+---------+----------+ 5 rows in set (0.00 sec)

exists相当于if语句的判断条件，有结果返回true，没有返回false

内层查询返回空结果集，外层查询也返回空结果集，也可以说外层查询没有执行

mysql> select * from student where id=10; Empty set (0.00 sec) mysql> select * from student where exists ( select * from student where id=10); Empty set (0.00 sec)

mysql> select NUll; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> select * from student where exists ( select null); +----+---------+----------+ | id | name | class_id | +----+---------+----------+ | 3 | 张三 | 1 | | 4 | 李四 | 1 | | 5 | 王五 | 2 | | 7 | 钱七 | 2 | | 9 | 钱七1 | 2 | +----+---------+----------+ 5 rows in set (0.00 sec)

返回的结果集是一个非空的，只不过列名为null，值也为null而已

六、合并查询

union和unionall

不管是union还是union all都可以将两个查询结果集进行合并。
union会对合并之后的查询结果集进行去重操作。
union all是直接将查询结果集合并，不进行去重操作。（union all和union都可以完成的话，优先选择union all，union all因为不需要去重，所以效率高一些。）

• 案例：查询工作岗位是MANAGER和SALESMAN的员工

select ename,sal from emp where job='MANAGER' union all select ename,sal from emp where job='SALESMAN';

以上案例采用 in 也可以完成，那 in 和union all有什么区别？
考虑走索引优化之类的选择union all，其它选择 in（in 使用不当很容易导致索引失效）。【in 比 or 效率高】
两个结果集合并时，列数量要相同。