SQL IS NULL

 

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.

employees_table

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;
SQL IS example

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;
SQL IS NOT NULL example

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

Reactions

Post a Comment

0 Comments