SQL中NOT EXISTS的通用替代方案咨询

首先先明确你的基础表结构和数据（方便大家理解上下文）：

CREATE TABLE t
(
id SERIAL PRIMARY KEY,
name VARCHAR(50),
food VARCHAR(50)
);

INSERT INTO t (name, food)
VALUES
('john', 'pizza'),
('john', 'cake'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('matt', 'pizza'),
('matt', 'pizza'),
('matt', 'burger'),
('david', 'cake'),
('david', 'pizza'),
('david', 'pizza'),
('elen', 'cake'),
('elen', 'pizza'),
('elen', 'donuts'),
('claire', 'cake'),
('claire', 'donuts'),
('claire', 'tacos'),
('john', 'pizza'),
('john', 'cake'),
('matt', 'apples'),
('matt', 'tacos');

你原来用NOT EXISTS实现了两个查询，但遇到部分SQL平台不支持关联子查询的问题，下面是两种通用且安全的替代方案，完全能实现你原有的查询需求：

一、左连接（LEFT JOIN）方案 #

这个方法的核心思路是：通过左连接匹配出用户所有不符合条件的记录，再筛选出没有匹配到不符合条件记录的用户行，以此达到和NOT EXISTS相同的效果。

替代Query1（获取只喜欢披萨的用户所有行） #

SELECT t1.id, t1.name, t1.food
FROM t AS t1
LEFT JOIN t AS t2 ON t1.name = t2.name AND t2.food != 'pizza'
WHERE t1.food = 'pizza' AND t2.food IS NULL
ORDER BY 1;

替代Query2（获取只喜欢披萨和蛋糕的用户所有行） #

SELECT t1.id, t1.name, t1.food
FROM t AS t1
LEFT JOIN t AS t2 ON t1.name = t2.name AND t2.food NOT IN ('cake', 'pizza')
WHERE t1.food IN ('cake', 'pizza') AND t2.food IS NULL
ORDER BY 1;

二、分组聚合（GROUP BY + HAVING）方案 #

这个方法先通过分组筛选出符合条件的用户（即没有其他食物偏好的用户），再回原表取出这些用户的所有目标行，逻辑直观且兼容性极强。

替代Query1（获取只喜欢披萨的用户所有行） #

SELECT t.id, t.name, t.food
FROM t
WHERE t.name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING COUNT(DISTINCT CASE WHEN food != 'pizza' THEN food END) = 0
)
AND t.food = 'pizza'
ORDER BY 1;

替代Query2（获取只喜欢披萨和蛋糕的用户所有行） #

SELECT t.id, t.name, t.food
FROM t
WHERE t.name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING COUNT(DISTINCT CASE WHEN food NOT IN ('cake', 'pizza') THEN food END) = 0
)
AND t.food IN ('cake', 'pizza')
ORDER BY 1;

这两种方案几乎兼容所有主流SQL平台，不需要依赖NOT EXISTS这类关联子查询特性，完全能达到你原来的查询效果。

备注：内容来源于stack exchange，提问作者Uk rain troll