WHERE field1 IN (NULL) [英] WHERE field1 IN (NULL)

问题描述

我想根据几列从表中检索数据，有些是数据，有些是 NULL.在下面的第一个代码示例中，过程很好 - 返回所有行.在第二个示例中，没有返回任何行 - 因为 NULL=NULL 返回 FALSE.

I want to retrieve data from the table based on a couple of columns, some with data, other with NULL. In the first code example below, the procedure is fine - all the rows are returned. In the second example no rows are returned - because NULL=NULL return FALSE.

第三个例子或多或少是我想到的，当列有 NULL 值时，那么这个子句必须被忽略"，只有前两列中的数据用于返回基于这些的行两列.

The third example is more or less what I have in mind, when the column has NULL values, then this clause has to be "ignored" and only the data in the first two columns are used to return the rows based on these two columns.

SELECT * FROM XYZ
WHERE col1 IN ('a', 'b')
AND col2 IN ('c', 'd')
AND col3 IN ('e', 'f')


SELECT * FROM XYZ
WHERE col1 IN ('a', 'b')
AND col2 IN ('c', 'd')
AND col3 IN (NULL) --???


SELECT * FROM XYZ
WHERE col1 IN ('a', 'b')
AND col2 IN ('c', 'd')
--AND col3 IN (NULL) --IGNORED

目前我使用的是动态 SQL 语句，但是速度很慢.

Currently I use a dynamic SQL statement, but is very slow.

SET @EventCodeList = 
(SELECT REPLACE(tEventCode, ' ', '') FROM tblActivityPerCC_Config WHERE tCostCenter = @CostCenter) 


SET @EventCodeStr = 
CASE WHEN @EventCodeList IS NULL THEN ' logs.tEventCode LIKE (''%'') '
ELSE ' logs.tEventCode IN (SELECT qValue FROM nsEMV.dbo.fncReturnCommaDelimitedStringAsTable(@EventCodeList))'
END

SET @SQLString = N'  
SELECT
    ccg.Field1,
    logs.Field2     
FROM dbo.tblEMV_Logsheet AS logs 
INNER JOIN dbo.tblLookup_EMVEquipment AS ccg ON logs.tEquipmentKey = ccg.tEquipmentKey
WHERE tDate BETWEEN ''' + CONVERT(varchar(30), @BMonth) + ''' AND ''' + CONVERT(varchar(30), @EMonth) + ''' 
AND  logs.tAreaCode IN ('XYZ', 'ABC') 
AND ' + @EventCodeStr + ' --THE FOLLOWING COLUMNS MAY HAVE NULL VALUES
AND ' + @SourceStr + '
AND ' + @DestinationStr'

任何帮助将不胜感激.

针对 Jayvee 的以下建议:这不符合预期，或者我做错了什么！

IN RESPONSE TO Jayvee's SUGGESTION BELOW: This does not do what was intended or I do something wrong!

CREATE TABLE #temp
(
value varchar(10),
value2 varchar(10)
)

INSERT INTO #temp (value, value2) SELECT '5', '3'
INSERT INTO #temp (value, value2) SELECT NULL, '2'
INSERT INTO #temp (value, value2) SELECT '4', NULL
INSERT INTO #temp (value, value2) SELECT '6', '2'
INSERT INTO #temp (value, value2) SELECT '6', NULL
INSERT INTO #temp (value, value2) SELECT '6', '4'
INSERT INTO #temp (value, value2) SELECT NULL, '1'
INSERT INTO #temp (value, value2) SELECT NULL, '4'

SELECT value as [value],value2 as [value2]  FROM #temp
WHERE ISNULL(value,'') IN ('4')
AND ISNULL(value2,'') IN ('4')

DROP TABLE #temp

推荐答案

Try IS NULL

SELECT * FROM XYZ 
WHERE col1 IN ('a', 'b') 
AND col2 IN ('c', 'd') 
AND col3 IS NULL   -- Instead of IN (NULL)

这篇关于WHERE field1 IN (NULL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！