Dealing with NULLs in SQL Server

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN.

Let’s take an example. Assuming an attribute (column) in our relation (Table) represents Tax Identification Number (TIN) of a set of Small Business owners in Accra, Ghana. The column for each business owner’s record will be populated with his/her TIN and we can use this column to determine some other attribute such as whether he/she is up to date in Tax payments. However, there is are two extra possibilities in this use case:

1. The Business Owner has a TIN and is up to date.
2. The Business owner does not have a TIN and is (obviously) not up to date.

The above describes what Dr. Codd called four-valued predicate logic. The SQL standard, however, simplifies these two additional conditions by defining it as unknown i.e. NULL. We do not know the TIN of the business owner and cannot determine any other value from the attribute of the affected Business Owner. So, NULL is UNKNOWN and is the third value in standard three-valued predicate logic.

NULL is Special

The definition of NULL necessitates the treatment of the marker in a different way from actual values. The following are examples:

1. There is no such thing as the filter “WHERE = NULL;”. The correct expression would “WHERE IS NULL;”. The same goes for the converse expression.
2. When sorting in ascending order SQL you can choose to list NULLs first or last. The default is to list NULLs first.
3. You cannot compare NULL values. This should be obvious since we have said that NULL is UNKNOWN.
4. When a concatenation attempt involves a NULL column, the result is NULL.

Common NULL-Related Functions

The following are three common NULL related functions in SQL Server

ISNULL

ISNULL – Replaces NULL with a specified replacement value. Listing 1 and Fig 1 show simple examples of ISNULL.

-- Listing 1: Simple Example of ISSNULL
SELECT ISNULL (NULL, 3) NULLREPLACEMENT;
SELECT ISNULL (NULL,'GREEN') NULLREPLACEMENT;
SELECT ISNULL (NULL,'2018-12-25') NULLREPLACEMENT;

Fig 1: Simple Example of ISNULL

NULLIF

NULLIF returns NULL is the value of the two arguments are equal.

-- Listing 2: Simple Example of NULLIF
SELECT NULLIF(3,3) AS NULLIFF;
SELECT NULLIF(3,5) AS NULLIFF;
SELECT NULLIF('RED','RED') AS NULLIFF;
SELECT NULLIF('GREEN','RED') AS NULLIFF;

Fig. 2: Simple Example of NULLIF

COALESCE

COALESCE returns the first non-NULL value from the list provided. Listing 1 shows examples of this and Fig 1 shows the output of the queries.

-- Listing 3: Simple Example of COALESCE
SELECT COALESCE (NULL,'','GREEN','','') AS NULLRESPONSE;
SELECT COALESCE (NULL,'GREEN','HOPE','') AS NULLRESPONSE;
SELECT COALESCE (1,'','GREEN','','') AS NULLRESPONSE;

Fig 3: Simple Example of Coalesce

Notice that these simple examples expose to use the nature of NULL. NULL and blank space is NOT the same. In the first statement, COALESCE returns a blank space showing us that a blank space is the first non-NULL value in the list.

Differences between ISNULL and COALESCE

The differences between ISNULL and COALESCE has been the subject of several online articles as well as books such as those listed in the references section. These differences are summarized as follows:

1. ISNULL is proprietary to SQL Server while COALESCE is an ANSI standard function. This implies that for portability, COALESCE is preferred.
2. ISNULL takes only two arguments while COALESCE can take more than two arguments.
3. The data type of the value returned by ISNULL is determined by the data type of the first argument while the data type of the value returned by COALESCE is determined by the data type in the list with the highest precedence.
4. When both functions are used with subqueries, ISNULL performs better because COALESCE is internally translated to a CASE expression making it tend to repeat scans.

Itzik Ben-Gan also explores other implications of the conversion of COALESCE to a case expression in his article the link to which is provided at the end of this article.

Sample Use Cases

We want to display on a web portal the list of customers based on certain criteria from the table we are creating in Listing 4. Tasks 1 and 2 give to possible requirements and we use ISNULL and COALESCE to meet the requirements.

--Listing 4: Table Creation Script
CREATE TABLE CUSTOMER 
(ID INT IDENTITY (1,1)
,FIRSTNAME VARCHAR(50)
,LASTNAME VARCHAR(50)
,SEX CHAR(1)
,ADDRESS VARCHAR(300)
,FIRSTTRANDATE DATETIME
,PHONENUMBER1 BIGINT
,PHONENUMBER2 BIGINT
,PHONENUMBER3 BIGINT);
GO

INSERT INTO CUSTOMER VALUES ('KENNETH','IGIRI','M','ACCRA, GHANA',GETDATE(),'0245335678','0555335678',NULL);
INSERT INTO CUSTOMER VALUES ('RICHARD','HANO','M','BUDAPEST, HUNGARY',GETDATE(),'889189400122',NULL,NULL);
INSERT INTO CUSTOMER VALUES ('GEORGINA','APPIAH','F','ACCRA, GHANA','09-16-2018','02456665678','0275339678',NULL);
INSERT INTO CUSTOMER VALUES ('HOWARD','KLEVIA',NULL,'HAGUE, SWITZERLAND','02-16-2017','3499285782',NULL,NULL);
INSERT INTO CUSTOMER VALUES ('ZEN','GREGOR',NULL,'SHANGHAI, CHINA','06-23-2018','0245335678','0555335678',NULL);
INSERT INTO CUSTOMER VALUES ('IHEOMA','AWA','F','LAGOS, NIGERIA',GETDATE(),'0245335678','0555335678',NULL);

Fig. 4 Sample Table

Task 1: Return the List of all customers who did not provide an alternate phone number.

--Listing 5: Table List of Customers with no Secondary Phones
--A: The Simple Answer
SELECT * FROM CUSTOMER WHERE PHONENUMBER2 IS NULL ;

--B: Presenting the Result Set Better
SELECT 
FIRSTNAME
,LASTNAME
,ADDRESS
,FIRSTTRANDATE
,PHONENUMBER1 AS [PRIMARY PHONE NUMBER]
,ISNULL(CAST(PHONENUMBER2 AS VARCHAR), 'NO SECONDARY PHONE') AS [SECONDARY PHONE NUMBER]
FROM CUSTOMER WHERE PHONENUMBER2 IS NULL ;

Using COALESCE (or ISNULL), we can present the information required much better using a text that says ‘No Secondary Phone’.

Fig. 5 Result Set for Listing 5

Task 2: Return the List of all customers, their primary Phone Number, and any other alternate phone number.

--Listing 6: Table List of Customers with an Other Alternate Number
SELECT 
FIRSTNAME
,LASTNAME
,ADDRESS
,FIRSTTRANDATE
,PHONENUMBER1 AS [PRIMARY PHONE NUMBER]
,COALESCE(CAST(PHONENUMBER2 AS VARCHAR),CAST(PHONENUMBER3 AS VARCHAR), 'NO OTHER PHONE') AS [OTHER PHONE NUMBER]
FROM CUSTOMER  ;

Fig. 6 Result Set for Listing 6

In this case, ISNULL is not an option since we are passing three arguments.

Conclusion

In this article, we have discussed the concept of NULL as it relates to three-valued predicate logic and described popular functions we use in SQL Server to deal with data sets containing NULLs. We have also seen examples of how these functions can be used. Many other references delve deeper into the use, benefits, and limitations of these functions. I would highly recommend Itzik Ben-Gan’s books and blogs as good sources of information.

References

• NULLIF (Transact-SQL)
• COALESCE (Transact-SQL)
• Itzik Ben-Gan’s article