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.