在一个查询结果中嵌入的另一个查询

需求：谁的工资比Abel高？

方式1：

  1. SELECT salary
  2. FROM employees
  3. WHERE last_name = 'Abel'
  5. SELECT last_name,salary
  6. FROM employees
  7. WHERE salary > 11000;

方式2：自连接

  1. SELECT e2.last_name,e2.salary
  2. FROM employees e1 INNER JOIN employees e2
  3. ON e1.employee_id = e2.employee_id
  4. WHERE e2.salary > e1.salary AND e1.last_name = 'Abel';

方式3：子查询

  1. SELECT last_name,salary
  2. FROM employees
  3. WHERE salary > (
  4.                 SELECT salary
  5.               FROM employees
  6.               WHERE last_name = 'Abel'
  7.         );

子查询的使用

  • 称谓的规范：外查询（主查询）、内查询（子查询）

  • 子查询在主查询之前一次执行完成

  • 子查询的结果被主查询使用
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

  • 角度1：单行子查询 vs 多行子查询

    • 从内查询返回的结果的条目数

  • 角度2：相关子查询 vs 非相关子查询

    • 内查询是否被执行多次
    • 比如：相关子查询的需求：查询工资大于本部门平均工资的员工信息
    • 不相关子查询的需求：查询工资大于本公司平均工资的员工信息

单行子查询

  • 子查询编写技巧（或步骤）
    • 从里往外写
    • 从外往里写

子查询

查询工资大于149号员工工资的员工的信息

  1. SELECT employee_id,employee_name,salary
  2. FROM employees
  3. WHERE salary > (
  4.             SELECT salary
  5.           FROM employees
  6.           WHERE employee_id = 149
  7.     )

返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

  1. SELECT employee_name,job_id,salary
  2. FROM employees
  3. WHERE salary > (
  4.     SELECT salary
  5.     FROM employees
  6.     WHERE employee_id = 143
  7. ) AND
  8. job_id = (
  9.     SELECT job_id
  10.     FROM employees
  11.     WHERE job_id = 141
  12. )

返回公司工资最少的员工的last_name，job_id和salary

  1. SELECT last_name,job_id,salary
  2. FROM employees
  3. WHERE salary = (
  4.         SELECT MIN(salary)
  5.       FROM employees
  6. )

查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id

  1. SELECT employee_id,manager_id,department_id
  2. FROM employees
  3. WHERE manager_id = (
  4.         SELECT manager_id
  5.       FROM employees
  6.       WHERE employee_id = 141
  7. )
  8. AND department_id = (
  9.         SELECT department_id
  10.       FROM employees
  11.       WHERE department_id = 141
  12. )
  13. AND employee_id <> 141;

HAVING中的子查询

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

  1. SELECT department_id,MIN(salary)
  2. FROM employees
  3. WHERE department_id IS NOT NULL
  4. GROUP BY department_id
  5. HAVING MIN(salary) > (
  6.         SELECT MIN(salary)
  7.       FROM employees
  8.       WHERE deparment_id = 50
  9. )

CASE中的子查询

显式员工的employee_id，last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为”Canada”，其余为’USA’

  1. SELECT employee_id,last_name,
  2.                 (CASE department_id 
  3.          WHEN
  4.                      (SELECT department_id    FROM departments    
  5.                WHERE location_id = 1800) 
  6.          THEN 'Canada' ELSE 'USA' END) "location"
  7. FROM employees
  9. SELECT department_id
  10. FROM departments
  11. WHERE location_id = 1800

子查询中的空值问题

  1. SELECT last_name,job_id
  2. FROM employees
  3. WHERE job_id = 
  4.                     (SELECT job_id
  5.            FROM employees
  6.            WHERE last_name = 'Haas'  //没有haas这个人
  7.           );
  • 若是空值，则子查询不返回任何行

非法使用子查询

  • 应该使用多行子查询操作符（IN）
    1. SELECT employee_id,last_name
    2. FROM employees
    3. WHERE salary = (   //要求的是单行数据
    4.       SELECT MIN(salary)            //里面有多行数据
    5.     FROM employees
    6.     GROUP BY department_id
    7. );

多行子查询

  • 集合标记子查询
  • 内查询返回多行
  • 使用多行比较操作符

