DQL子查询介绍
按结果集行列数不同:
- 标量子查询(结果为一列一行)
- 列子查询(结果为一列多行)
- 行子查询(结果为一行多列)
- 表子查询(结果为多行多列)
按子查询位置不同:
- select后面(标量子查询)
- from后面(表子查询)
- where或者having后面(标量子查询、列子查询、行子查询)
- exists后面(表子查询)
子查询放在小括号内部
子查询一般放在条件的右侧
标量子查询,一般配合单行操作符使用 > < >= <= = <>
列子查询,一般搭配多行子查询使用IN ANY/SOME ALL
放在where和having后面
标量子查询
# 第一步我们要查询Abel的工资
SELECT *
FROM employees
WHERE last_name = 'Abel'
# 第二步在第一部基础上查询
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
PS:注意标量子查询结果只能是一列一行的
列子查询
- IN:等于列表中任意一个
- ANY/SOME 和子查询返回值某一值比较(a > ANY(10,20,30) <==> a> MIN(10,20,30))
- ALL 和子查询返回的所有值比较(a > ANY(10,20,30) <==> a> MAX(10,20,30))
# 查询location_id 为1400或者1700的部门
SELECT *
FROM departments
WHERE location_id IN(1400,1700)
# 查询location_id为1400或者1700员工
SELECT *
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id IN (1400,1700)
)
行子查询(了解)
非行子查询写法
# 查询员工编号最小的人
SELECT MIN(employee_id)
FROM employees
# 查询员工工资最高的人
SELECT MAX(salary)
FROM employees
# 查询同时符合这两个条件的人
SELECT *
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
)
AND employee_id = (
SELECT MIN(employee_id)
FROM employees
)
行子查询写法
SELECT *
FROM employees
WHERE (employee_id,salary) = (
SELECT MIN(employee_id),MAX(salary)
FROM employees
)
放在select后面
标量子查询
SELECT departments.*,(
SELECT COUNT(*)
FROM employees
WHERE departments.department_id = employees.department_id
) 个数
FROM departments
放在from后面
SELECT *
FROM (
SELECT MIN(salary) 最低工资,department_id FROM employees GROUP BY department_id
) AS 表1,job_grades AS 表2
WHERE 表1.最低工资 BETWEEN 表2.lowest_sal AND highest_sal
放在exists后面
相关子查询
SELECT EXISTS(SELECT * FROM employees)
SELECT EXISTS(SELECT * FROM employees WHERE salary = 30000)