M5-022Medium性能与陷阱 · Performance & traps🔥 大厂高频出现 1 次

SQL 里 NULL 有哪些坑?举三个具体例子。

What are the NULL traps in SQL? Give three concrete examples.
💡 考场高分一句话答案:

NULL 参与比较返回 UNKNOWN(不是 FALSE)、NOT IN 遇 NULL 返回空、聚合函数忽略 NULL 导致计数偏差。

Comparisons with NULL return UNKNOWN rather than FALSE, NOT IN with a NULL returns nothing, and aggregates silently ignore NULLs.

📌 核心考察要点 (Key Points)

  • 比较:NULL = NULL 为 UNKNOWN,必须用 IS NULL
  • NOT IN:子查询含 NULL 时整体返回空——用 NOT EXISTS 替代
  • 聚合:COUNT(col) 忽略 NULL,COUNT(*) 不忽略;AVG 也忽略 NULL 导致分母变小
  • JOIN:NULL 不匹配任何值(包括 NULL),关联会丢行
  • Comparison: NULL = NULL is UNKNOWN; always use IS NULL
  • NOT IN: a NULL in the subquery empties the result — use NOT EXISTS
  • Aggregates: COUNT(col) skips NULLs while COUNT(*) does not; AVG skips NULLs and shrinks the denominator
  • Joins: NULL never matches anything, including NULL, so rows are dropped

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

  • 如何用 IFNULL/COALESCE 处理 NULL?
  • NULL 在 GROUP BY 里会被分成一组吗?

🎯 关联大厂考题实战训练 (1 题)

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

零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 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➔