MySQL WHERE子句中子查询需返回标量、单列或多行，分别用于比较、IN和EXISTS；注意避免NOT IN含NULL、函数包裹子查询等性能陷阱。

在 MySQL 的 WHERE 子句中使用子查询，是实现动态条件过滤的核心技巧。关键在于子查询必须返回明确的值（标量）、单列结果（用于 IN）或行集（配合 EXISTS），且不能有歧义。

用子查询返回单个值做比较

适用于需要一个具体数值作为判断依据的场景，比如查“工资高于部门平均工资的员工”。

• 子查询必须只返回一行一列，否则会报错 Subquery returns more than 1 row
• 常用操作符：=、>、、>=、、
• 示例：
  SELECT name, salary FROM emp WHERE salary > (SELECT AVG(salary) FROM emp WHERE dept = 'tech');

用 IN 配合子查询筛选多值

当主查询需匹配多个可能值时，IN 是最直观的选择，子查询返回单列多行结果即可。

• 子查询可返回 0 行（此时无结果），也可返回重复值（IN 自动去重语义）
• 注意性能：若子查询结果很大，建议确保相关字段有索引
• 示例：
  SELECT * FROM order WHERE customer_id IN (SELECT id FROM customer WHERE level = 'VIP');

用 EXISTS 判断子查询是否存在满足条件的记录

EXISTS 不关心子查询返回什么内容，只关注是否有结果行，适合关联性较强的逻辑，通常比 IN 更高效（尤其子查询表大时）。

• 子查询中常使用相关子查询（即引用外部表字段），如 WHERE c.id = o.customer_id
• 推荐写法：子查询 SELECT 1 或 SELECT * 即可，不需真实字段
• 示例：
  SELECT * FROM customer c WHERE EXISTS (SELECT 1 FROM order o WHERE o.customer_id = c.id AND o.status = 'paid');

避免常见错误和性能陷阱

嵌套查询容易写出低效甚至错误的 SQL，需特别注意：

• 别在 WHERE 中对子查询结果做函数处理，如 WHERE YEAR((SELECT ...)) = 2025 —— 这会导致子查询反复执行
• 慎用 NOT IN：若子查询结果含 NULL，整个条件恒为 FALSE，推荐改用 NOT EXISTS
• 复杂逻辑优先考虑 JOIN 或临时表，过度嵌套会降低可读性和优化器效率
• 用 EXPLAIN 查看执行计划，确认子查询是否被转为半连接（semi-join）等优化形式