Skip to content
Search
Generic filters
Exact matches only

A Complete Beginner’s Guide to Deal with NULL Values in SQL

  • IS NULL: Return rows that contain NULL values

Syntax: expression IS NULL

SELECT 
ID,
Student,
Email1,
Email2
FROM tblSouthPark
WHERE Email1 IS NULL AND Email2 IS NULL
ORDER BY
ID

The above query yields all records where both Email1 and Email2 columns are NULL.

  • IS NOT NULL: As the name suggests, it is the inverse of IS NULL.

Syntax: expression IS NOT NULL

SELECT 
ID,
Student,
Email1,
Email2
FROM tblSouthPark
WHERE
Email1 IS NOT NULL AND Email2 IS NOT NULL
ORDER BY
ID

The above query yields all records where both Email1 and Email2 columns are NOT NULL.

Syntax: ISNULL(expression, value)

Let us understand this by running a simple query on our SouthPark table.

SELECT 
ID,
Student,
ISNULL(Father,‘Missing’) AS Father
FROM tblSouthPark
ORDER BY ID

This query yields the following result:

Since the expression in the case of Eric Cartman evaluated to NULL, the ISNULL function returned the value, ‘Missing’. For other students, the expression was not NULL so it returned the expression itself.

Syntax: COALESCE(expression[1…..n])

For example, SELECT COALESCE(NULL, NULL, 'red', 'blue', NULL) returns ‘red’ as it is the first non-null value. If all the values are NULL, the COALESCE function will return NULL.

Let us use the COALESCE function on our SouthPark table.

SELECT 
ID,
Student,
COALESCE(Email1, Email2, 'N/A') AS Primary_Email
FROM tblSouthPark
ORDER BY ID

The above query yields the following result:

As expected, since both Email1 and Email2 are null in Kenny’s case, the COALESCE function returns ‘N/A’ as the Primary_Email. For Stan, Email2 is returned as the Primary_Email as it is the first non-NULL value in the COALESCE function. For others, Email1 is returned as the Primary_Email.

Here is the general syntax for the CASE expression.

CASE
WHEN expression_1 THEN result_1
WHEN expression_2 THEN result_2
.
.
.
WHEN expression_n THEN result_n
ELSE else_expression
END

We can generate the same output we got using COALESCE above by using CASE.

The query will look like this:

SELECT 
ID,
Student,
CASE

WHEN Email1 IS NOT NULL THEN Email1
WHEN Email2 IS NOT NULL THEN Email2
ELSE 'N/A'
END AS Primary_Email
FROM tblSouthPark
ORDER BY ID

This query yields the same output:

Note: TheCASE expression is syntactically similar to the COALESCE function. In fact, COALESCE is like a shorthand forCASE. The former is short and simple, but the latter is more clear and easy to understand.

Syntax: NULLIF(expression_1, expression_2)

NULLIF('Red','Orange') – Returns Red
NULLIF(0,NULL) – Returns 0
NULLIF(0,0) – Returns NULL

WhereNULLIF comes in handy is in case of data that contains a mixture of null and empty strings in a column.

Let us understand this with an example.

We see that the Phone column in our table contains both NULL and empty strings.

We can standardize this by changing the empty string to NULL using NULLIF

SELECT 
ID,
Student,
NULLIF(Phone,'') AS Phone
FROM tblSouthPark
ORDER BY ID

The above query yields:

Another good use case for NULLIF is to prevent “division by zero” errors.

var1 = 1
var2 = 0
var1/var2 – This will generate a "division by zero" errorvar1/NULLIF(var2,0)--This doesn't trigger a "division by zero" error

In the second case, we do not get a “division by zero” error as NULL is returned in the denominator in place of 0.