Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

ISNULL, NULLIF, and GETANSINULL

The ISNULL function is similar to COALESCE, but accepts only two parameters. The first parameter will be checked, and if NULL value is found, it will be replaced with the second parameter. Furthermore, ISNULL requires that both parameters have the same (not just similar) data type.

For instance, we can return 0 instead of NULL for titles that do not have any royalties associated with them, as follows:

SELECT 
TOP 10 
title_id, 
ISNULL(royalty, 0) AS royalty 
FROM titles

Results:

title_id     royalty     
--------     ----------- 
BU1032          10
BU1111          10
BU2075          24
BU7832          10
MC2222          12
MC3021          24
MC3026           0
PC1035          16
PC8888          10
PC9999           0

The NULLIF function returns a NULL value if the two parameters it accepts are equivalent. NULLIF can be thought of as an opposite of ISNULL; for instance, we could use NULLIF if we wanted to return NULLs whenever royalty is 10:

SELECT 
TOP 10 
      title_id, 
      NULLIF(royalty, 10) AS royalty 
FROM titles	

Results:

title_id     royalty     
--------     ----------- 
BU1032         NULL
BU1111         NULL
BU2075           24
BU7832         NULL
MC2222           12
MC3021           24
MC3026         NULL
PC1035           16
PC8888         NULL
PC9999         NULL

The GETANSINULL function provides a quick way of checking whether column nullability is determined according to the ANSI 92 standard. (I have not found much use for this function; you can refer to online documentation if you want to learn more about GETANSINULL).

  • + Share This
  • 🔖 Save To Your Account