What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax

 SELECT  column_names 
FROM  table_name
 WHERE  column_name IS NULL ;

IS NOT NULL Syntax

span style="color: blue"> SELECT  column_names
FROM  table_name
  WHERE  column_name IS NOT NULL ;

Demo Employee table

This employee table is used for examples:

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all employees with a NULL value in the "Address" field:

.

Example

 SELECT Employee_name,Address 
FROM  employee
 WHERE Address IS NULL ;

You can click on above box to edit the code and run again.

Output

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non- empty values (NOT NULL values).

The following SQL lists all employees with a value in the "Address" field:

Example

 SELECT Employee_name,Address
 
FROM  employee

 WHERE Address IS NOT NULL ;

You can click on above box to edit the code and run again.

Output