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

发布时间:2023年12月27日

概念

在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关键字

  1. 查询结果可以是单值,也可以是多条数据(集合)。若为多条数据时需要注意匹配,防止出现笛卡尔积的情况
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
)
文章来源:https://blog.csdn.net/u010086122/article/details/134181216
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。