一、含义
嵌套在其他语句内部的select语句称为子查询或内查询,外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多,外面如果为select语句,则此语句称为外查询或主查询。
二、分类
2.1、按出现位置
select后面:仅仅支持标量子查询
from后面: 表子查询
where或having后面: 标量子查询、列子查询、行子查询
exists后面:标量子查询、列子查询、行子查询、表子查询
2.2、按结果集的行列
标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
三、示例
3.1、where或having后面
案例1:谁的工资比 Abel 高
SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name = 'Abel');
案例2:查询最低工资大于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 );
3.2、select后面
案例:查询每个部门的员工个数
SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id` ) 个数 FROM departments d;
3.3、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;
3.4、exists后面
语法:exists(完整的查询语句)
结果:1或0
案例1:查询有员工的部门名
SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id`);
相当于
SELECT department_name FROM departments d WHERE d.`department_id` IN( SELECT department_id FROM employees )
关注公众号:Java后端生活,干货文章第一时间送达!