子查询
子查询
在整个SQL查询语句的过程之中,子查询并不是具备特殊的语法,也就是说在整个SQL查询操作里面子句:SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY.如果非要给出子查询的语法,那么只能进行拼凑了。
所有可能出现的子查询都需要使用“()”声明。
子查询语法(实际上是查询嵌套)
SELECT [DISTINCT] * | 字段 [别名] | 统计函数, (
SELECT [DISTINCT] * | 字段 [别名] | 统计函数,
FROM 表 [别名], 表 [别名],…
[WHERE 条件(s)]
[GROUP BY 分组字段,分组字段,…]
[HAVING 分组过滤]
[ORDER BY 排序 []ASC | DESC], 排序 [ASE | DESC],…]
)
FROM 表 [别名], 表 [别名],…(
SELECT [DISTINCT] * | 字段 [别名] | 统计函数,
FROM 表 [别名], 表 [别名],…
[WHERE 条件(s)]
[GROUP BY 分组字段,分组字段,…]
[HAVING 分组过滤]
[ORDER BY 排序 []ASC | DESC], 排序 [ASE | DESC],…]
)
[WHERE 条件(s)](
SELECT [DISTINCT] * | 字段 [别名] | 统计函数,
FROM 表 [别名], 表 [别名],…
[WHERE 条件(s)]
[GROUP BY 分组字段,分组字段,…]
[HAVING 分组过滤]
[ORDER BY 排序 []ASC | DESC], 排序 [ASE | DESC],…]
)
[GROUP BY 分组字段,分组字段,…]
[HAVING 分组过滤](
SELECT [DISTINCT] * | 字段 [别名] | 统计函数,
FROM 表 [别名], 表 [别名],…
[WHERE 条件(s)]
[GROUP BY 分组字段,分组字段,…]
[HAVING 分组过滤]
[ORDER BY 排序 []ASC | DESC], 排序 [ASE | DESC],…]
)
[ORDER BY 排序 []ASC | DESC], 排序 [ASE | DESC],…]
查询子句的任意位置上都可以随意出现子查询,出现子查询最多的位置:WHERE、FROM 、HAVING参考使用方案
- WHERE子句:子查询返回单行单列、单行多列、多行单列;
- HAVING子句:子查询返回单行单列,而且要使用统计函数过滤;
- FROM 子句:子查询返回的是多行多列;
- SELECT子句:一般返回单行单列,而且需要某些查询的时候使用;
1.在WHERE子句中使用子查询
WHERE子句主要是进行数据的筛选,通过分析发现,单行单列、多行单列、单行多列都可以在WHERE子句中出现。
1.子查询返回单行单列
范例:要求查询出公司工资最低的雇员信息
但是现在很明显,不可能拿出800这个数据直接使用。因为这个数据需要用到统计函数min()
第一步:统计出公司的最低工资
第二步:以上的查询会返回单行单列的数据,本质就是一个数值。
范例:查找出公司雇佣最早的雇员
最早的雇员一定是雇佣日期最小,那么使用min()函数完成。
2、子查询返回单行多列
范例:查询出于SCOTT工资相同,职位相同的所以雇员信息
第一步:查询SCOTT的工资与职位
第二步:查询与SCOTT信息相同的雇员
3.子查询返回多行单列(重点)
如果子查询返回了一个多行单列的数据,那么实质上就相当于告诉用户一个劲数据的操作范围,而如果要进行范围判断,在WHERE子句里面提供有主要的三个运算符:IN,ANY,ALL.
1.IN 操作
范例:查询出与经理工资相同的员工
对于IN操作还可以使用NOT IN 进行。
注意:NOT IN 里面的数据不能为null
2.ANY操作
对于ANY操作实质上有三种语法:
1.=ANY: 功能上与IN没有区别
2.>ANY:比子查询返回的最小内容要大
3.<ANY:比子查询返回的最大的内容要小
3.ALL操作
ALL操作有两种使用形式
1.>ALL:比子查询返回最大的值要大
2.<ALL:比子查询返回最小的值要小
4.exists()判断
如果现在子查询有数据返回(不管什么数据)就表示条件满足,那么就可以显示出数据,否则不显示
范例:观察exists()操作
因为此时的子查询没有返回任何的数据行,所以exists()就认为数据不存在,外部查询无法查询出
范例:观察exists()操作
范例:观察exists()操作
使用exists()只关心子查询里面返回的是否有行,至于什么行,它不关心。
范例:使用NOT EXISTS()
对于IN主要是进行数据的判断,EXISTS()是针对于是否存在数据行判断。很明显EXISTS比IN性能更高,因为不关心具体的数据。
2.在HAVING子句里面使用子查询
如果要使用到HAVING那么必须要结合GROUP BY子句,而如果要使用GROUP BY子句就一定要分组。
范例:要求统计出所有高于平均工资的部门编号、平均工资、部门人数
第一步:根据部门编号分组,统计出每个部门编号的平均工资、部门人数
第二步:如果想知道那些部门的工资高于公司的平均工资,则应该进行emp表的统计查询
此时的子查询返回了单行单列的数据,那么肯定要在HAVING子句中使用
第三步:对数据过滤
3.在SELECT 子句中使用子查询(了解)
首先要明确的是,这样的操作意义不大,而且性能不高。
范例:查询每个雇员的编号、姓名、职位、部门名称
按照正常的思路,肯定使用多表查询。
现在利用子查询,在SELECT 子句里面简化操作
实际上在SELECT 子句里面出现的子查询核心的目的在于:行列转换。
4.在FROM 子句中出现子查询(重点)
为了解释这种查询的作用,下面做一个简单的查询。
范例:查询出每个部门的编号、名称、位置、部门人数、平均工资
除了以上方式之外,也可以利用子查询完成。
首先dept是一张数据表,但是对于数据的统计查询,也可以将其定义为一张表
此时的查询返回的是一个多行多列的数据,那么只要是多行多列,就一定可以在FROM 子句中出现
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d,(SELECT deptno,COUNT(empno) count ,AVG(sal) avg FROM emp GROUP BY deptno) temp
WHERE d.deptno=temp.deptno(+);
现在有两种方式可以实现同样功能的查询,,那么这两种方式有什么区别呢?使用哪种?
为了更好的解释此类问题,现在将数据假设扩大一百倍,即:此时的emp表中有1400条记录, dept有400条记录
分析两种方式:
-
多表查询后分组统计:
|-数据量:emp表的1400*dept表的400=640000;
-
子查询分组统计:
|-FROM子句的数据量:1400行记录,最多会返回400条记录
|-与dept表查询:dept表的400*最多返回的400条=160000;
|-数据量:分组的1400行+160000=161400;
多表查询永恒都会存在有性能问题,而子查询主要的目的是为了解决多表查询性能问题而产生的。