• 关联子查询通常也会和EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。
  • 如果在子查询中不存在满足条件的行:

    • 条件返回FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行:

    • 不在子查询中继续查找
    • 条件返回TRUE
  • NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。

    案例:

    需求:查询公司管理者的employee_id,last_name,job_id,department_id信息 ```sql

    方式1:自连接

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

方式2:子查询

SELECT employee_id,last_name,job_id,department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees );

方式3:使用EXISTS

SELECT employee_id,last_name,job_id,department_id FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e1.employee_id = e2.manager_id );

  1. 需求:查询departments表中,不存在于employees表中的部门的department_iddepartment_name
  2. ```sql
  3. #方式1:
  4. SELECT d.department_id,d.department_name
  5. FROM employees e RIGHT JOIN departments d
  6. ON e.`department_id` = d.`department_id`
  7. WHERE e.`department_id` IS NULL;
  8. #方式2:
  9. SELECT department_id,department_name
  10. FROM departments d
  11. WHERE NOT EXISTS (
  12. SELECT *
  13. FROM employees e
  14. WHERE d.`department_id` = e.`department_id`
  15. );