It’s Wednesday and that means another SQL/Oracle post. Today we’ll be discussing NULL Values, which can sometimes be a real pain. Don’t worry though there’s a simple solution. Simply replace the NULL value with another.

Comparing a column with NULL and replacing with another value is really simple. There are built in functions for replacing NULL values. I’m not going to discuss the ANSI standard COALESCE here. If you want to know more about it you can find it on Bing.

SQL Server

SQL Server’s built in function for replacing NULL values is named ISNULL(). This very simple function takes two parameters, tests the first for NULL and if NULL is found substitutes the second parameter for the first.

SELECT ProductID, Weight, ISNULL(Weight, 0.00)
FROM Production.Product

Result

1, NULL, 0.00

2, NULL, 0.00

…

SELECT ISNULL(NULL, ‘XYZ’)

Result

XYZ

With the ISNULL function you can pass NULL as the first parameter; though, I’m not sure why you would want to.

SQL Server 2012 added the IIF function which works just like the MS Office IIF. Though I don’t see this widely used. Typically you’ll see ISNULL and COALESCE.

Oracle

The Oracle function may not be the one you’d think of first. Oracle has a function named NULLIF and it does something completely different. NULLIF compares to values and if they are the same returns NULL.

Replacing NULL values in Oracle is done with the NVL function, which works the same as SQL Server’s ISNULL described above.

Pass a column and if the column is NULL the function will return the value of the second parameter.

It’s worth mentioning that Oracle also has a function named NVL2 which allows you to pass in 3 parameters. The first parameter is compared and if NULL then the 3rd parameter is returned and if not NULL then the 2nd parameter is returned.

Conclusion

Both RDBMS platforms have the ANSI standard function for comparing and replacing NULL values (COALESCE). Both platforms have two native functions for this task that behave in nearly the same manor. So if you’re switching between the two or learning one or the other, this isn’t an area you need to worry about.