子查询
含义:出现在其他语句中的select语句,成为子查询或内查询
外部的查询语句,成为主查询或外查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一行多列)
表子查询(结果集一般为多行多列)
分类:
按子查询出现的位置:
1. select后面:仅仅支持标量子查询
eg:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) 个数
FROM departments d;
2.from后面:支持表子查询)
#将子查询结果充当一张表,要求必须起别名
eg:查询每个部门的平均工资的工资等级
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;
3.where或having后面:标量子查询(单行子查询),列子查询(多行子查询),行子查询
eg:返回job_id与141号员工相同,salary比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
);
eg:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=50
)
- 列子查询(多行子查询)
eg:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id in (
SELECT DISTINCT department_id
FROM departments
WHERE location_id in(1400,1700)
);
- 行子查询(结果为一行多列或多行多列)--用的较少
eg:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
4. exists后面(相关子查询):表子查询
语法:exists(完整的查询语句) 结果:1或0
eg:查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id=e.department_id
);