多行比较操作符

  • IN：等于列表中的任意一个
  • ANY：和子查询返回的某一个值比较
  • ALL：和子查询返回的所有值比较

  • SOME：是ANY的别名，作用相同

  • IN

    1. SELECT employee_id,last_name
    2. FROM employees
    3. WHERE salary IN (
    4.           SELECT MIN(salary)
    5.         FROM employees
    6.         GROUP BY department_id;
    7. )

  • ANY / ALL

返回其他job_id中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id及salary

  1. SELECT employee_id,last_name,job_id,salary
  2. FROM employees
  3. WHERE job_id <> 'IT_PROG'
  4. AND salary < ANY(
  5.             SELECT salary
  6.       FROM employees
  7.       WHERE job_id = 'IT_PROG'
  8.     );
  11. SELECT salary
  12. FROM employees
  13. WHERE job_id = 'IT_PROG'

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

  1. SELECT employee_id,last_name,job_id,salary
  2. FROM employees
  3. WHERE job_id <> 'IT_PROG'
  4. AND salary < ALL(
  5.             SELECT salary
  6.       FROM employees
  7.       WHERE job_id = 'IT_PROG'
  8.     );

查询平均工资最低的部门id

  • MySQL中聚合函数是不能嵌套使用的

方式1：

  1. SELECT MIN(AVG(salary)) //错误的
  2. FROM employees
  3. GROUP BY department_id
  1. SELECT MIN(avg_sal)
  2. FROM (
  3.             SELECT AVG(salary) avg_sal
  4.       FROM employees
  5.       GROUP BY department_id
  6.     ) t_dept_avg_sal
  1. SELECT department_id
  2. FROM employees
  3. GROUP BY department_id
  4. HAVING AVG(salary) = (
  5.         SELECT MIN(avg_sal)
  6.           FROM (
  7.             SELECT AVG(salary) avg_sal
  8.             FROM employees
  9.             GROUP BY department_id
  10.         ) t_dept_avg_sal
  11. );

方式2：

  1. SELECT department_id
  2. FROM employees
  3. GROUP BY department_id
  4. HAVING AVG(salary) <= ALL(
  5.             SELECT AVG(salary) avg_sal
  6.             FROM employees
  7.             GROUP BY department_id
  8.         ) 
  9. );
  • 空值问题

查询不是管理者的其他人的名字

  1. SELECT last_name  
  2. FROM employees
  3. WHERE employee_id NOT IN (
  4.                 SELECT manager_id   //内查询里有Null值
  5.               FROM employees
  6.               WHERE manager_id IS NOT NULL
  7. );

相关子查询

  • 如果子查询的执行依赖外部查询，是因为子查询中的表用到了外部的表，进行了条件关联，每次执行一次外部查询，子查询都要重新计算一次。

查询员工中种子大于本公司平均工资的员工的last_name，salary和其department_id

  1. SELECT last_name,salary,department_id
  2. FROM employees
  3. WHERE salary > (
  4.             SELECT AVG(salary)    
  5.             FROM employees
  6. )
  • 查询员工中种子大于本部门平均工资的员工的last_name，salary和其department_id

方式1：使用相关子查询

  1. SELECT last_name,salary,department_id
  2. FROM employees e1
  3. WHERE salary > (
  4.             SELECT AVG(salary)    
  5.             FROM employees e2
  6.           WHERE department_id = e1.department_id
  7. )

方式2：在FROM中声明子查询

  1. SELECT e.last_name,e.salary,e.department_id
  2. FROM employees e,(
  3.                 select department_id,AVG(salary) avg_sal
  4.               from employees
  5.               GROUP BY department_id) t_dept_avg_sal
  6. WHERE e.department_id = t_dept_avg_sal.department_id
  7. AND e.salary > t_dept_avg_sal.avg_sal

  • 查询员工的id，salary，按照department_name排序

    1. SELECT employee_id,salary
    2. FROM employees e 
    3. ORDER BY (
    4.           SELECT department_name
    5.         FROM deprtments d
    6.         WHERE e.department_id = d.department_id
    7.       ) ASC;

  • 结论：

    • 在SELECT 中，除了GROUP BY和 LIMIT之外，其他位置都可以声明子查询

若employees表中 employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id，last_name和其job_id

  1. SELECT employee_id,last_name,job_id
  2. FROM employees e
  3. WHERE 2 <= (
  4.             SELECT COUNT(*)
  5.           FROM job_history j
  6.           WHERE e.employee_id = j.employee_id
  7. )

