子查询

进阶7：子查询

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

分类：

1、按子查询出现的位置：
（1）select后面：仅仅支持标量子查询
（2）from后面：支持表子查询
（3）where或having后面：支持标量子查询、列子查询、行子查询（用得较小）
（4）exists后面（相关子查询）：支持表子查询

2、按结果集的行列数不同：
（1）标量子查询（结果集只有一行一列）
（2）列子查询（结果集只有一列多行）
（3）行子查询（结果集有一行多列）
（4）表子查询（结果集一般为多行多列）

一、where 或 having 后面

特点：

（1）子查询放在小括号内
（2）子查询一般放在条件后面
（3）标量子查询，一般搭配着单行操作符使用（>   <   >=   <=   =   <>)
     列子查询，一般搭配着多行操作符使用（in  any/some   all)
（4）子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

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

案例1：谁的工资比 Abel 高

（1）先查询Abel的工资

SELECT 
  salary 
FROM
  employees 
WHERE last_name = 'Abel' ;
#结果是11000.00

（2）查询员工的信息，满足salary>（1）的结果

SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;

结果：

案例2：返回job_id与141号员工相同，salary比143号员工多的员工job_id,姓名，job_id 和工资
（1）查询141号员工的job_id

SELECT 
  job_id 
FROM
  employees 
WHERE employee_id = 141 ;
#结果是ST_CLERK

（2）查询143号员工的salary

SELECT 
  salary 
FROM
  employees 
WHERE employee_id = 143 ;
#结果是2600.00

（3）查询员工的姓名、job_id和工资，要求job_id=(1)并且salary》（2）

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
（1）查询公司的最低工资

SELECT 
  MIN(salary) 
FROM
  employees ;
#结果是2100

（2）查询员工的last_name,job_id和salary，要求salary=（1）

SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

结果：

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
（1）查询50 号部门的最低工资

SELECT 
  MIN(salary) 
FROM
  employees 
WHERE department_id = 50 ;
#结果是2100

（2）查询每个部门的最低工资

SELECT 
  MIN(salary),
  department_id 
FROM
  employees 
GROUP BY department_id ;

结果：

（3）在（2）基础上筛选，满足min（salary）>（1）

SELECT 
  MIN(salary),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING MIN(salary) > 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE department_id = 50) ;

结果：

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

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

操作符	含义
in / not in	等于列表中的任意一个，in(查询语句)等价 =any，not in 等价 <>all
any / some	和子查询返回的某一个值比较，如：a>any(查询语句)==a>min(查询语句)，any小于最大值大于最小值
all	和子查询返回的所有值比较，all小于最小值大于最大值

案例1：返回location_id是1400或1700的部门中的所有员工姓名
(1)查询location_id是1400或1700的部门部门编号（如果有重复的，需要去重distinct）

SELECT 
  department_id 
FROM
  departments 
WHERE location_id IN (1400, 1700) ;#结果是单列多行

（2）查询员工姓名，要求部门号是（1）列表中的某一个

SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;

结果：

案例2：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
（1）查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT 
  salary 
FROM
  employees 
WHERE job_id = 'IT_PROG' ;

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

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id<>'IT_PROG';

#或
SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MAX(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

结果：

返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL 
  (SELECT DISTINCT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;
  
#或
SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

结果：

3、行子查询（结果集一行多列或多行多列，用的较少）

案例：查询员工编号最小并且工资最高的员工信息
(1)查询最小的员工编号

SELECT 
  MIN(employee_id) 
FROM
  employees ;

（2）查询最高工资

SELECT 
  MAX(salary) 
FROM
  employees ;

（3）查询员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id = 
  (SELECT 
    MIN(employee_id) 
  FROM
    employees) 
  AND salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;

结果：

方法二：行子查询

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 
  AVG(salary),
  department_id 
FROM
  employees 
GROUP BY department_id ;

(2)连接（1）的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

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 ;

结果：

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

语法：EXISTS(完整的查询语句)
结果：1或0
案例1：查询有员工的部门名
in：

SELECT 
  department_name 
FROM
  departments 
WHERE department_id IN 
  (SELECT 
    department_id 
  FROM
    employees) ;

exists：

SELECT 
  department_name 
FROM
  departments d
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees e 
  WHERE d.department_id = e.`department_id`) ;

结果：

案例2：查询没有女朋友的男神信息

SELECT 
  bo.* 
FROM
  boys 
WHERE bo.id NOT IN 
  (SELECT 
    boyfriend_id 
  FROM
    beauty) ;

#exists
SELECT 
  bo.* 
FROM
  boys bo 
WHERE NOT EXISTS 
  (SELECT 
    * 
  FROM
    beauty b 
  WHERE b.`boyfriend_id` = bo.`id`) ;