16、子查询

学习目标：

1、掌握子查询的语法

2、掌握相关子查询的含义

3、掌握

学习过程：

首先，我们先讲讲子查询的语法，在一条查询语句中可能会有两个select查询，其中内部查询也称为子查询，是在主查询之前执行一次的，子查询的结果被用于主查询（外部查询）。其常用个课时如下：

select * from 表 where (condition  包含另外一个查询也就是子查询。)

一、单行子查询

有的时候返回的结果只有一行数据，那么我们使用单行比较运算符，就是可以直接时候用

查询工资比奥巴马高的所有的员工，这个题目事实上需要做连个查询

1、查询奥巴马的工资。

select  salary from employee where employee_name='奥巴马'

得到一行查询结果如下：

select * from employee where salary>2000

2、根据奥巴马的工资作为比较条件，查询比它高的工资的员工信息

上面两条查询事实上可以使用子查询整合成为一条查询语句：

select * from employee where salary>(select  salary from employee where employee_name='奥巴马')

子查询会先执行。然后根据子查询的结果传入主查询。

类似例子还有：

--查询所有比平均工资高的员工
select * from employee where salary>(select avg(salary) from employee)

--查询部门1里面比平均工资高的员工
select * from employee where salary>(select avg(salary) from employee) and dep_id=1

二、多行子查询

上面的子查询的结果都只有一行语句返回，如果子查询有多行语句返回那么直接使用比较符合就会报错，我们需要结合下面几个关键字，更好表达出我们的查询要求：

• IN：表示与列表中的任一成员相等(1,2,3 )
• ANY：表示与子查询返回的每一个值比较，只要有一个满足条件即可。
• ALL：表示与子查询返回的所有值比较，必须全部都符合条件才行。

如下面几个例子：

--查询部门1里面比部门2的所有人得工资都高员工
select * from employee where dep_id=1 and salary > all(select salary from employee where dep_id=2)

--查询部门1里面比部门2的其中一个人得工资高员工
select * from employee where dep_id=1 and salary > any(select salary from employee where dep_id=2)

--查询部门1和部门2工资有相同的员工
select * from employee where dep_id=1 and salary in(select salary from employee where dep_id=2)

三、相关子查询

子查询参考父查询表中的一列，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。所以相关子查询的效率是比较低的。

如下面这个例子：

--查询所有工资超过他们本部门的平均工资的员工信息
select * from employee oe  where salary>(select avg(salary) from employee where dep_id=oe.dep_id)

每一次子查询都需要从主查询中获得当前员工的部门id，这里我们给主查询的表设置了一个别名，以在子查询中表区分开来。

四、使用EXISTS运算符

EXISTS运算符检测子查询的结果集中行的存在，他的执行过程是：如果一个子查询的行值被找到，内部查询中的搜索将不会继续，条件将会标记为TRUE。相反，如果一个子查询的行值没有被找到，条件将会标记为FALSE。

--查找有个工作职位变动的员工的姓名
select employee_name from employee e 
   where exists(select * from JOB_HISTORY where employee_id=e.employee_id)

四、使用EXISTS和in语法的选中

通过上面的学习，你会发现事实上Exists和in可以互相替换的，比如说我们现在有A、B两张表，其中A表的id在B表中也有对应的储存，现在要查询A表中id有在B表中的数据，使用in语法如下

select * from A   where id in(select id from B)

如果使用EXISTS，我们可以这样写

select a.* from A a  where exists(select 1 from B b where a.id=b.id)

那么这两种写法有什么区别呢？我们可以分析一下：

in语法：,in()里面的查询只执行一次，把所有数据查询出来并缓存起来，然后判断A表的id是否有和B表中的id相等，如果有相等就把A表的记录加入结果集中，直到遍历完A表的所有记录，如果B表中查询到的数据非常多，那么需要缓存的数据量会很大，严重占有内存，在生产环境随着数据量增大，这有可能操作非常严重的问题。

EXISTS运算：EXISTS并不缓存exists()结果集，只是循环的获得A表中的Id，判断是否在B表中是否有数据，如果有则返回true,没有则返回false。如果A表中的数据量很大，那么需要循环判断的次数非常多。查询效率会比较低。

综合来说，in适合B表比A表数据小的情况，反之使用exists，如果你并不清楚以后那个表的数据会比较多，那么可以先使用exists。