SQL IS NULL

Summary: in this tutorial, you learn about the NULL and how to use the SQL  IS NULL and IS NOT NULL operators to test if an expression is NULL or not.

What is NULL

NULL is special in SQL. NULL indicates that the data is unknown, inapplicable, or even does not exist. In other words, NULL represents the missing data in the database.

For example, if employees do not have phone numbers, you can store their phone numbers as empty strings.

However, if you don’t know their phone numbers when you save the employee records, you need to use the NULL for the unknown phone numbers.

The NULL is special because any comparisons with a NULL can never result in true or false, but in a third logical result, unknown.

The following statement returns NULL:

SELECT NULL = 5;Code language: PHP (php)

Try It

The NULL value is not even equal to itself, as shown in the following statement:

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

Try It

In this example, the result is NULL.

You cannot use the comparison operator equal to (=) to compare a value to a NULL value. For example, the following statement will not return the correct result:

SELECT
	employee_id,
	first_name,
	last_name,
	phone_number
FROM
	employees
WHERE
	phone_number = NULL;Code language: SQL (Structured Query Language) (sql)

Try It

The IS NULL and IS NOT NULL operators

To determine whether an expression or column is NULL or not, you use the IS NULL operator as follows:

expression IS NULLCode language: SQL (Structured Query Language) (sql)

If the result of the expression is NULL, IS NULL operator returns true; otherwise, it returns false.

To check if an expression or column is not NULL, you use the IS NOT operator:

expression IS NOT NULLCode language: PHP (php)

The IS NOT NULL returns false if the value of the expression is NULl; otherwise, it returns true;

SQL IS NULL and IS NOT NULL examples

In these examples, we’ll use the  employees table from the sample database for the demonstration.

To find all employees who do not have the phone numbers, you use the IS NULL operator as follows:

SELECT
	employee_id,
	first_name,
	last_name,
	phone_number
FROM
	employees
WHERE
	phone_number IS NULL;Code language: PHP (php)

Try It

To find all employees who have phone numbers, you use IS NOT NULL as shown in the following statement:

SELECT
	employee_id,
	first_name,
	last_name,
	phone_number
FROM
	employees
WHERE
	phone_number IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

Try It

Now you should understand the NULL concept and know how to use the SQL IS operator to check whether a value is NULL or not