Handling NULL Values

What Is NULL Value?

In SQL, NULL indicates the absence of a value. It's not to be confused with an empty string or a zero value which represents an actual value.

Problem

You want to find all rows that are null for a particular column.

Solution

To determine whether a value is null, you should use .

is not equal to anything, therefore you cannot use or for testing whether a column is .

To determine whether a row has values, you must use . You can also use to find rows without a null in a given column.

You have rows that contain nulls and would like to return non-null values in place of those nulls.

COALESCE()

Use the function to substitute real values for nulls.

The function takes one or more values as arguments. The function returns the first non-null value in the list.

In the solution, the value of is returned whenever is not null. Otherwise, zero is returned.

CASE Statement

While you can use the statement to translate nulls into values, it's much easier and succinct to use .

IFNULL()

Use to return the specified value if the expression is NULL, otherwise return the expression.

If there's a null value in , then the expression "Why isn't there a sport here?" is returned, otherwise, the value in is returned.

NULL is the smallest value in the sorting order. If we order by a column containing NULL values, the rows with NULL values will be at the top.
In a clause, if a column contains rows with NULL values, the NULL values will be grouped into one group.
The aggregate functions (, , , , ) do not handle NULL values and eliminate them before performing any calculations. The only exception to this is the function where it returns the count of all rows, including rows containing NULL values.

Next Lesson

[Draftting] Ranking with Window Functions