优先用 EXISTS 而非 IN 做存在性判断，因 IN 遇 NULL 会整行过滤，EXISTS 不受 NULL 影响且语义清晰；大表关联时 EXISTS 执行更可控，IN 可能生成临时表拖慢性能。
WHERE 子查询里用 EXISTS 还是 IN？看数据量和 NULL
EXISTS 和 IN 都能做基准对比，但行为完全不同。用 IN 时，如果子查询返回 NULL，整行直接被过滤掉（哪怕其他条件都满足），这是最常踩的坑。比如查“订单表中客户ID不在客户主表里的异常记录”，写成 WHERE customer_id NOT IN (SELECT id FROM customers)，只要 customers.id 里有一个 NULL，结果就是空——不是没异常，是 SQL 自己失效了。
- 优先用
EXISTS做存在性判断，它不care NULL，语义也更清晰 -
NOT EXISTS比NOT IN更安全，尤其在子查询涉及左连接或可能含空值的字段时 - 如果必须用
IN，显式排除 NULL：WHERE customer_id NOT IN (SELECT id FROM customers WHERE id IS NOT NULL) - 大表关联时，
EXISTS通常走半连接，执行计划更可控；IN在某些数据库（如 MySQL 5.7）可能转成临时表，拖慢速度
用窗口函数替代自连接做动态基准对比
自连接写“每个用户最近一笔订单金额 vs 全体平均”这类对比，SQL 又长又慢，还容易漏掉单条记录的边界情况。窗口函数才是正解：AVG(amount) OVER() 直接拿到全局均值，AVG(amount) OVER (PARTITION BY user_id ORDER BY create_time ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 就能算出“当前订单前的历史均值”。
- 窗口函数避免重复扫描，性能提升明显，尤其在百万级订单表上
- 注意
ORDER BY必须明确，否则ROWS BETWEEN行为不可控（PostgreSQL 和 MySQL 8.0+ 要求排序） - 如果基准要按时间范围滑动（比如“过去7天均值”），用
RANGE INTERVAL '7 days' PRECEDING（PostgreSQL）或变量模拟（MySQL） - 窗口函数不能直接出现在
WHERE中，异常检测逻辑得包一层子查询或 CTE
GROUP BY 后 HAVING 检测聚合异常，别在 WHERE 里硬塞
想查“单日下单量突增3倍的日期”，有人会试图在 WHERE 里写 count(<em>) > (SELECT AVG(c) </em> 3 FROM (SELECT COUNT(*) c FROM orders GROUP BY DATE(create_time)) t) ——这不仅语法错（子查询不能直接引用外层分组），而且执行效率极差。
- 异常检测涉及聚合比较，必须先
GROUP BY，再用HAVING过滤 - 基准值（比如历史7日均值）建议提前算好，存到临时表或 CTE，避免每次重算
- 示例：
WITH daily_cnt AS ( SELECT DATE(create_time) d, COUNT(*) c FROM orders WHERE create_time >= CURRENT_DATE - INTERVAL '7 days' GROUP BY DATE(create_time) ), baseline AS (SELECT AVG(c) * 3 AS threshold FROM daily_cnt) SELECT d, c FROM daily_cnt, baseline WHERE c > threshold;
-
HAVING是唯一能在分组后对聚合结果做条件筛选的地方，绕不开
LEFT JOIN + IS NULL 检出缺失基准数据的记录
基准对比的前提是“有基准”。但实际中，配置表、维度表经常缺数据——比如商品表里 category_id = 999，但分类表里根本没有这条记录。这种缺失本身就要告警。
- 用
LEFT JOIN关联基准表，再用WHERE base.id IS NULL直接揪出脏数据 - 别用
NOT IN或NOT EXISTS替代，它们对空值敏感，且无法区分“值不存在”和“基准表为空” - 加上
COUNT(*)和GROUP BY能快速定位缺失集中在哪几个外键字段 - 生产环境建议加索引：
CREATE INDEX idx_orders_category_id ON orders(category_id);，否则大表 JOIN 会卡死
异常检测不是写一次就完事。子查询嵌套层级一深，执行计划就容易偏离预期；基准数据一旦变更（比如统计口径调整），所有依赖它的检测逻辑都得同步核对。最麻烦的往往不是语法，而是没人定期验证基准值本身还合不合理。
