Introduction to NULL

In the database world, NULL means missing information or not applicable. NULL is not a value, therefore, you cannot compare it with other values like numbers or strings.

The comparison of NULL with a value will always result in NULL. Additionally, NULL is not equal to NULL so the following expression returns NULL:

SELECT null = null AS result;

Output:

result-------- null(1 row)

IS NULL operator

To check if a value is NULL or not, you cannot use the equal to (=) or not equal to (<>) operators. Instead, you use theIS NULL operator.

Here’s the basic syntax of theIS NULL operator:

value IS NULL

TheIS NULL operator returns true if thevalue is NULL or false otherwise.

To negate theIS NULL operator, you use theIS NOT NULL operator:

value IS NOTNULL

TheIS NOT NULL operator returns true if the value is not NULL or false otherwise.

To learn how to deal with NULL in sorting, check out theORDER BY tutorial.

PostgreSQL offers some useful functions to handle NULL effectively such asNULLIF,ISNULL, andCOALESCE.

To ensure that a column does not contain NULL, you use theNOT NULL constraint.

PostgreSQL IS NULL operator examples

We’ll use theaddress table from thesample database:

Please note that thepsql program displaysNULL as an empty string by default. To change howpsql shows NULL in the terminal, you can use the command:\pset null null. It will display NULL as null.

1) Basic IS NULL operator example

The following example uses theIS NULL operator to find the addresses from theaddress table that theaddress2 column containsNULL:

SELECT  address,  address2FROM  addressWHERE  address2 ISNULL;

Output:

address        | address2----------------------+---------- 47 MySakila Drive    | null 28 MySQL Boulevard   | null 23 Workhaven Lane    | null 1411 Lillydale Drive | null(4 rows)

2) Using the IS NOT NULL operator example

The following example uses theIS NOT NULL operator to retrieve the address that has theaddress2 not NULL:

SELECT  address,  address2FROM  addressWHERE  address2 IS NOT NULL;

Output:

address                 | address2----------------------------------------+---------- 1913 Hanoi Way                         | 1121 Loja Avenue                       | 692 Joliet Street                      | 1566 Inegl Manor                       |

Notice that theaddress2 is empty, not NULL. This is a good example ofbad practice when it comes to storing empty strings and NULL in the same column.

To fix it, you can use theUPDATE statement to change the empty strings to NULL in theaddress2 column, which you will learn in theUPDATE tutorial.

Summary

• In databases, NULL means missing information or not applicable.
• TheIS NULL operator returns true if a value is NULL or false otherwise.
• Use theIS NOT NULL operator returns true if a value is not NULL or false otherwise.

Last updated onMay 14, 2024

On this page

• Introduction to NULL
• IS NULL operator
• PostgreSQL IS NULL operator examples
• Summary