相关子查询

相关子查询的概述

我们前面把单行子查询和多行子查询就已经说完了,我们现在要把继续把相关子查询的内容给大家说明白,因为之前用的都是不相关子查询的案例,

我们来谈一谈相关子查询,记住单行子查询和多行子查询是可以进行相关子查询,只是我们刚才写的案例都是相关子查询的案例.

我们看一下,如果子查询的执行依赖于外部查询,用到了外部的表,每次查询的时候都要执行一次外部查询,那么这样的子查询就被我们叫做关联子查询,下面列出了子查询的步骤,

比如我们之前的案例,查询比abel这个人工资高的员工姓名和工资

这里内查询是11000的值,我们外查询的107个值和这11000进行对比,是否满足关系.如果结果是1我们就要,结果是0我们就不要.

而相关子查询就不一样的.

比如

我们这里内查询的表是不是出现了外查询的数据.我们实际步骤是从外查询找到了一条数据,然后送到内查询中去,送进去的值我们看看要怎么用,怎么运算.通过这个运算,返回一个SELECT的结果,可能是一个列也可能是2个列.第三步拿着我们送进去的数据结果和条件比较,然后最后对比结果是1我们就要,不是我们就不要,我们那一个案例来说明

WHERE中的相关子查询

题目1,我们要查询员工工资大于本部门平均工资的员工姓名工资和其部门id

那么我们是不是假如查询的是张三,那么子查询的内容就是张三部门的平均工资,如果查询的是李思,子查询的内容就是李四部门的平均工资,子查询的内容是跟随外部数据的变化而变化的就说明这是相关子查询,

我们来看看怎么做:

首先我们看看

题目查询员工工资大于60号部门平均工资的员工姓名工资和其部门id怎么写

SELECT last_name,salary

FROM employees

WHERE salary>(

              SELECT AVG(salary)

              FROM employees

              WHERE department_id=60)

但是实际是我们要的是本部门,所以60这个值需要修改成外查询的部门,所以我们给外查询的表起个名T1,内查询表起个名T2

SELECT last_name,salary

FROM employees T1

WHERE salary>(

              SELECT AVG(salary)

              FROM employees T2

              WHERE T2.department_id=T1.department_id)

我们执行一下

FROM中的相关子查询

这个子查询我们也可以用在FROM中声明子查询或者说连接子查询的的方法去写这个内容

如果我们的表里面多一个字段是部门的平均工资,那么是不是就可以直接用这个字段来筛选我们的表呢.自然是可行的.解答如下,我们把这两个表连接起来

SELECT last_name,salary

FROM employees T1 JOIN

(SELECT department_id,AVG(salary) avg_salary FROM employees T2 GROUP BY department_id) T2

ON T1.department_id=T2.department_id

WHERE T1.salary>T2.avg_salary

注意这里必须给表的聚合函数起一个别名才能连接使用

ORDER BY中使用子查询

查询员工的id,salary 安装department_name 排序

SELECT employee_id,salary

FROM employees e

ORDER BY(SELECT T1.department_name

          FROM departments T1

          WHERE T1.department_id=e.department_id)

          ASC

结论

子查询除了在GROUP BY和LIMIT中其他地方都可以使用子查询

题目,如果employee表中employee_id

与job_histoty表中的employe_id相同的数目不小于2输出相同id的员工的employee_id,last_name和其job_id

employee_id

这里就是要找到我们在job_history中调岗超过2次的员工

我们首先

SELECT employee_id,last_name,job_id FROM employees T1

WHERE (SELECT count(employee_id)

        FROM job_history T2

        WHERE T2.employee_id=T1.employee_id)

        >=2

这里是不是就相当于这个员工出现了2次不同工作的情况,说明调过岗位.

而且这里我们子查询既然出现了外查询的表,那就说明是相关子查询

关键词EXISTS 与NOT EXISTS

这两个关键字代表的是是否存在满足条件的行,一般出现这种情况属于相关子查询,因为如果子查询不和外查询相关的话,这个查询也就一直是FALSE或者一直是TRUE,那么EXISTS关键字就没有意义了.

查询公司管理者的employee_id,last_name,job_id,department_id

方法1:自连接

SELECT DISTINCT T2.manager_id,T1.last_name,T1.job_id,T1.department_id FROM employees T1 JOIN

employees T2 ON T1.employee_id=T2.manager_id

注意这里使用的是T1的字段,如果是T2的字段,代表的含义就是这个id的员工管 理了几个人

方法2:子查询

方法3:EXISTS

这个关键词的含义是把子查询的WHERE当成一个条件,而WHERE会把每一行数据都送进来进行判断,如果判断成功,WHERE就会把这条记录保留下来.所以本质是EXISTS是类似PYTHON和JAVA中的IF满足条件.也就是说前面SELECT后面跟的字段完全没有意义.

题目查询departments表中不存在于employees中的部门的department_id和department_name

这个题目是不是类似于我们讲内连接的时候的时候满足员工是空,部门存在的情况

SELECT department_id,department_name FROM departments T2

WHERE NOT EXISTS(SELECT * FROM employees T1

WHERE T1.department_id= T2.department_id)

SELECT employee_id,T2.department_id FROM employees RIGHT JOIN departments T2

ON employees.employee_id IS NULL

更新中的相关子查询

在员工表中添加一个department_name字段,数据为员工对应的部门名称

UPDATE employees e

SET department_name =(SELECT department_name

FROM departments d

WHERE e.department_id=d.department_id)

这个update本来是添加列的,我们现在添加列的值是利用=,每一行都会运行一遍子查询,寻找其中这行对应的department_name的值.

这是单行子查询,并且也是相关子查询

删除中的相关子查询

DELETE FROM employees e

WHERE employee_id IN (SELECT employee_id FROM emp_history

WHERE employee_id=e.employee_id)

思考题:谁的工资比Abel高呢,使用那个方法效率高

第一个方法是自连接,直接每个表都连上abel的工资,然后选出大于abel的

SELECT department_id,department_name FROM departments T2

WHERE NOT EXISTS(SELECT * FROM employees T1

WHERE T1.department_id= T2.department_id)

第二个方法是子查询,直接筛选条件,大于abel的

SELECT employee_id FROM employees

WHERE  salary>(SELECT salary FROM employees

WHERE last_name='abel')

结论:多表连接

是方法要用多表连接的方法,现在数据库会自动识别这种可以直接转换为自连接.的子查询,把它转化为自连接,因为DBMS系统都对自连接这种多表查询的方式进行了优化,而子查询是根据未知的表查询后的条件判断.所以前者快.具体原因我们下篇再说