CODEWITHSUNDEEP
sqlsql-server-2008
Control Freak
Control Freak

Reputation: 13233

IsNull() isn't returning all values

Lets say I have this table

TABLE (Name varchar, Value varchar, Active bit)

And data

'Name1', '1', NULL
'Name2', '1', NULL
'Name3', '1', 1
'Name4', '1', NULL
'Name3', '2', NULL

This script I'm trying to run, but it only returns 1 row.

Select Name,Value FROM Table WHERE Active = IsNull(1,Active)

Shouldn't it be returning all the NULL and 1 rows in the table? Why does this not work as expected?

Upvotes: 0

Views: 116

Answers (2)

John Woo
John Woo

Reputation: 263723

It should be:

WHERE Active IS NULL OR Active = 1

ISNULL() function is used to replace NULL value into your desired value.

The expression ISNULL(1, Active) will always return 1 since 1 is not null. The documentation on ISNULL()

Replaces NULL with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value )

So I think this is the expression you want to build:

WHERE ISNULL(Active, 1) = 1

Upvotes: 3

radar
radar

Reputation: 13425

ISNULL will use second param if first param is NULL Here first param is 1 which is not NULL , so it evaluates to 1 So only rows with value 1 will match

Upvotes: 1

Related Questions