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 ) I place 0 and null in this table-variable. Then I do this query: SELECT s

Solution 1:

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] ASTABLE(
    [B] [tinyint] NOTNULL
)
GO
declare@theBitTypeTable BitType

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

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

Solution 2:

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.

Solution 3:

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

eg

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