Interesting Tidbit on ISNULL()

ISNULL() replaces NULL with the value you provide. For example:  

DECLARE @var int
SET
@var = NULL
select ISNULL(@var, 100) as [Var]

This returns 100.  Simple and easy, right?  ISNULL is often used to take care of NULLs in mathematical sets. For example:

SELECT AVG(ISNULL(Charge, 0))
FROM BankAccount

This will change all NULLs to 0, so we can use them in the AVG function. (Remember, a NULL in any mathematical function will return NULL for the result). But what about something like this:

DECLARE @feathers int
SELECT
@feathers = ISNULL(featherCount,0)
FROM Bird
WHERE BirdID = 17

That's fine if the featherCount for BirdID=17 is a number, or is null.  But what if there is no row with BirdID = 17?  @feathers will NOT be set to 0, as you might expect.  It will be = NULL.

Bottom line: ISNULL returns NULL, not the specified value, when 0 rows are returned.

 

-Jennifer McCown, http:/www.MidnightDBA.com

1 thought on “Interesting Tidbit on ISNULL()

  1. Pingback: “Thing IS NOT NULL” isn’t the same as “Thing != NULL” | The MidnightDBA Star-Times

Comments are closed.