Isnull

Transact-SQL
1

Hello, I have the following ISNULL, what does this do exactly? is it replacing 0 for null and then 1 for null?
CASE
WHEN ISNULL(IsActive, 0) = 1
Thanks,
Pasi

2

That is part of a CASE expression - and the ISNULL is useless in this situation. The CASE is looking for the condition 'IsActive = 1'

The ISNULL converts any NULL values in the IsActive column to a value of 0 and then compares that to 1 - which is false. The same thing will happen if you just use IsActive = 1.

3

Not necessarily. The original condition will be FALSE. The condition of IsActive = 1 when IsActive is NULL will also be NULL rather than FALSE. That may make a difference in the results of the code (or not, depending on the specific code used).

1 Like
4

WHEN ISNULL(IsActive, 0) = 1

Doesn't matter if it is null, blank or 0, it will not be equal to 1.

Therefore the ISNULL is useless here because the only condition that will evaluate to true is when the column is equal to 1.

Now if that was checking for equal to 0, then it would evaluate nulls, empty strings and 0 to be true.

1 Like
5

Thanks Jeff!

6

Thanks Scott!

7

Again, yes, only 1 will evaluate to True. But NULL will evaluate to False with the original code but("unknown") if the code is just "IsActive = 1".
For example:


--run this
DROP TABLE IF EXISTS #test1;
CREATE TABLE #test1 ( column1 int NULL, CHECK (ISNULL(column1, 0) = 1) );
INSERT INTO #test1 VALUES(NULL);

--...then run this
DROP TABLE IF EXISTS #test2;
CREATE TABLE #test2 ( column1 int NULL, CHECK (column1 = 1) );
INSERT INTO #test2 VALUES(NULL);

The first fails, the second doesn't.

1 Like
8

Again - in the question the OP asked about the ISNULL in a CASE expression and not about a CHECK constraint.

Your example is not valid though - since the column allows NULL then you should be able to insert a NULL OR 1 into that column. The check constraint using ISNULL prevents a NULL value from being inserted into that column where the check constraint not using ISNULL allows the NULL value.

If the intent is to prevent anything except a 1 from being entered - then changing the column to NOT NULL and the check constraint works as expected and the only value that can be entered is a 1.

Going back to the logic:

ISNULL(column, 0) = 1

If column is null - the above evaluates to 0 = 1 (false)
If column is blank - the above evaluates to 0 = 1 (false - implicit conversion of '' to int = 0)
If column is 0 - the above evaluates to 0 (false)

column = 1

If column is null - the above evaluates to unknown = 1 (unknown - not true or false)
If column is blank - the above evaluates to 0 = 1 (false - implicit conversion of '' to int = 0)
If column is 0 - the above evaluates to 0 = 1 (false)

Since we can get the same results without using the function - why would you include it?

1 Like
9

Hi

It's taking care of null values

Trying to exclude nulls

Another way is
Active is not null

1 Like
10

IsActive = 1 already excludes NULL values. No need to check for a null value to exclude it because NULL can never be equated to anything.

1 Like