NOT EXISTS 和 EXCEPT 是 SQL 中用于处理集合差集的高级操作，它们在不同场景下非常有用。
一、 NOT EXISTS 用法
1. 基本语法
1
2
3
4
5
6
7
|
SELECT columns
FROM table1 t1
WHERE NOT EXISTS (
SELECT 1
FROM table2 t2
WHERE t1.related_column = t2.related_column
);
|
2. 实际例子
例1：找出没有项目的员工
1
2
3
4
5
6
7
|
SELECT e.employee_id, e.name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM projects p
WHERE p.leader_id = e.employee_id
);
|
例2：找出没有订单的客户
1
2
3
4
5
6
7
|
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
|
例3：找出从未被订购的产品
1
2
3
4
5
6
7
|
SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM order_items oi
WHERE oi.product_id = p.product_id
);
|
3. 复杂例子：找出没有下属的管理者
1
2
3
4
5
6
7
8
|
SELECT e1.employee_id, e1.name
FROM employees e1
WHERE e1.is_manager = 1
AND NOT EXISTS (
SELECT 1
FROM employees e2
WHERE e2.manager_id = e1.employee_id
);
|
二、 EXCEPT 用法
1. 基本语法
1
2
3
|
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
|
2. 实际例子
例1：找出只在A表不在B表的记录
1
2
3
4
|
-- 找出在employees表但不在former_employees表的员工
SELECT employee_id, name FROM employees
EXCEPT
SELECT employee_id, name FROM former_employees;
|
例2：找出有库存但从未被订购的产品
1
2
3
|
SELECT product_id FROM inventory
EXCEPT
SELECT product_id FROM order_items;
|
例3：多列比较
1
2
3
4
|
-- 找出在2023年有销售但2024年没有的客户
SELECT customer_id, product_id FROM sales_2023
EXCEPT
SELECT customer_id, product_id FROM sales_2024;
|
三、 NOT EXISTS vs EXCEPT 对比
相同需求的不同实现
需求：找出没有项目的部门
使用 NOT EXISTS:
1
2
3
4
5
6
7
|
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM projects p
WHERE p.department_id = d.department_id
);
|
使用 EXCEPT:
1
2
3
4
5
6
|
SELECT department_id, department_name
FROM departments
EXCEPT
SELECT d.department_id, d.department_name
FROM departments d
JOIN projects p ON d.department_id = p.department_id;
|
四、 高级应用场景
1. 多层 NOT EXISTS（复杂逻辑）
找出所有产品都库存充足的产品类别
1
2
3
4
5
6
7
8
9
|
SELECT c.category_id, c.category_name
FROM categories c
WHERE NOT EXISTS (
-- 找出该类别中库存不足的产品
SELECT 1
FROM products p
WHERE p.category_id = c.category_id
AND p.current_stock < p.minimum_stock
);
|
2. EXCEPT 用于数据验证
验证两个表的结构一致性
1
2
3
4
|
-- 找出在source_table中存在但在target_table中不存在的记录
SELECT id, name, value FROM source_table
EXCEPT
SELECT id, name, value FROM target_table;
|
3. 组合使用 NOT EXISTS 和 EXISTS
找出只订购过一次的客户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
-- 至少有一个订单
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
-- 没有第二个订单
SELECT 1 FROM orders o1, orders o2
WHERE o1.customer_id = c.customer_id
AND o2.customer_id = c.customer_id
AND o1.order_id <> o2.order_id
);
|
五、 性能考虑和最佳实践
1. NOT EXISTS 通常性能更好
1
2
3
4
5
6
7
|
-- 推荐：使用NOT EXISTS
SELECT * FROM table1 t1
WHERE NOT EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id);
-- 不推荐：使用NOT IN（对NULL值处理有问题）
SELECT * FROM table1
WHERE id NOT IN (SELECT id FROM table2 WHERE id IS NOT NULL);
|
2. EXCEPT 自动去重
1
2
3
4
|
-- EXCEPT 会自动去除重复记录
SELECT department FROM employees
EXCEPT
SELECT department FROM former_employees;
|
3. 处理NULL值
1
2
3
4
5
6
|
-- NOT EXISTS 能正确处理NULL值
SELECT * FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM discontinued_products dp
WHERE dp.product_id = p.product_id
);
|
六、 实际业务场景
场景1：电商平台 - 找出从未被浏览的商品
1
2
3
4
5
6
7
|
SELECT product_id, product_name
FROM products
WHERE NOT EXISTS (
SELECT 1
FROM user_browsing_history
WHERE product_id = products.product_id
);
|
场景2：学校系统 - 找出没有选课的学生
1
2
3
4
5
6
|
SELECT student_id, student_name
FROM students
EXCEPT
SELECT s.student_id, s.student_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id;
|
场景3：银行系统 - 找出有账户但从未交易的客户
1
2
3
4
5
6
7
8
|
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN accounts a ON c.customer_id = a.customer_id
WHERE NOT EXISTS (
SELECT 1
FROM transactions t
WHERE t.account_id = a.account_id
);
|
总结
- NOT EXISTS: 更适合关联查询，性能通常较好，能正确处理NULL值
- EXCEPT: 更适合比较两个结果集的差异，语法更直观，自动去重
- 选择依据:
- 需要关联条件时用
NOT EXISTS
- 直接比较两个查询结果时用
EXCEPT
- 考虑数据库优化器和具体数据量进行测试
两者都是处理"在A中但不在B中"这类需求的强大工具！