目录
一、介绍
出现在其他语句中的select语句,称为子查询或内查询。外部的查询语句,称为主查询或外查询。简单理解就是嵌套查询,select语句里还有select语句。
应用场景:当一条查询语句已经没法解决需求时,需要使用到另一条或者多条结果集,这便是子查询。
例如:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资
需要满足job_id=141,并且salary>143号员工。也就需要进行多条查询结果:
①查询141号员工的job_id
②查询143号员工的salary
③查询员工的姓名,job_id 和工资,要求job_id=①并且salary>②
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
);
二、分类
按照结果集的行列数不同分类:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
按照子查询出现的位置:
select后面:
仅仅支持标量子查询
from后面:
支持表子查询
where或having后面:
标量子查询(单行)
列子查询 (多行)
行子查询
exists后面(相关子查询)
表子查询
1、标量子查询
又称单行子查询,结果集只有一行一列,一般搭配单行操作符使用:> < = <> >= <=
基本语法:
select *
from 数据源
where 条件判断 =/<>
(select 字段名 from 数据源 where 条件判断); //子查询得到的结果只有一个值
案例:谁的工资比 Abel 高?
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
2、列子查询
又称多行子查询,结果集只有一列多行,一般搭配多行操作符使用:any、all、in、not in
in指的是 属于子查询结果中的任意一个就行, any和all往往可以用其他查询代替。
基本语法:
SELECT 查询列表
FROM 数据源
WHERE 条件判断 操作符(
结果集
);
案例:返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id <>ALL(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
3、行子查询
结果集有一行多列或多行多列。行子查询涉及到一个行元素的名词概念行元素,字段元素是指一个字段对应的值,行元素对应的就是多个字段,多个字段合起来作为一个元素参与运算,把这种情况称之为行元素。
基本语法:
SELECT 查询列表
FROM 数据源
WHERE (行元素)=(
结果集
);
案例:查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
4、表子查询
结果集一般为多行多列,将子查询结果充当一张表使用,要求必须起别名。表子查询与行子查询比较相似,返回的结果集都为多行多列,区别在于表子查询没有行元素。并且表子查询一般是在from后面使用。
基本语法:
Select 字段表
from (表子查询)
as 别名
where
group by
having
order by
limit;
案例:查询每个部门的平均工资的工资等级
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;
5、Exists子查询
Exists子查询:查询返回的结果只有0或者1,1代表成立,0代表不成立
基本语法:where exists(完整的查询语句) 结果:1或0;
exists就是根据查询得到的结果进行判断:
如果结果存在,返回1,否则返回0。
Where 1:永远为真
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);