SQL备忘--子查询与ALL/ANY运算符

概念

在SQL查询语句中包含了其他的查询语句，出现了SQL语句的嵌套，即为使用了嵌套子查询

子查询用法

子查询可以用在：

• SELECT查询字段中
• FROM表名语句中
• WHERE条件中

由此可见子查询用法广泛，可用在SQL基本语句的每个位置。但具体每个位置的查询会有要求，下面具体说明

1. SELECT查询字段中

可直接用在select处的查询字段输出中，如下：

SELECT name as "老师姓名", 
(	
	SELECT AVG(s.score) 			-- 均分	
	FROM student s
	WHERE s.class = t.class		-- 此老师的班级
) as "学生平均分"
FROM teacher t

此时需注意：

1. 子查询可以使用外部查询的字段，也就是和外部查询产生关联（关联子查询Correlated Subquery）
2. 查询结果必须只有单个值（标量子查询Scalar Subquery），即不能出现多条数据，也不能出现多个列，否则会报错。本例通过AVG()函数确保只返回一个值

2. 在FROM表名语句中

SELECT t.name as "老师姓名", 
s.avg_score	as "平均分"
FROM teacher t,
(	
	SELECT class, AVG(score) as avg_score
	FROM student
	GROUP BY class
) s
WHERE t.class = s.class

此时注意点：

1. 子查询可以使用外部查询的字段，也就是和外部查询产生关联，此时需要用 LATERAL关键字。本例中可以写为：

SELECT t.name as "老师姓名", 
ss.avg_score as "平均分"
FROM teacher t,
LATERAL (	
	SELECT AVG(s.score) as avg_score				
	FROM student s
	WHERE t.class = s.class
)  ss

仅Oracle、Mysql、PostgreSQL支持 LATERAL关键字

2. 查询结果可以是单值，也可以是多条数据（集合）。若为多条数据时需要注意匹配，防止出现笛卡尔积的情况

3. 在WHERE条件中

SELECT *
FROM teacher t
WHERE class IN (	
	SELECT s.class 			-- 查找均分大于80分的班级		
	FROM student s
	WHERE s.class = t.class		-- 筛选有老师的学生班级
	GROUP BY s.class 
	HAVING AVG(s.score) > 80
)

此时需注意：

1. WHERE子查询可以使用外部查询的字段，也就是和外部查询产生关联（关联子查询Correlated Subquery）
2. 查询结果即可以为单个值（标量子查询Scalar Subquery），也可为一行数据（一行数据多列，行子查询Row Subquery）、多条数据（Table Subquery）。

单值时，外部可用=号判断子查询的结果。此时需确保查询结果不能出现多行或多列的情况，否则sql报错
单行数据时，外部也可以用=号判断子查询的结果，只是等号左边要写成多个数据在()内的格式
多行数据时，使用IN、或者ALL、ANY运算符来进行匹配，例如下例子

WHERE (class, grade) IN (	
	SELECT s.class , s.grade			
	FROM student s
	WHERE s.class = t.class	
	GROUP BY s.class, s.grade 
	HAVING AVG(s.score) > 80
)

子查询中的ALL、ANY运算符

SQL的子查询中，可能查询到多个结果，因此一般都用IN来匹配

SELECT *
FROM student
WHERE class IN (
	SELECT class 
	FROM teacher
	WHERE grade = 1
)

此时，可以通过比较运算符(=、!=、<、<=、>、>=)与ALL、ANY的组合，来表示等于、不等于、大于…集合中的全部数据

SELECT *
FROM student
WHERE class =ANY (		-- 查找属于1年级的学生
	SELECT class 
	FROM teacher
	WHERE grade = 1
)

ALL运算符相当于：对其中每个选项进行比较运算符计算，并用AND运算符串联
IN运算符相当于：对其中每个选项进行=运算符计算，并用OR运算符串联
ANY与IN类似，也是由OR运算符串联，比较运算符写于ANY之前；如果是=ANY，则与IN相同

要注意：

• "=ALL"必然会返回NULL（除非后面的集合中只有一个元素值）。因为它等同于 c1 = “x1” AND c1 = “x2” AND c1 = “x3” … ，当等式有两项及以上时，表中的每条数据带入此公式中，必然有被判断为FALSE的子项，导致整个判断为FALSE，数据被过滤掉
• !=ANY必然返回所有结果（除非后面的集合中只有一个元素值）。因为它等同于 c1 != "x1 OR c1 != “x2” OR c1 != “x3” …，当等式有两项及以上时，表中的每条数据带入此公式中，必然有被判断为TRUE的子项，导致整个判断为TRUE，使得数据被选择

SELECT *
FROM student
WHERE class !=ANY (		-- 查找不属于1年级所有班级的学生，设施
	SELECT class 
	FROM teacher
	WHERE grade = 1
)