在SQL Server中的where子句中排除null和其他值

Question

FROM 
Table1 L
LEFT JOIN 
Table2 M ON L.id = M.id
AND ISNULL([Col1], '') NOT IN ('', '#N/A', 'NULL')

我如何在T-SQL中编写这个逻辑，在那里我想排除null、NA以及字符串null？

Answer 1

两种可能性是使用where子句(该子句也由本主题的标题指示(，或者删除left联接。

SELECT ...
FROM Table1 L
JOIN Table2 M ON L.id = M.id
AND ISNULL([Col1],'') NOT IN ('','#N/A','NULL')

SELECT ...
FROM Table1 L
JOIN Table2 M ON L.id = M.id
WHERE ISNULL([Col1],'') NOT IN ('','#N/A','NULL')

Answer 2

SELECT      * 
FROM        Table1 L 
LEFT JOIN   Table2 M 
ON     L.id = M.id 
AND 
M.id IS NOT NULL 
AND 
NOT(L.[Col1] IN ('#N/A', 'NULL')) 

SELECT      * 
FROM        (SELECT * FROM Table1 WHERE NOT(Table1.[Col1] IN ('#N/A', 'NULL'))) L
LEFT JOIN   Table2 M 
ON      L.id = M.id 
AND 
M.id IS NOT NULL