EXISTS 与 NOT EXISTS关键字

查询公司管理者的employee_id，last_name，job_id，department_id信息
方式1：自连接

  1. SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
  2. FROM employees emp LEFT JOIN employees mgr
  3. ON emp.manager_id = mgr.employee_id;

方式2：子查询

  1. SELECT employee_id,last_name,job_id,department_id
  2. FROM employees
  3. WHERE employee_id IN (
  4.         SELECT DISTINCT manager_id
  5.       FROM employees 
  6. )

方式3：使用EXISTS

  1. SELECT employee_id,last_name,job_id,department_id
  2. FROM employees el
  3. WHERE EXISTS(
  4.         SELECT *
  5.       FROM employees e2
  6.       WHERE e1.employee_id = e2.manager_id
  7. )
  • NOT EXISTS

查询departments表中不存在于employees表中的部门的department_id和department_name

方式1：

  1. SELECT d.department_id,d.department_name
  2. FROM employees e RIGHT JOIN departments d
  3. ON e.department_id = d.department_id
  4. WHERE e.department_id IS NULL;

方式2：

  1. SELECT department_id,department_name
  2. FROM departments d
  3. WHERE NOT EXISTS (
  4.             SELECT * 
  5.           FROM employees e
  6.           WHERE d.department_id = d.department_id
  7. )

相关删除

  • 使用子查询依据一个表中的数据删除另一个表的数据

删除表employees中，其与emp_history表皆有的数据

  1. DELETE FROM employees e
  2. WHERE employee_id IN (
  3.         SELECT employee_id
  4.       FROM emp_history e1
  5.       WHERE e1.employee_id = e.employee_id
  6. )

思考题

  • 谁的工资比Abel高？

方式1：自连接

  1. SELECT e2.last_name,e2.salary
  2. FROM employees e1,employees e2
  3. WHERE e1.last_name = 'Abel' 
  4. AND e1.salary < e2.salary

方式2：子查询

  1. SELECT last_name,salary
  2. FROM employees
  3. WHERE salary > (
  4.             SELECT salary
  5.           FROM employees
  6.           WHERE last_name = 'Abel'
  7. )
  • 自连接的方式好，
  • 子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分DBMS中都对自连接处理进行了优化

课后练习

  1. 查询和Zlotkey相同部门的员工姓名和工资

    1. SELECT e2.last_name,e2.salary
    2. FROM employees e1,employees e2
    3. WHERE e1.department_id = e2.department_id 
    4. AND e1.last_name = 'Zlotkey'
    1. SELECT last_name,salary
    2. FROM employees
    3. WHERE department_id IN (
    4.              SELECT department_id
    5.            FROM employees
    6.            WHERE last_name = 'Zlotkey'
    7. );

  2. 查询工资比公司平均工资高的员工的员工号，姓名和工资

    1. SELECT employee_id,last_name,salary
    2. FROM employees
    3. WHERE salary > (
    4.          SELECT AVG(salary)
    5.        FROM employees 
    6. );

  3. 选择工资大于所有JOB_ID = ‘SA_MAN’的员工的工资的员工的last_name，job_id，salary

    1. SELECT last_name,job_id,salary
    2. FROM employee
    3. WHERE salary > ALL(
    4.      SELECT salary
    5.    FROM employees
    6.    WHERE job_id = 'SA_MAN'
    7. );

  4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 ```sql SELECT DISTINCT e2.employee_id,e2.last_name
    FROM employees e1,employees e2 WHERE e1.last_name LIKE ‘%u%’ AND e1.department_id = e2.department_id

SELECT employee_id,last_name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE last_name LIKE ‘%U%’ )

  2. 5. 查询在部门的location_id为1700的部门工作的员工的员工号
  3. ```sql
  4. SELECT department_id   //从内向外
  5. FROM departments
  6. WHERE location_id = 1700
  8. SELECT employee_id
  9. FROM employees
  10. WHERE department_id IN (
  11.         SELECT department_id
  12.         FROM departments
  13.         WHERE location_id = 1700
  14. );
  1. 查询管理者是King的员工姓名和工资 ```sql SELECT employee_id FROM employees WHERE last_name = ‘King’

SELECT last_name,salary FROM employees WHERE manager_id IN ( SELECT employee_id FROM employees WHERE last_name = ‘King’ )

  2. 7. 查询工资最低的员工信息：last_name，salary
  3. ```sql
  4. SELECT last_name,salary
  5. FROM employees
  6. WHERE salary = (
  7.             SELECT MIN(salary)
  8.           FROM employees
  9. )
  1. 查询平均工资最低的部门信息

