子查询
说白了子查询就是嵌套查询
SQL 中子查询的使用大大增强了 SELECT 查询的能力
因为很多时候查询需要从结果集中获取数据,或者需要从同一个表中先计算得出一个数据结果,然后与这个数据结果(可能是某个标量,也可能是某个集合)进行比较。
需求分析
如果我们遇见下面这种问题,就要用到子查询了
现有方法:
- 先查出Abel的工资,然后那结果在查询一次进行筛选
#方式一: SELECT salary FROM employees WHERE last_name = 'Abel'; SELECT last_name,salary FROM employees WHERE salary > 11000;
- 自连接
SELECT e2.last_name, e2.salary FROM employees e1, employees e2 WHERE e1.last_name = 'Abel' AND e1.`salary` < e2.`salary`
- 子查询
SELECT last_name, salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel')
子查询的分类
单行、多行子查询
按查询的结果返回一条还是多条记录,将子查询分为单行子查询、多行子查询
- 子查询(内查询)在主查询之前一次执行完成。
- 子查询的结果被主查询(外查询)使用 。
- 注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧(提高可读性)
- 单行操作符对应单行子查询,多行操作符对应多行子查询
- 单行子查询就是子查询查出的结果就一条
- 多行子查询就是子查询查出的结果不止一条
相关、不相关子查询
我们按内查询是否被执行多次,将子查询划分为 相关(或关联)子查询 和 不相关(或非关联)子查询 。
子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条 件进行执行,那么这样的子查询叫做不相关子查询。(子查询的结果是固定的)
同样,如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查 询,然后再将结果反馈给外部,这种嵌套的执行方式就称为相关子查询。(子查询的结果不固定,会根据外面传过来的值改变)
单行子查询
单行比较操作符
什么是单行操作符
就是它只能匹配一行数据,如果你的子查询查出了多条数据,那么它就报错
代码示例
题目:查询工资大于149号员工工资的员工的信息
题目:返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
写子查询什么思路呢?
根据题目,先单独写出子查询,最后添加到查询语句中
比如题目说返回job_id与141号员工相同,那我们就可以先写出141号员工的job_id是多少的查询语句,之后在放到总查询语句中
SELECT last_name, job_id, salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary > ( SELECT salary FROM employees WHERE employee_id = 143 )
题目:返回公司工资最少的员工的last_name,job_id和salary
先通过子查询获得工资最少的工资数,然后把这个当成筛选条件获取是谁的工资这么低
SELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees )
题目:查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id,department_id
最后的AND employee_id NOT IN (174, 141);是为了去掉141、174员工它们本身,因为题目要求是求其他员工的…
这里的IN属于多行子查询操作符,这里提前用到了。因为子查询结果不唯一
-- 不成对比较 SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN ( SELECT manager_id FROM employees WHERE employee_id IN ( 174, 141 ) ) AND department_id IN ( SELECT department_id FROM employees WHERE employee_id IN ( 174, 141 ) ) AND employee_id NOT IN ( 174, 141 );
(manager_id,department_id)这里为什么要给条件加上括号呢?
和后面的结果做匹配
-- 成对比较 SELECT employee_id, manager_id, department_id FROM employees WHERE ( manager_id, department_id ) IN ( SELECT manager_id, department_id FROM employees WHERE employee_id IN ( 174, 141 ) ) AND employee_id NOT IN ( 174, 141 );
HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
题目:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN( salary ) FROM employees GROUP BY department_id HAVING MIN( salary ) > ( SELECT MIN( salary ) FROM employees WHERE department_id = 50 );
CASE中的子查询
题目:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800 的department_id相同,则location为’Canada’,其余则为’USA’。
若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’
这题明显的CASE,也就是SWITCH形式的
1、根据location_id为1800的department_id先写出这个查询
2、然后CASE后面跟的条件就是department_id,拿CASE后面跟的条件去和子查询查出的条件相比即可
3、给它起个别名location
SELECT employee_id, last_name, ( CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id = 1800 ) THEN location = 'Canada' ELSE 'USA' END ) location FROM employees;
空值问题
意思就是子查询查出来的结果是空值,导致外面的查询对应的结果也是空值
SELECT last_name, job_id FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE last_name = 'Haas' );
非法使用子查询
Subquery returns more than 1 row