子查询类别详细分类与规则

一.子查询

1.子查询的分类及其作用

什么是子查询、主查询，哪个是先被执行的语句？

为了给主查询提供数据源，而首先被执行的一个查询被称为子查询，通常子查询出现在语句的右边，用括号括起来。

作为三大数据源：表、试图、子查询中的一种，子查询会在某些特定的场景发挥作用。

子查询作为一条查询语句，可以返回如下类型：

单行单列

查询与ALLEN相同岗位的员工的薪资 

select  sal
from emp
where job = (select job from emp where ename = 'ALLEN');

多行单列

查询员工号和上级为7698相同的员工的薪资

select sal
from emp
where empno = (select empno from emp where mgr = 7698);

单行多列

查询上级和岗位分别和员工号为7566相同的员工的薪资

select sal
from emp 
where (mgr,job) in (select mgr,job from emp where empno = 7566);

多行多列

注意事项：

单行多列、单行单列要使用单行运算符

>  <  <>  =  >=  <=

当子查询的结果是多行多列、多行单列时就不能使用单行运算符（例如子查询使用group by 进行分组）

当子查询没有结果时，sql语句不会报错，主查询也将没有结果

select sal
from emp 
where empno = (select mgr from emp where empno = 8000);

多行单列子查询，要使用多行比较运算符

in 

查询岗位薪资大于3000的员工姓名、员工号、薪资、部门号

select emp.ename,emp.empno,emp.sal,emp.deptno
from emp
where job in (select job from emp where sal>3000);

in可以满足当子查询返回多行单列时的情况

all 

>all   大于子查询的最大值

查询30号部门的薪资

select sal from emp where deptno = 30

查询比30号部门薪资最高的都高的员工信息

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

<all  小于子查询的最小值

 查询比30号部门薪资最低的都低的员工信息

select e.*
from emp e 
where e.sal < all(select sal from emp where deptno = 30);

any

查询10号部门的薪资

查询20号部门的薪资

>any 大于子查询的最小值

查询比10号部门薪资高的员工信息

select e.*
from emp e 
where e.sal > any(select sal from emp where deptno = 10);

<any 小于子查询的最大值

查询比20号部门薪资低的员工信息

select e.*
from emp e 
where e.sal < any(select sal from emp where deptno = 20);

多行多列子查询

使用in

成对的比较

select empno,ename,mgr,job
from emp
where (mgr,job) in(select mgr,job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno!= 7369;

非成对比较

select empno,ename,mgr,job
from emp
where mgr in(select mgr from emp where empno = 7566 or empno = 7369)
and job in(select job from emp where empno = 7566 or empno = 7369)
and empno != 7566 and empno!= 7369;

结果都是

2.子查询还可以当做一个虚拟的表作为数据源