目录
什么是子查询?
子查询指的是一个查询语句嵌套在另一个查询语句内部的查询
一.需求分析与问题解决
很多时候查询需要从结果集中获取数据, 或者需要从一个表中先计算得出一个结果, 然后与这个数据结果(可能是某个标量, 也可能是某个集合)进行比较
1.1实际问题
例题1: 谁的工资比名字为" Abel" 高
1.2子查询的基本使用
SELECT last_name
FROM employees
WHERE salary > (
SELECT salary
FROM employees e
WHERE last_name = 'Abel'
);
其中括号内的称为子查询也叫(内查询)
括号外称为主查询也叫(外查询)
须知:
子查询在主查询之前一次执行完成
子查询的结果被主查询使用
1.3子查询的分类
单行子查询就是内查询返回单行值供外查询使用
多行子查询就是内查询返回多行值供外查询使用
二.单行子查询
子查询编写技巧:
1.从内往外写
2.从外往内写
2.1单行比较操作符
| = | <>(不等于) |
|---|---|
| > | >= |
| < | <= |
2.2代码示例
注意:
1.子查询要包含在括号内
2.将子查询放在比较条件的右侧
3.单行操作符对应单行子查询, 多行操作符对应多行子查询
例题2: 查询工资大于149号员工工资的员工信息
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees e
WHERE employee_id = 149
);
例题3: 返回job_id与141号员工相同, salary比143号员工多的员工姓名, job_id和工资
SELECT last_name, job_id, salary
FROM employees e
WHERE job_id = (
SELECT job_id
FROM employees e
WHERE employee_id = 141
)
AND salary > (
SELECT salary
FROM employees e
WHERE employee_id = 143
);
例题4: 返回公司工资最少的员工的last_name, job_id和salary
SELECT last_name, job_id, salary
FROM employees e
WHERE salary = (
SELECT MIN(salary)
FROM employees e
);
2.3HAVING中的子查询
例题5: 查询最低工资大于50号部门最低工资的部门id和最低工资
SELECT department_id, MIN(salary)
FROM employees e
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees e
WHERE department_id = 50
);
2.4CASE中的子查询
例题6: 显示员工的employee_id, last_name和location, 其中department_id与location_id为1800的department_id相同, 则location为’Canada’, 其余为’USA’
SELECT employee_id, last_name, CASE WHEN department_id = (SELECT department_id
FROM departments d
WHERE location_id = 1800)
THEN "Canada"
ELSE "USA" END AS 'location'
FROM employees e
2.5子查询中空值的情况
如果内查询的返回结果为空或者外查询与内查询比较完结果为空则返回空值
2.6非法使用子查询
用单行子查询语句去查询多行子查询 为非法使用子查询
三.多行子查询
3.1多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个 |
| ANY | 需要和单行比较操作符一起使用, 和子查询返回的某一个值比较 |
| ALL | 需要和单行比较操作符一起使用, 和子查询返回的所有值比较 |
| SOME | 实际上是ANY的别名, 作用相同, 一般常使用ANY |
3.2代码示例
例题7: 返回其他job_id中比job_id为’IT_PROG’部门任一工资低的员工的员工号,姓名,job_id,和salary
SELECT employee_id, last_name, job_id, salary
FROM employees e
WHERE salary < ANY (
SELECT salary
FROM employees e
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG'
例题8: 查询平均工资最低的部门id
方式一:
SELECT department_id
FROM employees e
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN(abc)
FROM (SELECT department_id, AVG(salary) AS abc
FROM employees e
GROUP BY department_id)t_avg
)
方式二:
SELECT department_id
FROM employees e
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary)
FROM employees e
GROUP BY department_id
)
四.相关子查询
4.1相关子查询的执行流程
如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表, 并进行了条件关联, 因此每执行一次外部连接, 子查询都到重新计算一次, 这样的子查询就称为关联子查询
在SELECT中, 除了GROUP BY 和 LIMIT之外, 其他位置都可以声明子查询!
\quad
4.2代码示例
例题9: 查询员工中工资大于本部门平均工资的last_name, salary和其他department_id
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e.department_id
)
方法二:
SELECT e1.last_name, e1.salary, e1.department_id
FROM employees e1, (SELECT department_id, AVG(salary) AS s_avg
FROM employees e
GROUP BY department_id)t_davg
WHERE e1.department_id = t_davg.department_id
AND e1.salary > t_davg.s_avg
例题10: 查询员工的id, salary, 按照department_name排序
SELECT employee_id, salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
)ASC ;
例题11: 若employees表中employee_id与job_history表中employee_id相同的数目不小于2,
则输出这些相同id的员工的employee_id, last_name和其他job_id
SELECT employee_id, last_name, job_id
FROM employees e
WHERE 2 <= (
SELECT count(*)
FROM job_history jh
WHERE e.employee_id = jh.employee_id
)
例题12: 查询公司管理者的employee_id, last_name, job_id, department_id信息
SELECT DISTINCT e.employee_id, e.last_name, e.job_id, e.department_id
FROM employees e JOIN employees e1
ON e1.manager_id = e.employee_id
4.3EXITS与NOT EXITS关键字
关联子查询通常也会和EXSTS操作符一起来使用, 用来检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回TRUE
如果在子查询中不存在满足条件的行:
条件返回FALSE
继续在子查询中查找
NOT EXITS关键字表示如果不存在某种条件, 则返回TRUE, 否则返回FALSE
\quad
\quad
例题12: 查询公司管理者的employee_id, last_name, job_id, department_id信息
用EXITS关键字来做
SELECT e.employee_id, e.last_name, e.job_id, e.department_id
FROM employees e
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e.employee_id = e2.manager_id
)
#其中*只是做记录, 用什么标记不重要
例题13: 查询department表中, 不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.department_id = d.department_id
)
\quad
\quad
五.自连接与子查询哪个好?
由上面的例题1: 谁的工资比名字为" Abel" 高
#自连接
SELECT e1.last_name, e1.salary
FROM employees e1, employees e2
WHERE e2.last_name = 'Abel'
AND e1.salary > e2.salary
#子查询
SELECT last_name
FROM employees
WHERE salary > (
SELECT salary
FROM employees e
WHERE last_name = 'Abel'
);
自连接方式好!!
题目中可以使用子查询, 也可以使用自连接, 一般情况下建议使用自连接, 因为在许多DBMS的处理过程中, 对于自连接的处理速度要比子查询快得多
可以这样理解: 子查询实际上是通过未知表进行查询后的条件判断, 而自连接是通过已知的自身数据表进行条件判断, 因此在大部分DBMS中都对自连接进行了优化