SQL查询中高效比较DATE与DATETIME列及连接问题排查

我的两张表有数百万甚至数十亿行数据，必须保证查询高效。多表连接的片段如下：

LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              AND (GC_TBMED.MED_EFF_DATE = dbo_GCSOCTPS.EFF_DATE)
                              AND (GC_TBMED.MED_CANC_DATE = dbo_GCSOCTPS.CANC_DATE)) 

其中GC_TBMED的日期列是DATE类型，dbo_GCSOCTPS的日期列是DATETIME类型，受公司规则限制无法修改列格式。

测试情况 #

单独查询两张表都能返回匹配数据：

查询dbo_GCSOCTPS #

SELECT TOP 200 
    HDHPQ,
    SOC_NUM, 
    EFF_DATE,
    CLASS_NUM,
    CANC_DATE
FROM
    dbo.GCSOCTPS
WHERE
    SOC_NUM = '25521'
    AND CLASS_NUM = '37'
    AND CANC_DATE IS NULL;

返回结果：

HDHPQ    SOC_NUM    EFF_DATE                  CLASS_NUM    CANC_DATE 
N         25521    2025-01-01 00:00:00.000      37         NULL

查询GC_TBMED #

SELECT TOP 200 
    MED_SOC_NUM,
    MED_EFF_DATE,
    MED_CLASS_NUM,
    MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
WHERE 
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL;

返回结果：

MED_SOC_NUM    MED_EFF_DATE    MED_CLASS_NUM    MED_CANC_DATE
25521        2025-01-01        37                NULL

但执行以下连接查询时返回空结果集：

SELECT DISTINCT TOP 200 
    dbo_GCSOCTPS.HDHPQ,
    dbo_GCSOCTPS.SOC_NUM, 
    dbo_GCSOCTPS.EFF_DATE,
    dbo_GCSOCTPS.CLASS_NUM,
    dbo_GCSOCTPS.CANC_DATE,
    GC_TBMED.MED_SOC_NUM,
    GC_TBMED.MED_EFF_DATE,
    GC_TBMED.MED_CLASS_NUM,
    GC_TBMED.MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              AND (GC_TBMED.MED_EFF_DATE = CAST(dbo_GCSOCTPS.EFF_DATE as DATE))
                              AND (GC_TBMED.MED_CANC_DATE = CAST(dbo_GCSOCTPS.CANC_DATE as DATE)) 
WHERE
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL
    AND dbo_GCSOCTPS.EFF_DATE >= '2025-01-01';

注释掉连接条件中的日期字段就能返回数据，推测是日期匹配逻辑有问题，同时想知道针对超大数据集，哪种日期比较方式最高效（CAST/CONVERT/转文本）。

问题排查与解决方案 #

1. 连接返回空的直接原因 #

• NULL值比较逻辑错误：SQL中NULL = NULL的结果是UNKNOWN而非TRUE，连接条件里的GC_TBMED.MED_CANC_DATE = CAST(dbo_GCSOCTPS.CANC_DATE as DATE)当两边都是NULL时，条件不成立，导致无法匹配。
• LEFT JOIN被强制转为INNER JOIN：WHERE子句中的dbo_GCSOCTPS.EFF_DATE >= '2025-01-01'会过滤掉所有dbo_GCSOCTPS无匹配的行（此时该列值为NULL，NULL与任何值比较结果都是UNKNOWN，会被过滤），破坏了LEFT JOIN的原有逻辑。

2. 超大数据集下的高效日期比较方式 #

绝对不要用转文本比较，这会完全无法利用索引，导致全表扫描，性能极差。正确的优化方向：

• 避免在索引列上使用函数：如果dbo_GCSOCTPS.EFF_DATE和CANC_DATE有索引，不要对它们做CAST/CONVERT（会导致索引失效），应反过来把DATE类型列转成DATETIME，这是轻量操作，且不影响原表索引的使用。
• 单独处理NULL值：不要用等号比较NULL，需单独判断NULL的情况。

