Like this article? We recommend
Like this article? We recommend
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).