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)

Curcuma

Answer 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] 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)

Loath

Good idea, so instead of create BitType, maybe I need to create a TinyintType table variable, then I do not need to cast. Will try – Curcuma

Yup, the datatype's size is just the same, so you will not lost in performance, but'll gain in versatility – Loath

The other comparisons don't return false, they return unknown. The difference is readily apparent if you place a NOT() around such a comparison - NOT (1=NULL) isn't true. – Trousers

@Damien_The_Unbeliever: The other comparisons don't return UNKNOWN, they evaluate to UNKNOWN e.g. postgreSQL has partial support for Standard SQL's BOOLEAN type but uses NULL as a placeholder for UNKNOWN, so it may be said for that product that such comparisons evaluate to UNKNOWN (a logical value) but return NULL (a data value). – Gunplay

Answer 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.

Baty

Answer 3

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

eg

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

Ioves