扫码加入数据分析学习群

子查询

子查询又称为嵌套查询，它是指在一个select语句中包含另一个或多个完整的select语句。

子查询的语法规则：

子查询需要用圆括号括起来。

子查询多可以嵌套到32层（个别查询可能会不支持32层嵌套）。

执行顺序由内到外，先执行内部的子查询，再执行外部的主查询。

1.子查询分类

按子查询返回的结果，可分为：

标量子查询：返回的结果是一个数据（单行单列） （select，where，having）

行子查询：返回的结果是一行（单行多列） （where，having）

列子查询：返回的结果是一列（多行单列） （where，having）

表子查询：返回的结果是一张临时表（多行多列）（from）

按子查询出现的位置，可分为：

select子句中：将子查询返回的结果作为主查询的计算字段（标量子查询）

where或having子句中：将子查询返回的结果作为主查询的筛选条件（标量子查询、行子查询、列子查询）

from子句中：将子查询返回的结果作为主查询的一张表（表子查询）

2.子查询常用运算符

子查询出现在where或having子句中时，可以使用>、>=、<、<=、=、<>/!=等比较运算符或[not ]in、 any/some、all、[not ]exists等操作符进行条件筛选。

标量子查询：查询基本工资高于公司平均工资的员工信息

select * from emp where sal > (select avg(sal) from emp);

行子查询：查询和smith同部门同职位的员工

select * from emp where (deptno,job)=(select deptno,job from emp where ename='smith') and ename<>'smith';

列子查询：where子句中可使用[not ]in、any/some、all、[not ]exists等操作符进行条件筛选。

[not ]in子查询：查询普通员工的信息

select * from emp where empid not in (select mgr from emp where mgr is not null);

any/some子查询：查询基本工资高于30号部门任意员工的员工信息

select * from emp where sal>any(select sal from emp where deptno=30) and deptno<>30;

all子查询：查询基本工资高于30号部门所有员工的员工信息

select * from emp where sal>all(select sal from emp where deptno=30);

exists子查询：如果dept表中存在30号部门则查询该部门的员工信息

select * from emp where deptno=30 and exists (select deptno from dept where deptno=30);

表子查询：查询各部门工资高的员工

select emp.* from emp join (select deptno,max(sal) as max_sal from emp group by deptno) as t on emp.deptno=t.deptno where sal=max_sal;

3.子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。执行子查询时，MySQL需要为内层子查询的查询结果建立一 个临时表，然后外层主查询在临时表上进行查询和筛选。查询完毕后再撤销这些临时表，这里多了一个 创建和销毁临时表的过程。因此，子查询的速度会受到一定的影响，如果查询的数据量比较大，这种影响就会随之增大。

优化方法：可以使用连接查询（join）代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

所有的连接查询都可以替换为子查询，但并不是所有的子查询都可以用连接查询代替。当where子句中 需要使用聚合函数作为筛选条件时，只能使用子查询。