子查询不走索引 子查询放在select后

文章目录

• 子查询（嵌套查询）

• 标准子查询
• 列子查询（多行子查询）
• 行子查询
• select后面的子查询
• from后面的子查询
• exists后面（相关子查询）

子查询（嵌套查询）

出现在其他语句中的select语句被称为子查询

按位置分类：

• select后面
• from后面
• where或having后面
• exists后面

按结果级的行数不同分类：

• 标量子查询 (结果集有一行一列)
• 列子查询 (结果集只有一列多行)
• 行子查询 (结果集只有一行多列)
• 表子查询 (结果集一般为多行多列)

where或having后面
标量子查询
列子查询
行子查询
特点：

1. 子查询都放在小括号内，结尾不用加 ;
2. 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3. 子查询优先于主查询执行，主查询使用了子查询的执行结果

子查询根据查询结果的行数不同分为以下两类：单行子查询和多行子查询

1. 单行子查询
   结果集只有一行
   一般搭配单行操作符使用：> < = <> >= <=
   非法使用子查询的情况：

1. 子查询的结果为一组值
2. 子查询的结果为空

2. 多行子查询
   结果集有多行
   一般搭配多行操作符使用：any、all、in、not in
   in：属于子查询结果中的任意一个就行
   any和all:往往可以用其他查询代替

标准子查询

1. 谁的工资比abel高?
   1查询Abel的工资
   SELECT salaryFROM employeesWHERE last_name = 'Abel'2查询员工的信息，满足 salary>1结果
   SELECT *FROM employeesWHERE salary>( SELECT salary FROM employees WHERE last_name = 'Abel'); 把1的结果放在2的小括号内,括号内就是子查询。

自查行中用到分组函数。
2. 返回公司工资最少的员工的last_name,job_id和salary

1. 查询公司的 最低工资
   SELECT MIN(salary)FROM employees
2. 查询last_name,job_id和salary，要求 salary=1
   SELECT last_name,job_id,salary FROM employees WHERE salary= ( SELECT MIN(salary) FROM employees );

一个查询里面可以放两个子查询。
3. 返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资

SELECT last_name,job_id,salaryFROM employeesWHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141) 查询141号员工的job_id
AND salary>( SELECT salary FROM employees WHERE employee_id = 143);查询143号员工的salary

4. 查询最低工资大于50号部门最低工资的部门id和其最低工资

1. 查询50号部门的最低工资
   SELECT MIN(salary)FROM employeesWHERE department_id = 50
2. 查询每个部门的最低工资
   SELECT MIN(salary),department_idFROM employeesGROUP BY department_id
3. 在②基础上筛选，满足min(salary)>①
   SELECT MIN(salary),department_idFROM employeesGROUP BY department_idHAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id = 50);

列子查询（多行子查询）

• 返回多行
• 使用多行比较操作符

操作符	含义
in/out in	等于列表中的任意一个
not in	不在指定的集合范围内
any/some	和子查询返回的某个值比较，有任意一个满足即可
all	和子查询返回的所有值比较，必须全部满足

1. 案例: 查询 “销售部” 和 “市场部” 的所有员工信息(in)

分解为以下两步:

①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';

②. 根据部门ID, 查询员工信息
select * from emp 
where dept_id in 
(select id from dept where name = '销售部' or name = '市场部');

2. 查询比 财务部 所有人工资都高的员工信息（all）

# ①. 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

# ②. 比 财务部 所有人工资都高的员工信息
select * from emp 
where salary > all 
( select salary from emp where dept_id =(select id from dept where name = '财务部') );

3. 查询比研发部其中任意一人(比最小值高)工资高的员工信息(any)
   分解为以下两步:

①. 查询研发部所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');

②. 比研发部其中任意一人工资高的员工信息
select * from emp 
where salary > any 
( select salary from emp where dept_id =(select id from dept where name = '研发部') );

3. 返回location_id是1400或1700的部门中的所有员工姓名

1. 查询location_id是1400或1700的部门编号
   SELECT distinct department_idFROM departmentsWHERE location_id IN(1400,1700)
2. 查询员工姓名，要求部门号是①列表中的某一个
   SELECT last_nameFROM employeesWHERE department_id in( SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));

4. 返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

1. 查询job_id为‘IT_PROG’部门任一工资
   SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG'
2. 查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
   SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary < ANY( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 或(< any等价于< min)
   SELECT last_name,employee_id,job_id,salaryFROM employeesWHERE salary < ( SELECT min(salary) FROM employees WHERE job_id = 'IT_PROG') AND job_id<>'IT_PROG';

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:

①. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';

②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp 
where (salary,managerid) = (select salary, managerid from empwhere name = '张无忌');

查询员工编号最小并且工资最高的员工信息

1. 查询最小的员工编号
   SELECT MIN(employee_id)FROM employees
2. 查询最高工资
   SELECT MAX(salary)FROM employees
3. 查询员工信息
   SELECT *FROM employeesWHERE employee_id=( SELECT MIN(employee_id) FROM employees)AND salary=( SELECT MAX(salary) FROM employees);

常规查询分为以上三步，行查询可转化为：
SELECT * FROM employeesWHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees);

select后面的子查询

• 仅仅支持标量子查询

1. 查询员工号=102的部门名
   SELECT( SELECT department_name,e.department_id FROM departments d INNER JOIN employees e ON d.department_id=e.department_id WHERE e.employee_id=102) as 部门名;

from后面的子查询

• 将子查询结果充当一张表，要求必须起别名

1. 查询每个部门的平均工资的工资等级

1. 查询每个部门的平均工资
   SELECT AVG(salary),department_idFROM employeesGROUP BY department_id 然后再
   SELECT * FROM job_grades; 显示查询结果
2. 连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_salSELECT ag_dep.*,g.'grade_level'FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) as ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

语法：
exists(完整的查询语句)
结果：1或0

SELECT EXISTS(SELECT employee_id FROM employees); 结果为0。只关心他括号里的子查询有没有，有就为1

1. 查询有员工的部门名

• 用in
  SELECT department_nameFROM departments dWHERE d.'department_id' IN( SELECT department_id FROM employees);
• 用exists
  SELECT department_nameFROM departments dWHERE EXISTS( SELECT * FROM employees e WHERE d.'department_id'=e.'department_id');