SQL IS NULL

Summary: in this tutorial, we will introduce you to the NULL concept and show you 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 that the data is missing in the database.

For example, if an employee does not have a phone number, you can store it as an empty string. However, if we don’t know his or her phone number at the time we insert the employee record, we will use the NULL value for the unknown phone numbers.

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

The following statement returns a null value.

SELECT NULL = 5;

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

SELECT NULL = NULL;

In this example, the result is a null value.

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

SELECT
	employee_id,
	first_name,
	last_name,
	phone_number
FROM
	employees
WHERE
	phone_number = NULL;

The IS NULL and IS NOT NULL operators

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

expression IS NULL;

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 NULL;

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 will 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;

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;

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