- 关联子查询通常也会和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
);
需求:查询departments表中,不存在于employees表中的部门的department_id和department_name。
```sql
#方式1:
SELECT d.department_id,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
#方式2:
SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE d.`department_id` = e.`department_id`
);