SQL IFNULL — Hurdling Over Potholes

Relational databases store information in table cells — in columns that describe aspects of an item and rows that tie together the columns. Cells that contain NULLs — a special no-value marker that is not the same as the number zero or an empty string — present special processing challenges.

As there’s no SQL standard for extended functions, vendors have chosen varying names for their null-checking tool: IFNULL (MySQL), ISNULL (SQL Server), IsNull (Microsoft Access), NVL (Oracle). These functions have a syntax that parallels the MySQL variant:

IFNULL(expression, alternate_value)

This blog post continues to uncover NULL processing; see our SQL Coalesce blog entry for another way of handling NULLs.

NULL Values and the Problems They Present

The keyword NULL denotes a table cell that carries no value. “No value” is different from a cell which contains the number zero or an empty string; different in that computations can’t be performed on NULLs. One can add to a zero, or append a string to a null string:

SELECT
  ( 3.14 + 0) AS 'numeric',
  CONCAT( 'Good morning, ', '') AS 'string' ;

Trying the same operations with a NULL value always returns NULL, or may raise an error condition (depending on the database).

SELECT
  ( 3.14 + NULL) AS 'numeric',
  CONCAT( 'Good morning, ', NULL) AS 'string' ;

Clearly,  you should use defensive programming to intercept NULL values before use. SQL Coalesce offers one option, the ISNULL functions offer a more direct, conceptually obvious choice.

Provide a Useful Alternative to NULLs

In the vast majority of use cases, a bit of code encounters a NULL value that has eluded input data validation. Provide a useful alternative value for the code to use.

Consider a rockhound’s table of geological glasses:

SELECT * FROM geology ;

(The source code required to create and populate this table is given below.)

It’s reasonable to have NULL values in the alt_name column; presumably this is part of the table design and denotes there is no alternative name. The NULL in the quantity column seems problematic as NULL makes no sense when counting, especially when there’s a 0 in another of that column’s cells. (Perhaps the input data was incorrect, or the process reading the inputs was aborted prematurely by some error.)

Note please that MySQL provides both IFNULL and ISNULL functions, leading to confusion and, when a typo is involved, protracted frustration. Ensure you’re calling the intended function. ISNULL checks the NULL-ness whereas IFNULL provides an alternative value.

When extracting the data from this table (perhaps for a catalog), the alt_name column can be made more visually appealing:

SELECT
  name AS 'Primary Name',
  IFNULL(alt_name, '-= none =-') AS 'Alternative Name'
FROM geology ;

Similarly, the quantity column can be rehabilitated:

SELECT
  name AS 'Primary Name',
  IFNULL(quantity, 0) AS 'In Stock'
FROM geology ;

Conclusion

We’ve covered the problems posed by NULL values when used for numeric or string computation and how to use the SQL Ifnull function to provide a useful alternative value. In addition, we’ve made reference to related functionality in our SQL Coalesce blog post.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE geology (
name VARCHAR(50),
alt_name VARCHAR(50),
type VARCHAR(50),
location VARCHAR(99),
quantity INT
   ) ;

INSERT INTO
geology 
VALUES
( 'Libyan Desert glass', 'Great Sand Sea glass', 'impactite', 'Eastern Sahara', 123 ),
( 'Trinitite', 'Alamogordo glass', 'nuclear residue', 'Alamogordo, New Mexico', 0 ),
( 'Darwin glass', NULL, 'impactite', 'Queenstown, West Coast, Tasmania', 456 ),
( 'Edeowie glass', NULL, 'impactite', 'South Australia', NULL ) ;