CREATE TABLE AS SELECT WITH DATA生成结果与单独查询不符问题咨询

首先可以明确：CREATE TABLE ... AS (SELECT ...) WITH DATA 这个语法本身不会固有地导致行数差异——它本质就是把SELECT的结果集持久化到新表中。你遇到的行数不一致，大概率是查询逻辑、数据特性或执行环境的细节问题导致的，下面是最常见的几个排查方向：

1. NOT IN子查询的NULL陷阱（最可能的原因） #

你的查询里用到了 WHERE key_id NOT IN (...)，如果子查询 A 中的 key_id 存在NULL值，那么整个NOT IN条件会返回UNKNOWN，导致这部分WHERE子句过滤掉所有行！

举个例子：如果子查询A里有一条key_id IS NULL的记录，那么key_id NOT IN (...)等价于key_id != val1 AND key_id != val2 AND ... AND key_id != NULL——而key_id != NULL永远是UNKNOWN，最终整个条件不成立，这部分的SELECT结果会被全部过滤。

你可以先验证子查询是否存在NULL：

SELECT COUNT(*) 
FROM (
    SELECT key_id 
    FROM(
        SELECT COL1, COL2 FROM EXISTING_TABLE_3 
        UNION 
        SELECT COL1, COL2 FROM EXISTING_TABLE_4 
    )A
) 
WHERE key_id IS NULL;

如果结果大于0，建议把NOT IN改成NOT EXISTS，它对NULL的处理更符合预期：

SELECT COL_1, COL2 FROM EXISTING_TABLE_2 
WHERE NOT EXISTS (
    SELECT 1 FROM(
        SELECT COL1, COL2 FROM EXISTING_TABLE_3 
        UNION 
        SELECT COL1, COL2 FROM EXISTING_TABLE_4 
    )A 
    WHERE A.key_id = EXISTING_TABLE_2.key_id
)

2. UNION的去重行为差异 #

你用了UNION（而非UNION ALL），它会自动去重结果集中的重复行。可能出现的差异场景：

• 单独执行SELECT时，客户端工具是否限制了显示行数？比如有些工具默认只显示前1000行，你误以为总共有30万+，但实际去重后就是25万？
• 源表的列数据类型不一致：比如EXISTING_TABLE_1.COL_1是VARCHAR(50)，EXISTING_TABLE_2.COL_1是VARCHAR(40)，UNION时会隐式转换为更短的类型，导致部分数据被截断，原本不重复的行变成重复，进而被去重。
• 数据库对UNION的去重实现差异：单独执行SELECT时和CREATE TABLE时，优化器用了不同的去重算法（比如排序去重vs哈希去重），极端情况下可能因为哈希冲突导致额外去重（概率极低，但值得排查）。

3. 数据一致性问题 #

你单独执行SELECT和运行CREATE TABLE语句的时间差里，源表（EXISTING_TABLE_1/2/3/4）可能发生了数据变更：比如有数据被删除、更新，或者新插入的数据刚好被UNION去重了，导致两次结果行数不同。

建议你在同一个事务中先执行SELECT并统计行数，再执行CREATE TABLE，然后立即统计新表的行数，排除数据变更的影响。

4. 表列的隐式定义导致数据丢失 #

当用CREATE TABLE AS SELECT时，数据库会自动推断新表的列类型和长度。如果源表的列有特殊特性（比如超长字符串、高精度数值），新表的列可能被截断：

• 比如源表COL_2是DECIMAL(18,9)，新表被推断为DECIMAL(10,2)，导致部分数值被四舍五入后变成重复行，被UNION去重。
• 或者字符串列长度被缩短，导致部分数据截断，进而产生重复。

你可以对比MY_TABLE和源表的列定义，看是否存在类型或长度的差异（不同数据库语法略有不同，以DB2为例）：

DESCRIBE TABLE MY_TABLE;
DESCRIBE TABLE EXISTING_TABLE_1;

排查步骤总结 #

• 优先检查NOT IN子查询中的NULL值，这是最常见的坑；
• 验证UNION是否需要改为UNION ALL（如果不需要去重的话）；
• 在同一事务中对比SELECT和CREATE TABLE的行数，排除数据变更；
• 对比新表和源表的列定义，检查是否有隐式转换或截断；
• 查看两次查询的执行计划，确认优化器是否采用了不同的处理逻辑。

内容的提问来源于stack exchange，提问作者Anonymous