方式1：

  1. SELECT MIN(avg_sal)   //查询各部门的平均工资
  2. FROM (
  3.         SELECT AVG(salary) avg_sal
  4.         FROM employees
  5.         GROUP BY department_id
  6. )t_dept_avg_sal
  8. SELECT department_id
  9. FROM employees
  10. GROUP BY department_id
  11. HAVING AVG(salary) = (
  12.             SELECT MIN(avg_sal) 
  13.       FROM (
  14.           SELECT AVG(salary) avg_sal
  15.           FROM employees
  16.           GROUP BY department_id
  17.       )t_dept_avg_sal
  18. ) 
  20. SELECT *
  21. FROM departments
  22. WHERE department_id = (
  23.         SELECT department_id
  24.         FROM employees
  25.         GROUP BY department_id
  26.         HAVING AVG(salary) = (
  27.               SELECT MIN(avg_sal) 
  28.               FROM (
  29.                   SELECT AVG(salary) avg_sal
  30.                   FROM employees
  31.                   GROUP BY department_id
  32.               )t_dept_avg_sal
  33.         ) 
  34. )

方式2：

  1. SELECT *
  2. FROM departments
  3. WHERE department_id = (
  4.         SELECT department_id
  5.         FROM employees
  6.         GROUP BY department_id
  7.         HAVING AVG(salary) <= ALL(
  8.                   SELECT AVG(salary)
  9.                   FROM employees
  10.                   GROUP BY department_id
  11.               )
  12. )

方式3：LIMIT

  1. SELECT AVG(salary) avg_sal
  2. FROM employees
  3. GROUP BY department_id
  4. ORDER BY avg_sal ASC
  5. LIMIT 1
  8. SELECT *
  9. FROM departments
  10. WHERE department_id = (
  11.         SELECT department_id
  12.         FROM employees
  13.         GROUP BY department_id
  14.         HAVING AVG(salary) = (            
  15.                   SELECT AVG(salary) avg_sal
  16.                   FROM employees
  17.                   GROUP BY department_id
  18.                   ORDER BY avg_sal ASC
  19.                   LIMIT 1
  20.               )
  21. );

