1. 定义
出现在其他语句中的 select 语句称为子查询或内查询,内部嵌套其他 select 语句的查询,称为外查询或主查询
2. 分类
按子查询出现的位置 | 按结果集的行列数不同 |
---|---|
select 后:支持标量子查询 | 标量子查询:结果集为一行一列 |
from 后:支持表子查询 | 列子查询:结果集为一列多行 |
where 或 having 后:支持标量子查询,列子查询,行子查询 | 行子查询:结果集为一行多列 |
exists 后:支持表子查询 | 表子查询:结果集一般为多行多列 |
3. 实例
3.1 where 或 having 后的标量子查询(单行子查询)
案例:查询工资比Abel高的员工信息
SELECT
`employee_id`,
`last_name`,
`salary`
FROM
`employees`
WHERE `salary` >
(SELECT
`salary`
FROM
`employees`
WHERE `last_name` = 'Abel')
案例:查询job_id与141号员工相同,且工资比143号员工多的员工的姓名,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)
案例:返回公司工资最少的员工的姓名,job_id和工资
SELECT
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE `salary` =
(SELECT
MIN(`salary`)
FROM
`employees`)
案例:查询最低工资大于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)
3.2 where 或 having 后的列子查询(多行子查询)
案例:查询location_id是1400或1700的部门中的所有员工姓名
SELECT
`last_name`
FROM
`employees`
WHERE `department_id` IN
(SELECT
`department_id`
FROM
`departments`
WHERE `location_id` IN (1400, 1700))
案例:查询其他工种中比job_id为IT_PROG任一工资低的员工信息
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE `salary` < SOME
(SELECT DISTINCT
`salary`
FROM
`employees`
WHERE `job_id` = "IT_PROG")
AND `job_id` != "IT_PROG"
案例:查询其他部门中比job_id为IT_PROG所有工资都低的员工信息
SELECT
`employee_id`,
`last_name`,
`job_id`,
`salary`
FROM
`employees`
WHERE `salary` < ALL
(SELECT DISTINCT
`salary`
FROM
`employees`
WHERE `job_id` = "IT_PROG")
AND `job_id` != "IT_PROG"
3.3 where 或 having 后的行子查询
案例:查询员工编号最小且工资最高的员工信息
SELECT
*
FROM
`employees`
WHERE (`employee_id`, `salary`) =
(SELECT
MIN(`employee_id`),
MAX(`salary`)
FROM
`employees`)
3.4 select 后的子查询
案例:查询每个部门的员工个数
SELECT
d.`department_id`,
d.`department_name`,
(SELECT
COUNT(*)
FROM
`employees` e
WHERE e.`department_id` = d.`department_id`) AS "部门人数"
FROM
`departments` d
案例:查询员工号为102的部门名
SELECT
(SELECT
`department_name`
FROM
`departments` d
INNER JOIN `employees` e
ON e.`department_id` = d.`department_id`
WHERE e.`employee_id` = 102)
3.5 from 后的子查询
案例:查询每个部门的平均工资的工资等级
SELECT
a.`avg`,
`grade_level`
FROM
(
(SELECT
`department_id`,
AVG(`salary`) AS `avg`
FROM
`employees`
GROUP BY `department_id`) AS a
INNER JOIN `job_grades`
ON a.`avg` BETWEEN `lowest_sal`
AND `highest_sal`
)
3.6 exists后的子查询(相关子查询)
案例:查询是否存在工资大于30000的员工
-- 0 只关注子查询的结果集是否为空
SELECT
EXISTS
(SELECT
`employee_id`
FROM
`employees`
WHERE `salary` > 30000)
案例:查询有员工的部门名
SELECT
`department_name`
FROM
`departments` AS d
WHERE EXISTS
(SELECT
`employee_id`
FROM
`employees` AS e
WHERE d.`department_id` = e.`department_id`)
SELECT
`department_name`
FROM
`departments`
WHERE `department_id` IN
(SELECT DISTINCT
`department_id`
FROM
`employees` );
案例:查询没有女朋友的男神信息
SELECT
*
FROM
`boys` AS bo
WHERE ! EXISTS
(SELECT
*
FROM
`beauty` AS b
WHERE b.`boyfriend_id` = bo.`id`)
总结
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询搭配单行操作符(条件运算符)使用,列子查询一般搭配多行操作符(in,some, all)使用
- 子查询的执行是优先于主查询的