示例
示例
-- EXISTS：存在性判断，语义清晰
SELECT u.id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 等价 JOIN + 去重
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id;
-- 需要计数/聚合时应直接用 LEFT JOIN
SELECT u.id, COUNT(o.id) AS cnt
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;| EXISTS | 半连接语义：只判断存在，不产生重复行 |
|---|---|
| IN | 与 EXISTS 常可互换；NOT IN 有 NULL 陷阱，建议改写为 NOT EXISTS |
| 优化器 | MySQL 有半连接优化策略；PG 通常产生 Semi Join 类计划 |