sql子查询详解

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

一、案例

1.实际问题

先引出几张简单的表，供后续使用，分别为emp员工表：

dept部门表

我们需要查出工资比id为2的员工的工资多的所有用户。

解决方式：

1.使用两条语句分别去查询
select salary from emp where id = 2;

select * from emp where salary > 1200

#2.使用子查询嵌套查询
select * 
from emp 
where salary > (
						select salary 
						from emp 
						where id = 2
				)

#3.使用自连接查询
select e2.*
from emp e1,emp e2
where e1.salary < e2.salary
	and e1.id = 2

题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。 可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表 进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

2.子查询的基本使用

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。

注意事项：

• 子查询要包含在括号内
• 最好将子查询放在比较条件的右侧
• 单行操作符对应单行子查询，多行操作符对应多行子查询

3.子查询的分类

• 分类方式1：

我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询 、 多行子查询 。上面的例子是单行子查询，因为id为2的最多只有一条记录。

• 分类方式2：

我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

二、单行子查询

1.单行比较操作符

单行比较可使用的操作符有：

• =
• >、>=
• <、<=
• <>、!=

2.代码示例

题目1：查询部门和id为2的员工相同，且工资大于id为2的员工信息

select * 
from emp 
where dept_id = (select dept_id from emp where id=2)
and salary > (select salary from emp where id=2)

题目2：返回公司工资最少的员工的信息

select * 
from emp 
where salary = (select min(salary) from emp)

3.having中的子查询

• 首先执行子查询
• 向主查询的having子句返回结果

题目：查询最低工资大于2号部门最低工资的部门id和其最低工资 

select dept_id,min(salary)
from emp 
where dept_id is not null
group by dept_id 
having min(salary) >
					(
						select min(salary) 
						from emp 
						where dept_id=2
					)

4.非法子查询

select *
from emp 
where salary > (
					select min(salary) 
					from emp
					group by dept_id
				)

多行子查询使用单行比较符错误

三、多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

1.多行比较操作符

操作符	含义
in	等于列表中的任意一个
any	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
some	实际上是any的别名，作用相同，一般常使用ANY
all	需要和单行比较操作符一起使用，和子查询返回的所有值比较

2.代码示例

题目1：返回其它部门中比1号部门中任一工资低的员工信息

select *
from emp 
where salary < any (
		            select salary
		            from emp 
		            where dept_id = 1
		            )
and dept_id <> 1

 本例的any就代表取到最大值。

题目2：返回其它部门中比1号部门中所有员工都低的员工信息

select *
from emp 
where salary < all (
		            select salary
		            from emp 
		            where dept_id = 1
		            )
and dept_id <> 1

本例的all相当于取到最小值。

 题目3：查询平均工资最低的部门id

#方式1
select dept_id
from emp 
group by dept_id
having avg(salary) =  (
					   select min(salary)
					   from
					      (
							select avg(salary) as "salary"
							from emp
							group by dept_id
						  ) t_avg
					  )

#方式2
select dept_id
from emp 
group by dept_id
having avg(salary) <= all  (
							select avg(salary) as "salary"
							from emp
							group by dept_id
							having dept_id is not null
							)

注意空值问题，当子查询得到的结果中有空值时，有些操作的结果就会为空

四、相关子查询

1.执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。 相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

 说明：子查询中使用主查询中的列

前面的都是不相关子查询，如第一个案例中

 子查询的结果最先执行，然后得到一个固定的常量值，主查询再每次遍历时都只是比较该常量值。而相关子查询每次主查询前都会重新执行子查询。

2.代码示例

题目1：查询工资比该部门的平均工资高的员工信息

#方式1:相关子查询
select *
from emp e
where salary > (
		        select avg(salary)
		        from emp
		        where dept_id = e.dept_id 
                )

#方式2:在from中使用子查询
select *
from emp,(
		  select dept_id , avg(salary) avg_salary
		  from emp
		  group by dept_id
		  ) t_dept_avg
where emp.dept_id = t_dept_avg.dept_id
			and salary > avg_salary

题目2：查询员工信息，按照部门名排序

#在order by中使用子查询
select *
from emp e 
order by (
        select dept.name
		from dept
		where dept.id = e.dept_id
		)

除了在group by和order by中不能使用子查询外，其他的都可以使用

3.EXISTS 与 NOT EXISTS关键字

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

4.相关更新

UPDATE table1 alias1
SET column = (SELECT expression
              FROM table2 alias2
              WHERE alias1.column = alias2.column
              )

5.相关删除

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
                       FROM table2 alias2
                       WHERE alias1.column = alias2.column
                        );

6.相关插入

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

不必书写 VALUES 子句。