出现在其他语句中的SELETE语句,称为子查询或者内查询,外部的查询称为外查询或者主查询。
子查询按结果的行列数不同分为:
-
标量子查询(结果只有一个值,也就是一行一列)
-
列子查询(结果有多行一列)
-
行子查询(结果有一行多列)
-
表子查询(结果为多行多列)
按出现的位置分为:
-
SELECT后面,仅仅支持标量子查询
-
FROM后面,支持表子查询
-
WHERE或者HAVING后面,支持标量子查询、列子查询、行子查询
-
EXISTS后面(相关子查询),支持表子查询
SELECT后面的子查询
案例:
SELECT
d.*,(
SELECT
COUNT(*)
FROM
employees e
WHERE
e.department_id = d.department_id
)
FROM departments d
FROM后面的子查询
案例:查询部门平均工资列表和等级
SELECT
ag_dep.*,g.grade_level
FROM
(
SELECT
AVG(salary) ag, department_id
FROM
employees
GROUP BY
department_id
) ag_dep
INNER JOIN
job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
WHERE或者HAVING后面
标量子查询案例
SELECT
*
FROM
employee e
WHERE salary > (
SELECT
AVG(salary)
FROM
employees
)
列子查询案例
SELECT
*
FROM
employees
WHERE salary in (
SELECT
salary
FROM
employees
WHERE
last_name LIKE '%c%'
);
行子查询案例
SELECT
*
FROM
employees
WHERE
(employee_id,salary) = (
SELECT
MIN(employee_id),MAX(salary)
FROM
employees
)
exists子查询
只关心子查询有没有返回结果
例如:
SELECT
*
FROM
departments d
WHERE EXISTS (
SELECT * FROM employees e WHERE d.department_id = e.department_id
)