三种写法
三种写法
-- 1) NOT EXISTS：与 NULL 无关
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = u.id);
-- 2) NOT IN：子查询含 NULL 时结果为空
SELECT * FROM users u
WHERE u.id NOT IN (SELECT user_id FROM blacklist);
-- 3) LEFT JOIN IS NULL
SELECT u.* FROM users u
LEFT JOIN blacklist b ON b.user_id = u.id
WHERE b.user_id IS NULL;| NOT EXISTS | 推荐；子查询列可空也安全 |
|---|---|
| NOT IN | 子查询结果含 NULL 时整体为空 |
| LEFT JOIN IS NULL | 判空列必须 NOT NULL（如主键） |