CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ryan
Ryan

Reputation: 3982

Why is isnull(<field>, 0) matching NULL in SQL statement?

In SQL Server 2008, I run the following SQL:

SELECT id, old_tgt_price, new_tgt_price 
FROM inst i 
WHERE inst_id IN (281, 229) 
AND is_latest = 1

With the following result:

id          old_tgt_price   new_tgt_price
4492        462             NULL
4487        2700            2500

Nothing wrong there. If I add a check that the old and new price are not equal (whilst expecting some values to be NULL):

SELECT id, old_tgt_price, new_tgt_price 
FROM inst i 
WHERE inst_id IN (281, 229) 
AND is_latest = 1 
AND (isnull(old_tgt_price, 0) != isnull(new_tgt_price, 0))

I get the same result. For the record with id 4492, surely the AND (isnull(old_tgt_price, 0) != isnull(new_tgt_price, 0)) clause should fail.

Why is the record 4492 still returned in the second result set?

Upvotes: 1

Views: 258

Answers (2)

Tyler Ferraro
Tyler Ferraro

Reputation: 3772

The statement added at the bottom is saying select that row if old_tgt_price != new_tgt_price and is changing both of them to 0 if they are null. old_tgt_price isn't null so its value is 462. The new_tgt_price is null so it gets the value of 0. They are not equal so it prints the result.

What is it you're trying to do? Do you not want it to print null results?

Upvotes: 2

Piotr Auguscik
Piotr Auguscik

Reputation: 3681

Your null will be changed for 0 so your condition will look like this: 462 != 0 which is TRUE

Upvotes: 0

Related Questions