如何在TSQL查询中跳过含NULL值的记录？附复杂查询示例

嘿，这个需求很常见，我结合你的查询给你分两种场景来处理：

场景1：跳过特定列存在NULL的记录 #

如果只是想确保某几个关键列（比如关联字段、业务必填字段）不为NULL，直接在查询末尾加WHERE子句，用IS NOT NULL判断就行。注意TSQL里绝对不能用!= NULL或<>来判断非NULL，必须用IS NOT NULL语法。

比如，假设你想确保v.KodQ、v.rDatum和k.Oblast这几个核心列没有NULL值，修改后的查询如下：

SELECT distinct 
    v.KodQ, v.DodPeriodika, v.GodGP, v.Periodika as Period, 
    k.Oblast, k.KratokNazivSI, k.NazivSI, k.Periodika, 
    v.rKod, r.Naziv, v.rDatum, v.IT, v.Primerok, v.BrojIE, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo 
FROM GP.dbo.MyTable1 AS v 
INNER JOIN GP.dbo.MyTable2 as k ON k.KodSI = v.KodQ AND k.DodObr = v.DodPeriodika 
INNER JOIN GP.dbo.MyTable3 AS r ON r.... -- 补充你的完整关联条件
WHERE 
    v.KodQ IS NOT NULL 
    AND v.rDatum IS NOT NULL 
    AND k.Oblast IS NOT NULL;

你可以根据实际业务需求，把更多需要检查的列添加到WHERE子句里。

场景2：跳过任意列存在NULL的记录 #

如果要求查询返回的所有列都不能有NULL值，那就要把SELECT里的每一列（原始字段）都加到WHERE子句做非NULL判断。比如：

SELECT distinct 
    v.KodQ, v.DodPeriodika, v.GodGP, v.Periodika as Period, 
    k.Oblast, k.KratokNazivSI, k.NazivSI, k.Periodika, 
    v.rKod, r.Naziv, v.rDatum, v.IT, v.Primerok, v.BrojIE, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo 
FROM GP.dbo.MyTable1 AS v 
INNER JOIN GP.dbo.MyTable2 as k ON k.KodSI = v.KodQ AND k.DodObr = v.DodPeriodika 
INNER JOIN GP.dbo.MyTable3 AS r ON r.... -- 补充你的完整关联条件
WHERE 
    v.KodQ IS NOT NULL 
    AND v.DodPeriodika IS NOT NULL 
    AND v.GodGP IS NOT NULL 
    AND v.Periodika IS NOT NULL 
    AND k.Oblast IS NOT NULL 
    AND k.KratokNazivSI IS NOT NULL 
    AND k.NazivSI IS NOT NULL 
    AND k.Periodika IS NOT NULL 
    AND v.rKod IS NOT NULL 
    AND r.Naziv IS NOT NULL 
    AND v.rDatum IS NOT NULL 
    AND v.IT IS NOT NULL 
    AND v.Primerok IS NOT NULL 
    AND v.BrojIE IS NOT NULL 
    AND v.RefPeriodOd IS NOT NULL 
    AND v.RefPeriodDo IS NOT NULL;

这里要注意：RefPeriodOd和RefPeriodDo是转换后的别名列，只要原始字段v.RefPeriodOd和v.RefPeriodDo非NULL，转换结果就不会是NULL，所以只需要检查原始字段即可，不用管别名列。

另外提一句：你的INNER JOIN本身会自动过滤掉关联条件中不匹配的记录（比如k.KodSI = v.KodQ中任意一方是NULL，这条记录会被排除），但如果关联后的其他列有NULL，还是得靠WHERE子句来过滤。

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