M5-013Medium去重与关联 · Dedup & joins

如何找出“浏览过但从未购买”的用户?

How do you find users who viewed but never purchased?
💡 考场高分一句话答案:

用 LEFT JOIN 或 NOT EXISTS 做反连接;注意 NULL 处理,NOT IN 遇到 NULL 会返回空结果。

A left anti-join or NOT EXISTS. Beware NOT IN, which returns nothing when the subquery contains NULL.

📌 核心考察要点 (Key Points)

  • LEFT JOIN + IS NULL:最通用
  • NOT EXISTS:语义清晰,通常性能更好
  • NOT IN 的坑:子查询含 NULL 时整体返回空
  • 性能:大表用 EXCEPT DISTINCT 或 anti-join 时注意分区裁剪
  • LEFT JOIN plus IS NULL: most portable
  • NOT EXISTS: clearer intent and usually faster
  • NOT IN trap: any NULL in the subquery makes the whole predicate unknown, so the result is empty
  • Performance: watch partition pruning when anti-joining large tables

🔍 大厂面试官连环追问 (Follow-ups)

  • 为什么 NOT IN 遇到 NULL 会返回空?
  • 如何统计“浏览后 7 天内未购买”的用户?

嫌字多看太累?进入苏格拉底式对话推演实验室

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。

💬 开启对话式破冰 ➔
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.
🎯 Drill This Card Now➔