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 TRUEif 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.