SQL NULL Values

The SQL NULL value is a term used to indicates that a data value does not exist in a database.

A NULL signifies a missing value, and so has no value associated with it.

A NULL value in a table is a value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is a field that has been left blank during record creation.

Note: It is crucial to understand that a NULL value is different from a zero value or a field that contains spaces.

How to Test for NULL Values?

Using the classic comparison operators like =, <, >, <> will be impossible to test for NULL values.

A NULL value is a special value that signifies that there is no value. To manipulate NULL values, SQL offers two operators, IS NULL and IS NOT NULL.

IS NULL Operator

The IS NULL operator is used in SQL to check for a NULL value. It returns TRUE if a NULL value is found, otherwise it returns FALSE. It can be used with a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IS NULL operator is as follows:

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Operator

The IS NOT NULL operator is used in SQL to check for a value that is not NULL. It returns TRUE id a non NULL value is found, otherwise, it returns FALSE. Like the IS NULL operator, it can also be used with SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IS NOT NULL operator is as follows:

SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL

Demo table

Let us consider the following "Employee" table:

The IS NULL Operator Example

The following SQL statement will select all employees from the "Employee" table with a NULL value in the "dept_name" filed.

SELECT * 
FROM employee
WHERE dept_name IS NULL

After the execution, the output will look like this:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | NULL             |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | NULL             |
+--------+-------------+-----------+------------+------------+-----------+------------------+

The IS NOT NULL Operator Example

The following SQL statement will select all employees from the "Employee" table with a value in the "dept_name" field different from NULL.

SELECT *
FROM employee
WHERE dept_name IS NOT NULL;

After running the above query, the output will be as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1008   | Nelson      | Lopez     | 1999-05-26 | 2010-10-06 | 7300      | Marketing        |
+--------+-------------+-----------+------------+------------+-----------+------------------+