子查询

含义：

出现在其它语句中的select语句，称为子查询、内查询、嵌套查询

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

分类：

案子查询出现的位置：

• select后面      仅支持标量子查询

• from后面       支持表子查询

• ※where或having后面    标量子查询※、列子查询※、行子查询（较少）

• exists后面（相关子查询）

按结果集的行列数不同：

• 标量子查询（结果集只有一行一列）

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

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

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

一、where或having后面

1. 标量子查询（单行子查询）

2. 列子查询（多行子查询）

3. 行子查询（单行多列） 

特点：

1. 子查询放在小括号内

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

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

4. 子查询的执行优先于主查询执行，主查询的条件使用到了子查询的结果 

列子查询，一般搭配着多行操作符的使用

in、any/some、all 

1、标量子查询

#案例1：谁的工资比Abel高？ 
select last_name
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) 
from employees 
group by department_id
having min(salary) > ( 
    select min(salary) 
    from employees
    where department_id = 50 
);

2、列子查询

#案例1：返回location_id是1400或1700的部门中所有的员工姓名
#使用表连接
select location_id, last_name
from employees e 
join departments d 
on e.department_id = d.department_id 
where location_id in (1400, 1700); 
#使用子查询（in关键字） 
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（any关键字）
select employee_id, last_name, job_id, salary 
from employees 
where department_id != 'IT_PROG'
and salary < any (
    select distinct salary 
    from employees 
    where job_id = 'IT_PROG' 
);
   
#案例3：返回其它工种的，比job_id为‘IT_PROG’工种所有员工工资都低的员工的：工号、姓名、job_id以及salary（all关键字）
select employee_id, last_name, job_id, salary
from employees 
where department_id != 'IT_PROG' 
and salary < all ( 
    select distinct salary 
    from employees 
    where job_id = 'IT_PROG'
); 
 
#案例4：问题同案例1，但是要用any或all            in 和 =any 等效 ！！！       not in 和 != null等效 
select last_name 
from employees 
where department_id  = any ( 
    select distinct department_id
    from departments
    where location_id in (1400, 1700) 
);

3、行子查询 

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

二、select后面

#案例1：查询每个部门的员工个数 
select d.*, (
    select count(*) 
    from employees e 
    where e.department_id = d.department_id 
) 个数 
from departments d; 
 
#案例2：查询员工号=102的部门名 
select (
    select department_name
    from departments d
    inner join employees e 
    on d.department_id = e.department_id 
    where e.employee_id = 102 
) 部门名;

三、from后面

#案例1：查询每个部门的平均工资的工资等级 
select ag_dep.*, g.grade_level
from (
    select department_id, avg(salary) ag
    from employees 
    group by department_id 
) ag_dep 
inner join job_grades g 
on ag_dep.ag between lowest_sal and highest_sal;

四、exists后面（相关子查询）

语法：

exists（完整的查询语句）

结果：

1或0

select exists(select employee_id from employees);

#案例1：查询有员工的部门名 
select department_name 
from departments d
where exists(
    select *
    from employees e
    where d.department_id = e.department_id 
);
   
#案例2：查询没有女朋友的男神信息 
#in 
select bo.* 
from boys bo 
where bo.id not in( 
    select boyfriend_id 
    from beauty b
)  
 
select bo.* 
from boys bo 
where not exists(
    select * 
    from beauty b
    where b.boyfriend_id = bo.id
);
 
#Test1：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
 select employee_id, last_name, salary,e.department_id
 from (
     select avg(salary) ag, department_id 
    from employees 
    group by department_id 
) ag_dep 
inner join employees e 
on e.department_id = ag_dep.department_id 
where salary > ag;     
 
#Test2：查询工资最高德员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
 select max(salary), concat(first_name, '.', last_name)
 from employees;

作者：请保持优秀。

原文链接：https://blog.csdn.net/IT_TIfarmer/article/details/110389287