SQL IS Operator

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

Introduction to SQL IS operator

In databases, NULL is unknown, not applicable or missing information, therefore, you cannot use the comparison operators (=, >,<, etc.,) to check whether a value is NULL or not.

For example, the expression A = NULL, B <> NULL or NULL = NULL returns NULL because NULL values cannot be compared. Fortunately, SQL provides the  IS operator to check whether a value is NULL.

The following illustrates the syntax of the  IS NULL operator:

WHERE expression IS (NOT) NULLCode language: PHP (php)

The  IS NULL returns TRUE if the expression is NULL, otherwise it returns FALSE.

If you use the NOT operator, the expression returns a TRUE if the expression is not NULL, otherwise it returns FALSE. Remember that the NOT operator is used to inverse the predicate.

We often use the  IS NULL in the WHERE clause of the SELECT statement or the subquery.

SQL IS operator examples

You can use IS NULL to check if the supplier does not have a fax so that you can communicate with them via an alternative communication channel. The following query accomplishes this:

SELECT 
    companyName, fax
FROM
    suppliers
WHERE
    fax IS NULL;Code language: PHP (php)
SQL IS NULL

To print the fax list of the suppliers, you can use  IS NOT NULL as follows:

SELECT 
    companyName, fax
FROM
    suppliers
WHERE
    fax IS NOT NULLCode language: PHP (php)

In this tutorial, we have shown you how to use the  IS NULL or  IS NOT NULL to check whether a value is NULL or not.