NOT EXISTS最稳，因NOT IN遇NULL时条件恒为UNKNOWN被过滤；需先用COUNT(*)与COUNT(id)判断是否存在NULL，再用NOT EXISTS关联外层查断号起点，并加t1.id

SQL Server 中用子查询查断号，最稳的写法是 NOT EXISTS + 外层 id ，而不是 NOT IN —— 后者遇到 NULL 就全失效。

为什么 NOT IN 在 SQL Server 里查不出断号？

只要源表的 ID 列里存在 NULL，WHERE id NOT IN (SELECT id FROM t) 整个条件就会变成 UNKNOWN，所有行被过滤掉，结果永远为空。这不是语法错，是三值逻辑的必然行为。

• 先跑 SELECT COUNT(*), COUNT(id) FROM your_table，如果两个数不等，说明有 NULL，必须处理
• NOT EXISTS 不受 NULL 影响，但子查询里必须关联外层（比如 WHERE t2.id = t1.id + 1），不能写成独立子查询
• 如果业务允许，直接加 WHERE id IS NOT NULL 过滤再用 NOT IN，但不如换 NOT EXISTS 干净

用 NOT EXISTS 查出断号起点（推荐写法）

这个结果表示“该 ID 后面断了”，即缺失的是 id + 1。它兼容 SQL Server 所有版本，执行计划也容易走索引。

SELECT t1.id AS gap_start
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1 FROM your_table t2 WHERE t2.id = t1.id + 1
)
AND t1.id 

• t1.id 必须加，否则最大值本身也会被当成断号起点
• 确保 id 字段上有索引，否则子查询会触发大量嵌套循环扫描
• 若表很大且 id 稀疏（比如只存了 1、100、200），可先加 WHERE t1.id >= @min AND t1.id 缩小外层扫描范围

想直接得到“缺失区间”（如 4–6、9–9）怎么办？

单靠一层子查询做不到，得先定位缺口两端：用 NOT EXISTS 找到每个断点起始，再用子查询找下一个存在的 ID，相减减一就得区间长度。

SELECT 
    t1.id + 1 AS istart,
    (SELECT MIN(t2.id) FROM your_table t2 WHERE t2.id > t1.id) - 1 AS iend
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1 FROM your_table t3 WHERE t3.id = t1.id + 1
)
AND t1.id 

• 内层 (SELECT MIN(t2.id) FROM ...) 是相关子查询，性能依赖 id 索引；没有索引时可能比 LEFT JOIN 还慢
• 结果中 istart > iend 表示只缺一个数（比如 istart=5, iend=5），这是正常情况
• 若数据量超 10 万，建议改用 LEAD() 窗口函数（SQL Server 2012+），避免多次子查询

补全缺失数字时最容易忽略的边界

生成缺失值不是为了凑数，而是还原“本该存在”的语义。很多人卡在三个地方：

• 空表时 SELECT MAX(id) 返回 NULL，整个子查询报错 → 外层要用 COALESCE((SELECT MAX(id) FROM t), 0)
• 用递归 CTE 生成序列时忘了加 OPTION (MAXRECURSION 0)，默认 100 层就中断，报错 Msg 530
• 误把 ROW_NUMBER() 当补缺——它只是重编号，不会还原原始缺失的 ID 值，比如原数据是 1,2,4，它给 1,2,3，但 3 并非系统中该补的位置