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, NULLis unknown, not applicable, or missing information, therefore, you cannot use the comparison operators (=, >,<, etc.) to check whether a value is NULLor not.

For example, the expression A = NULL, B <> NULL or NULL = NULL returns NULLbecause NULLvalues 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: SQL (Structured Query Language) (sql)

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

If you use the NOToperator, the expression returns a TRUE if the expression is not NULL, otherwise, it returns FALSE. Remember that the NOToperator 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 NULLto 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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.

SQL IS Operator

Introduction to SQL IS operator

SQL IS operator examples

Databases