10、子查询、exists

一、子查询

1、含义：出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句，称为主查询或外查询

2、分类：按子查询出现的位置

select后面

仅仅支持标量子查询

from后面

支持表子查询

where或having后面

标量子查询（单行）

列子查询（多行）

行子查询

特点：

子查询放在小括号内

子查询一般放在条件的右侧

标量子查询，一般搭配这单行操作符使用 > < = != <> >= <=

列子查询，一般搭配着多行操作符使用 in（等于列表的任意一个）、any/some（和子查询返回的某一个值比较）、all（和子查询返回的所有值比较 ）

子查询的执行优先与主查询执行，因为主查询的条件用到了子查询的结果

exists后面（相关子查询）

表子查询

按结果集的行列数不同

标量子查询（又称单行子查询，结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集有一行多列、多列多行）

表子查询（结果集一般为多行多列）

 

二、exists：判断查询结果是否有值

语法：exists(完整的查询语句)

与子查询运行规则相悖：外部sql先运行，内部sql后运行

如果查询语句存在值则返回1，否则返回0

 

三、案例

-- 一、放在where后面
-- 标量子查询
# 案例1：查询谁的工资比Abel高 (标量子查询)
select last_name,salary from employees where salary>(select salary from employees where last_name='Abel');

# 案例2：返回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');

# 案例3：返回公司工资最少的员工的last_name,job_id,salary
select last_name,job_id,salary from employees where salary=(select min(salary) from employees);

# 案例4：查询最低工资大于50号部门的最低工资部门id和其最低工资
select department_id,min(salary) as salary from employees group by department_id having salary>(select min(salary) from employees where department_id='50');

-- 列子查询
# 案例1：返回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'));

# 案例2：返回其他部门中比job_id为‘it_prog’部门任意工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary from employees where salary<any(select distinct salary from employees where job_id='IT_PROG') and job_id<>'IT_PROG';

# 案例3：返回其他部门中比job_id为‘it_prog’部门所有工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary from employees where salary<all(select distinct salary from employees where job_id='IT_PROG') and job_id<>'IT_PROG';

-- 行子查询
# 案例1：查询员工编号最小并且工资最高的员工信息
select * from employees where (employee_id,salary)=(select min(employee_id),max(salary) from employees);
select * from employees where employee_id=(select min(employee_id) from employees where salary=(select max(salary) from employees));

-- 二、放在select后面（只支持标量子查询）
# 案例1：查询每个部门的员工个数
select department_id,(select count(*) from employees s where s.department_id=d.department_id) from departments d;
# 案例2：查询员工号=102的部门名
select (select department_name from departments d,employees s where d.department_id=s.department_id and employee_id='102') as 部门名;

-- 三、from后面
# 案例1：查询每个部门的平均工资的工资等级
select department_id,salary,grade_level
from job_grades j inner join (select department_id,(salary) as salary from employees group by department_id) g where salary BETWEEN lowest_sal and highest_sal;

-- 四、exists后面（相关子查询）
# 案例1：查询有员工的部门名
select department_name from departments d where exists (select * from employees s where d.department_id=s.department_id);