Query SQL Server with IN (NULL) not working

Question

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

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

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

SELECT something FROM theTable WHERE item IN @theBitTypeTable

Will only get item=0 not 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

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.

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

Answer 1

Null does not equal null in SQL Server (and most other database management systems). You would need to do a coalesce on the joined column, and use a sentinel value to represent nulls.

Answer 2

There is a cheat use isnull on the item being compared.

eg

SELECT something 
FROM theTable 
WHERE ISNULL(item,0) IN (0)

Answer 3

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)

So, try the following

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)