方式4：

  1. SELECT department_id,AVG(salary) avg_sal
  2. FROM employees
  3. GROUP BY department_id
  4. ORDER BY avg_sal ASC
  5. LIMIT 0,1
  7. SELECT *
  8. FROM departments,(
  9.         SELECT department_id,AVG(salary) avg_sal
  10.         FROM employees
  11.         GROUP BY department_id
  12.         ORDER BY avg_sal ASC
  13.         LIMIT 0,1
  14.         )t_dept_avg_sal
  15. WHERE d.department_id = t_dept_avg_sal.department_id

  1. 查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

    1. SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id) avg_sal
    2. FROM departments d
    3. WHERE department_id = (
    4.      SELECT department_id
    5.      FROM employees
    6.      GROUP BY department_id
    7.      HAVING AVG(salary) = (
    8.            SELECT MIN(avg_sal) 
    9.            FROM (
    10.                SELECT AVG(salary) avg_sal
    11.                FROM employees
    12.                GROUP BY department_id
    13.            )t_dept_avg_sal
    14.      ) 
    15. )

  2. 查询平均工资最高的job信息 ```sql 与第8题相似，也有四种写法

方式4： SELECT j.* FROM jobs j,( SELECT job_id,AVG(salary) avg_sal FROM employees GROUP BY job_id ORDER BY avg_sal DESC LIMIT 0,1 ) t_job_avg_sal WHERE j.job_id = t_job_avg_sal.job_id

  2. 11. 查询平均工资高于公司平均工资的部门有那些？
  3. ```sql
  4. SELECT AVG(salary)
  5. FROM employees
  7. SELECT department_id
  8. FROM employees
  9. WHERE department_id IS NOT NULL
  10. GROUP BY department_id
  11. HAVING AVG(salary) > (
  12.                 SELECT AVG(salary)
  13.               FROM employees
  14. )

  1. 查询出公司所有manager的详细信息

    1. SELECT DISTINCT mgr.*
    2. FROM employees emp JOIN employees mgr
    3. ON emp.manager_id = mgr.employee_id

  2. 各个部门中 最高工资中最低的那个部门的 最低工资是多少？ ```sql SELECT MIN(salary) FROM employees WHERE department_id = (

    1.             SELECT department_id
    2.   FROM employees
    3.   ORDER BY department_id
    4.   HAVING MAX(salary) = (
    5.             SELECT MIN(max_sal)
    6.             FROM (
    7.                     SELECT MAX(salary) max_sal
    8.                     FROM employees
    9.                     GROUP BY department_id
    10.             ) t_dept_max_sal
    11.   )

    )

方式4： SELECT MIN(salary) FROM employees e,( SELECT department_id,MAX(salary) max_sal FROM employees GROUP BY department_id ORDER BY max_sal ASC LIMIT 0,1 ) t_dept_max_sal WHERE e.department_id = t_dept_max_sal.department_id

  2. 14. 查询平均工资最高的的部门的manager的详细信息
  3. ```sql
  4. SELECT MAX(avg_sal)
  5. FROM (
  6.                 SELECT AVG(salary)
  7.         FROM employees
  8.         GROUP BY department_id
  9. )
  11. SELECT department_id
  12. FROM employees
  13. GROUP BY department_id
  14. HAVING AVG(salary) = (
  15.                     SELECT MAX(avg_sal)
  16.           FROM (
  17.                   SELECT AVG(salary)
  18.                   FROM employees
  19.                   GROUP BY department_id
  20.           )
  21. )
  23. SELECT DISTINCT manager_id
  24. FROM employees 
  25. WHERE department_id = (
  26.                 SELECT department_id
  27.         FROM employees
  28.         GROUP BY department_id
  29.         HAVING AVG(salary) = (
  30.                   SELECT MAX(avg_sal)
  31.                   FROM (
  32.                           SELECT AVG(salary)
  33.                           FROM employees
  34.                           GROUP BY department_id
  35.                   )
  36.         )
  37. )
  1. 查询部门的部门号，其中不包括job_id是”ST_CLERK”的部门号

方式1：

  1. SELECT department_id
  2. FROM employees
  3. WHERE department_id NOT IN (
  4.             SELECT DISTINCT department_id
  5.       FROM employees
  6.       WHERE job_id = 'ST_CLERK'
  7. )

方式2：

  1. SELECT department_id
  2. FROM departments d
  3. WHERE NOT EXISTS (
  4.                 SELECT *
  5.               FROM employees d
  6.                WHERE d.department_id = e.department_id
  7.                WHERE e.job_id = 'ST_CLERK'
  8. )

  1. 选择所有没有管理者的员工的last_name

    1. SELECT last_name
    2. FROM employees emp
    3. WHERE NOT EXISTS (
    4.         select *
    5.       from employees mgr
    6.       where emp.manager_id = mgr.employee_id
    7. )

  2. 查询员工号、姓名、雇佣时间、工资。其中员工的管理者为’De Haan’

    1. SELECT employee_id,last_name,hire_date,salary
    2. FROM employees
    3. WHERE manager_id IN (
    4.         SELECT employee_id
    5.        FROM employees
    6.       WHERE last_name = 'De Haan'
    7. )

  3. 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资

    1. SELECT department_id,last_name,salary
    2. FROM employees emp
    3. WHERE salary > (
    4.     SELECT AVG(salary)
    5.   FROM employees 
    6.   WHERE department_id = emp.department_id
    7. )

  4. 查询各个部门下的部门人数大于5的部门名称

    1. SELECT department_name
    2. FROM departments d
    3. WHERE 5 < (
    4.     SELECT COUNT(*)
    5.   from employees e
    6.   WHERE d.department_id = e.department_id
    7. )

  5. 查询每个国家下的部门个数大于2的国家编号

    1. SELECT country_id
    2. FROM location_id
    3. WHERE 2  < (
    4.      SELECT count(*)
    5.   FROM departments d
    6.   WHERE l.location_id = d.loaction_id
    7. )

    编写技巧

  • 如果子查询相对比较简单，建议从外往里写。一旦子查询结构比较复杂，建议从里往外写
  • 如果是相关子查询，通常都是从外往里写