查询带有IN(NULL)的SQL Server不起作用 [英] Query SQL Server with IN (NULL) not working

问题描述

当我定义用户定义的表类型"时，为:

When I define a "User-Defined Table Type", as:

CREATE TYPE [dbo].[BitType] AS TABLE(
    [B] [bit] NULL
)

我在此表变量中放入0和null. 然后我执行以下查询:

I place 0 and null in this table-variable. Then I do this query:

SELECT something FROM theTable WHERE item IN @theBitTypeTable

只会得到item=0而不是item is null

简单地说:SELECT something FROM theTable WHERE item IN (0, NULL)不起作用(尽管没有错误) 它必须是SELECT something FROM theTable WHERE item=0 OR item IS NULL

Simply put: SELECT something FROM theTable WHERE item IN (0, NULL) is not working (no error although) It has to be SELECT something FROM theTable WHERE item=0 OR item IS NULL

所以，我的问题是，如果我想使用用户定义的表类型，但是我还需要使用NULL值.如何正确执行查询以获取包含空项目的结果.

So, my question is, if I like to use User-Defined Table Type, but I also need to use NULL value. How can I perform the query correctly to get result include null item.

谢谢(顺便说一句，我使用的是MS SQL Server 2008 R2)

Thanks (btw, I use MS SQL Server 2008 R2)

推荐答案

具有NULL值的唯一有效比较操作是IS NULL或IS NOT NULL，其他操作始终返回false(实际上-未知，请参见@Damien_The_Unbeliever的注释)

The only valid comparison operations with NULL values are IS NULL or IS NOT NULL, others always return false (actually - Unknown, see the @Damien_The_Unbeliever's comment)

因此，请尝试以下操作

CREATE TYPE [dbo].[BitType] AS TABLE(
    [B] [tinyint] NOT NULL
)
GO
declare @theBitTypeTable BitType

insert @theBitTypeTable
VALUES(0), (2 /* instead of NULL*/)

SELECT something FROM theTable WHERE IsNull(cast(item as tinyint), 2) IN (select B from @theBitTypeTable)

这篇关于查询带有IN(NULL)的SQL Server不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！