子查询 vs JOIN，到底该用哪个？

-- JOIN写法（推荐）
SELECT
  o.order_id,
  o.order_amount,
  u.user_name,
  u.phone
FROM user_order o
JOIN dim_user u ON o.user_id = u.user_id
 
-- 子查询写法（不推荐）
SELECT
  order_id,
  order_amount,
  (SELECT user_name FROM dim_user WHERE user_id = o.user_id) AS user_name,
  (SELECT phone FROM dim_user WHERE user_id = o.user_id) AS phone
FROM user_order o

-- EXISTS写法（推荐）
SELECT u.user_id, u.user_name
FROM dim_user u
WHERE EXISTS (
  SELECT 1 FROM user_order o
  WHERE o.user_id = u.user_id
)
 
-- IN写法
SELECT user_id, user_name
FROM dim_user
WHERE user_id IN (
  SELECT user_id FROM user_order
)
 
-- JOIN写法
SELECT DISTINCT u.user_id, u.user_name
FROM dim_user u
JOIN user_order o ON u.user_id = o.user_id

-- ❌ NOT IN遇到NULL会出问题
SELECT user_id, user_name
FROM dim_user
WHERE user_id NOT IN (
  SELECT user_id FROM user_order  -- 如果这里有NULL...
)

如果user_order表中有一条记录的user_id是NULL，那么NOT IN的结果会变成空集——一个用户都查不出来。

为什么？SQL的三值逻辑。x NOT IN (1, 2, NULL) 等价于 x != 1 AND x != 2 AND x != NULL。而x != NULL的结果是UNKNOWN（不是TRUE也不是FALSE），AND上一个UNKNOWN，整个条件就永远不会是TRUE。

-- ✅ 用NOT EXISTS替代NOT IN
SELECT u.user_id, u.user_name
FROM dim_user u
WHERE NOT EXISTS (
  SELECT 1 FROM user_order o
  WHERE o.user_id = u.user_id
)
 
-- ✅ 或者用LEFT JOIN + IS NULL
SELECT u.user_id, u.user_name
FROM dim_user u
LEFT JOIN user_order o ON u.user_id = o.user_id
WHERE o.user_id IS NULL

我之前做用户增长分析的时候就踩过这个坑。需求是"找出注册了但从未下单的用户"，用NOT IN写的，跑出来结果是0。我还以为所有用户都下过单，兴高采烈地跟运营汇报"转化率100%"。结果运营一脸问号，排查了一个下午才发现是订单表里有几条user_id为NULL的脏数据。从那以后我再也不用NOT IN了。

场景四：关联子查询 vs 窗口函数

需求：查每个部门工资最高的员工。

-- 关联子查询写法
SELECT *
FROM employee e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employee e2
  WHERE e2.dept_id = e1.dept_id
)
 
-- 窗口函数写法（推荐）
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employee
) t
WHERE rn = 1

关联子查询在外表每一行都要执行一次内部查询，逻辑上是O(N×M)的复杂度。窗口函数只需要一次全表扫描+分组排序，效率高得多。

而且关联子查询还有一个问题：如果最高工资有并列，它会返回多条记录。窗口函数用ROW_NUMBER可以精确控制只返回一条。

结论：涉及"分组内的TopN/最值"需求，优先用窗口函数。

场景五：大数据场景下的特殊考虑

在Hive/Spark里，子查询和JOIN的差异更大：

1. IN子查询的大小限制

Hive执行IN子查询时，会把子查询结果收集到Driver端，如果结果集太大（比如几百万行），Driver会OOM。而JOIN走的是MapReduce/Spark的分布式Shuffle，不会有这个问题。

-- ❌ 在Hive中，如果子查询返回几百万行，可能OOM
SELECT * FROM big_table
WHERE user_id IN (SELECT user_id FROM another_big_table)
 
-- ✅ 改用JOIN
SELECT t.*
FROM big_table t
JOIN (SELECT DISTINCT user_id FROM another_big_table) u
  ON t.user_id = u.user_id

2. EXISTS在Hive中的支持

早期的Hive版本对EXISTS支持不好，有些写法会报错或者性能很差。Hive 2.x以后好多了，但如果你还在用老版本，LEFT SEMI JOIN是替代方案：

-- Hive的LEFT SEMI JOIN = EXISTS
SELECT u.*
FROM dim_user u
LEFT SEMI JOIN user_order o ON u.user_id = o.user_id

LEFT SEMI JOIN只返回左表中能匹配上右表的行，效果等同于EXISTS，而且不会产生笛卡尔积。

面试总结：怎么答这道题

面试官问"子查询和JOIN怎么选"的时候，分场景回答：

场景	推荐写法	原因
------	---------	------
取关联字段	JOIN	直观，性能好
存在性判断	EXISTS	短路逻辑，不怕NULL
不存在判断	NOT EXISTS 或 LEFT JOIN + IS NULL	NOT IN有NULL陷阱
分组内TopN	窗口函数	比关联子查询快
大数据量过滤	JOIN	子查询结果集可能撑爆Driver

最后补一句：可读性也是选择标准。 团队里10个人看你的SQL，能一眼看懂的写法就是最好的写法。我在做SQL代码规范治理的时候，明确规定了"禁止使用NOT IN""减少标量子查询"这两条规则，从源头避免踩坑。

明天 DAY 12 聊NULL的各种坑，SQL三值逻辑、NULL的算术运算、聚合函数对NULL的处理——这些细节决定了你的SQL是"能跑"还是"跑对"。

你在工作中是偏好子查询还是JOIN？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长