T-SQL 101: 79 Replacing NULL values with ISNULL, COALESCE

The next set of introductory T-SQL topics that I want to talk about is how to change data and/or data types. The first issue is dealing with NULL values.

Once you get your head around the idea that NULL means the lack of a value (NULL isn’t a value), you might need to replace NULL values with something else. While you could just do that with a CASE statement, T-SQL provides two built-in functions for doing this.

The most straightforward function is ISNULL. It takes two parameters. If the first parameter isn’t NULL, then it is returned. If the first parameter value is NULL, then the second parameter value is returned. In the example above, the dbo.Products table is being queried, and a MinimumQuantity is being calculated. If there is an OuterQuantity, it is returned. Otherwise, 24 is returned.

ISNULL is a T-SQL language extension. It is not an ANSI SQL function. The ANSI SQL option is COALESCE.

COALESCE is similar but it can take a list of parameter values, not just two. It returns the first value that isn’t NULL. In the example above, for the Quantity column, if the OrderQuantity isn’t NULL, it is returned. If OrderQuantity is NULL, then OuterQuantity is checked. If it isn’t NULL, it’s returned. If both of the columns are NULL, then the hard-coded value 24 is returned.

Apart from the number of parameters, and the ANSI compliance, there is one other major difference between these two functions. The data type returned by ISNULL is the data type of the first parameter. With COALESCE, the data type of all parameters is considered, and the one with the highest precedence is returned. So if you have two decimal values and an integer, a decimal value would be returned.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2021-01-04