子查询在WHERE中必须返回单值或单列多行，多行时需用IN或EXISTS替代=；标量子查询须严格单行单列；关联子查询易引发性能问题，应优先用JOIN或索引优化。
子查询写在 WHERE 里，但必须返回单值或行集
子查询不是万能过滤器，WHERE 后面跟的子查询如果返回多行单列（比如 SELECT user_id FROM orders WHERE status = 'pending'），直接用 = 会报错：Subquery returns more than 1 row。这时候得换操作符。
- 要判断“是否属于某集合”，用
IN：WHERE user_id IN (SELECT user_id FROM orders WHERE status = 'pending') - 要判断“是否存在关联记录”，用
EXISTS（更高效，不关心具体值）：WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') - 要拿一个标量做比较（比如最高金额），确保子查询只返回一行一列：
WHERE amount > (SELECT MAX(amount) FROM orders WHERE region = 'CN')
关联子查询容易慢，别在大表上无索引乱用
像 SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u 这种写法，每查一条 users 就执行一次子查询——如果 users 有 10 万行，orders 没在 user_id 上建索引，基本就卡死。
- 先确认
orders.user_id有索引，没有就加：CREATE INDEX idx_orders_user_id ON orders(user_id) - 能用
JOIN替代尽量替代，尤其聚合场景：SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name - MySQL 8.0+ 或 PostgreSQL 可考虑用
LATERAL（PostgreSQL）或JOIN LATERAL（MySQL 8.0.14+）来控制执行时机，但需明确版本支持
子查询结果为空时，NOT IN 会意外丢数据
WHERE id NOT IN (SELECT user_id FROM blacklisted WHERE reason = 'fraud') 看似合理，但如果子查询结果为空（没任何拉黑用户），整个表达式会变成 WHERE id NOT IN (NULL)，而任何值跟 NULL 做 NOT IN 都是 UNKNOWN，这一行就被过滤掉了——不是你想要的“全部保留”。
- 改用
NOT EXISTS更安全：WHERE NOT EXISTS (SELECT 1 FROM blacklisted b WHERE b.user_id = u.id AND b.reason = 'fraud') - 或者显式排除
NULL：WHERE id NOT IN (SELECT user_id FROM blacklisted WHERE reason = 'fraud' AND user_id IS NOT NULL) - 注意：Oracle 对
NULL处理更严格，PostgreSQL 和 MySQL 行为一致，但都绕不开这个逻辑陷阱
CTE 不是子查询，但常被拿来替代嵌套子查询提升可读性
三层嵌套的 SELECT ... WHERE x IN (SELECT ... WHERE y IN (SELECT ...)) 很难维护。用 WITH 把中间结果拎出来，不是性能优化（多数情况下执行计划一样），而是为了逻辑分层和复用。
- 比如筛选“近7天下单且30天内有登录的活跃用户”：
WITH recent_orders AS ( SELECT DISTINCT user_id FROM orders WHERE created_at >= NOW() - INTERVAL '7 days' ), recent_logins AS ( SELECT DISTINCT user_id FROM events WHERE type = 'login' AND occurred_at >= NOW() - INTERVAL '30 days' ) SELECT u.* FROM users u WHERE u.id IN (SELECT user_id FROM recent_orders) AND u.id IN (SELECT user_id FROM recent_logins);
- CTE 在 PostgreSQL 和 SQL Server 中支持递归，在 MySQL 8.0+ 也支持，但 SQLite 目前不支持
- 别为了用 CTE 而用 CTE——如果子查询只用一次、逻辑简单，硬拆反而增加阅读负担
子查询真正难的不是语法，是判断它该不该出现、在哪出现、以及数据库到底怎么执行它——看执行计划比背语法重要得多。
