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)
NULL can be assigned, but using ' = NULL ', ' <> NULL ', or any other comparison operator, in an expression with NULL as a value, is illegal in SQL and should trigger an error. It can never be correct. The expression will always return NULL .
NULL has a special status in SQL. It represents the absence of value so, it cannot be used for comparison. If you use it for comparison, it will always return NULL. In order to use NULL value in NOT IN Clause, we can make a separate subquery to include NULL values.
The notation COUNT(*) includes NULL values in the total. The notation COUNT( column_name ) only considers rows where the column contains a non- NULL value.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With