子查询
含义:
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或者外查询
分类:
按子查询出现的位置:
select后面:
仅仅支持标量查询
from后面:
支持子查询
where或having后面: **
标量子查询 (单行)**
列子查询 (多行)**
行之查询(用少)
exists后面(相关子查询):
表子查询
按结果集的列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一行多列)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
一,where 或 having后的
标量子查询 (单行)**
列子查询 (多行)**
行子查询
特点:
1,子查询放在小括号内
2,子查询一般放条件的右侧
3,标量子查询,一般搭配着单行操作符使用
> < >= <= = <>
4.子查询优先于主查询
列子查询,一般搭配多行操作符使用
in any some all
1.标量子查询
案例1:谁的工资比abel高?
SELECT *
FROM employees e
WHERE salary > (
SELECT salary
FROM employees m
WHERE last_name = 'abel');
案例2:返回job——id与141号员工相同,salary比143号员工多的员工姓名,job_id,工资
SELECT last_name,salary,job_id
FROM employees e
WHERE job_id =(
SELECT job_id
FROM employees m
WHERE employee_id =141
)
AND salary > (
SELECT salary
FROM employees p
WHERE employee_id =143
);
案例3:返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees e
WHERE salary = (
SELECT MIN(salary)
FROM employees m
);
案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees e
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees m
WHERE department_id = 50);
2.列子查询(多行子查询)
in/not in 等于表中任意一个
any/some 和子查询返回的某一个值比较
all 和查询返回的所有值比较
案例1:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees e
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments d
WHERE location_id IN (1400,1700)
);
案例2:返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号,姓名,job_id,salary
SELECT employee_id,last_name,job_id,salary
FROM employees e
WHERE salary < ANY(
SELECT DISTINCT salary
FROM employees m
WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG';
案例3:返回其他部门中比job_id为‘IT_PROG’部门所有工资低的员工的员工号,姓名,job_id,sal
SELECT employee_id,last_name,job_id,salary
FROM employees e
WHERE salary < ALL(
SELECT DISTINCT salary
FROM employees m
WHERE job_id = 'IT_PROG'
)AND job_id<>'IT_PROG';
3,行子查询 (一行多列)
案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees e
WHERE employee_id = (
SELECT MIN(employee_id)
FROM employees
)
AND salary = (
SELECT MAX(salary)
FROM employees
);
SELECT *
FROM employees e
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
二,select后面
仅仅只支持标量查询
案例:查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.department_id
) 个数
FROM departments d;
三,from后面
将子查询结果充当一张表,要求必须取别名
案例:查询每个部门的平均工资的工资等级
SELECT ag.*,j.`grade_level`
FROM
(
SELECT AVG(salary) ags
FROM employees
GROUP BY department_id
) ag
JOIN job_grades j
ON ag.ags BETWEEN j.`lowest_sal` AND j.`highest_sal`;
四,exists 后面(相关子查询)
语法:
exists (完整的查询语句)
结果 1/0
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
案例:查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);