SQLite IS NULL

Summary: in this tutorial, you will learn how to use the SQLite IS NULL and IS NOT NULL operators to check whether a value is NULL or not.

Introduction to the SQLite IS NULL operator

NULL is special. It indicates that a piece of information is unknown or not applicable.

For example, some songs may not have the songwriter information because we don’t know who wrote them.

To store these unknown songwriters along with the songs in a database table, we must use NULL.

NULL is not equal to anything even the number zero, an empty string, and so on.

Especially, NULL is not equal to itself. The following expression returns 0:

NULL = NULL
Code language: SQL (Structured Query Language) (sql)

This is because two unknown information cannot be comparable.

Let’s see the following tracks table from the sample database:

The following statement attempts to find tracks whose composers are NULL:

SELECT
    Name, 
    Composer
FROM
    tracks
WHERE
    Composer = NULL;
Code language: SQL (Structured Query Language) (sql)

It returns an empty row without issuing any additional message.

This is because the following expression always evaluates to false:

Composer = NULL
Code language: SQL (Structured Query Language) (sql)

It’s not valid to use the NULL this way.

To check if a value is NULL or not, you use the IS NULL operator instead:

{ column | expression } IS NULL;
Code language: SQL (Structured Query Language) (sql)

The IS NULL operator returns 1 if the column or expression evaluates to NULL.

To find all tracks whose composers are unknown, you use the IS NULL operator as shown in the following query:

SELECT
    Name, 
    Composer
FROM
    tracks
WHERE
    Composer IS NULL
ORDER BY 
    Name;   
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

SQLite IS NOT NULL operator

The NOT operator negates the IS NULL operator as follows:

expression | column IS NOT NULL
Code language: SQL (Structured Query Language) (sql)

The IS NOT NULL operator returns 1 if the expression or column is not NULL, and 0 if the expression or column is NULL.

The following example finds tracks whose composers are not NULL:

SELECT
    Name, 
    Composer
FROM
    tracks
WHERE
    Composer IS NOT NULL
ORDER BY 
    Name;       
Code language: SQL (Structured Query Language) (sql)

This picture illustrates the partial output:

In this tutorial, you have learned how to check if values in a column or an expression is NULL or not by using the IS NULL and IS NOT NULL operators.

Was this tutorial helpful ?