子查询
出现在其他语句中的SELECT语句,称为子查询或内查询
外部的查询语句称为主查询(外查询)
示例
#查询部门地址编号为1700的所有员工的名字
SELECT first_name FROM employee
WHERE department_id in (
SELECT department_id FROM departments
WHERE loaction_id=1700
)
按子查询出现位置分类
SELECT 后面
FROM 后面
WHERE 后面
HAVING 后面
EXISTS 后面
按子查询结果分类
标量子查询 (一行一列)
列查询(一列多行)
行查询(一行多列)
表子查询(多行多列)
标量子查询(WHERE或HAVING后)
#查询比小明工资高的所有员工的信息
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE neme='小明'
);
列子查询(WHERE或HAVING后)
多行比较操作符
操作符
含义
IN/NOT IN
等于列表中任意一个
ANY|SOME
和子查询返回的某一个值比较
ALL
和子查询返回的所有值比较
示例
#查询location_id是1400或1700的部门的所有员工名字
SELECT name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE loaction_id IN(1400,1700)
);
行子查询
#查询员工中编号最小且工资最高的员工信息
SELECT *
FROM employee
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
SELECT后的子查询
#查询部门信息及每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employee e
WHERE e.department_id=d.department_id
) 员工个数
FROM department d;
FROM后的子查询
#查询每个部门的平均工资的工资水平
SELECT agv_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) avg_dep
INNER JOIN job_grades g
ON avg_dep.ag BETWEEN lowest_sal AND highest_sal;
EXISTS后的子查询(相关子查询)
EXISTS(查询语句)
结果:1或0
#查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department
)