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

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

How do I write this logic in T-SQL where I want to exclude null, NA as well as the string NULL?

如何在T-SQL中编写此逻辑，我想要排除null，NA以及字符串NULL？

2 个解决方案

#1

Two possibilities are to either use a where clause, which was also indicated by the title of this topic, or drop the left join.

两种可能性是使用where子句，也可以使用本主题的标题，或者删除左连接。

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')

#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