3. 修正后的连接查询 #

方案一：保留LEFT JOIN逻辑，调整WHERE条件

SELECT DISTINCT TOP 200 
    dbo_GCSOCTPS.HDHPQ,
    dbo_GCSOCTPS.SOC_NUM, 
    dbo_GCSOCTPS.EFF_DATE,
    dbo_GCSOCTPS.CLASS_NUM,
    dbo_GCSOCTPS.CANC_DATE,
    GC_TBMED.MED_SOC_NUM,
    GC_TBMED.MED_EFF_DATE,
    GC_TBMED.MED_CLASS_NUM,
    GC_TBMED.MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              -- 把DATE转DATETIME，利用GCSOCTPS.EFF_DATE的索引
                              AND (CAST(GC_TBMED.MED_EFF_DATE AS DATETIME) = dbo_GCSOCTPS.EFF_DATE)
                              -- 单独处理NULL情况，避免NULL=NULL的逻辑错误
                              AND (
                                  (GC_TBMED.MED_CANC_DATE IS NULL AND dbo_GCSOCTPS.CANC_DATE IS NULL)
                                  OR (CAST(GC_TBMED.MED_CANC_DATE AS DATETIME) = dbo_GCSOCTPS.CANC_DATE)
                              )
WHERE
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL
    -- 保留LEFT JOIN的无匹配行，增加NULL判断
    AND (dbo_GCSOCTPS.EFF_DATE >= '2025-01-01' OR dbo_GCSOCTPS.EFF_DATE IS NULL);

方案二：将过滤条件移至ON子句（更优）

把dbo_GCSOCTPS.EFF_DATE >= '2025-01-01'移到JOIN的ON子句中，避免破坏LEFT JOIN逻辑：

SELECT DISTINCT TOP 200 
    dbo_GCSOCTPS.HDHPQ,
    dbo_GCSOCTPS.SOC_NUM, 
    dbo_GCSOCTPS.EFF_DATE,
    dbo_GCSOCTPS.CLASS_NUM,
    dbo_GCSOCTPS.CANC_DATE,
    GC_TBMED.MED_SOC_NUM,
    GC_TBMED.MED_EFF_DATE,
    GC_TBMED.MED_CLASS_NUM,
    GC_TBMED.MED_CANC_DATE
FROM
    [dbo].[AS_tblTBMED] GC_TBMED
LEFT JOIN 
    dbo.GCSOCTPS dbo_GCSOCTPS ON (GC_TBMED.MED_CLASS_NUM = dbo_GCSOCTPS.CLASS_NUM) 
                              AND (GC_TBMED.MED_SOC_NUM = dbo_GCSOCTPS.SOC_NUM)
                              AND (CAST(GC_TBMED.MED_EFF_DATE AS DATETIME) = dbo_GCSOCTPS.EFF_DATE)
                              AND (
                                  (GC_TBMED.MED_CANC_DATE IS NULL AND dbo_GCSOCTPS.CANC_DATE IS NULL)
                                  OR (CAST(GC_TBMED.MED_CANC_DATE AS DATETIME) = dbo_GCSOCTPS.CANC_DATE)
                              )
                              -- 将过滤条件移至ON子句，保留LEFT JOIN特性
                              AND dbo_GCSOCTPS.EFF_DATE >= '2025-01-01'
WHERE
    GC_TBMED.MED_SOC_NUM = '25521'
    AND GC_TBMED.MED_CLASS_NUM = '37'
    AND GC_TBMED.MED_CANC_DATE IS NULL;

4. 额外性能优化建议 #

• 给dbo_GCSOCTPS创建复合索引：(SOC_NUM, CLASS_NUM, EFF_DATE, CANC_DATE)，连接时可直接通过索引快速定位匹配行。
• 除非确实需要去重，否则去掉DISTINCT，它会增加额外的排序开销。

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