SQL NULL Value

SqlNULL Value

A NULL value represents an unknown data that is missing.

By default, the columns of the table can hold NULL values.

This chapter explains the is null and is not NULL operators.

SQL NULL Value

If a column in the table is optional, we can insert a new record or update an existing record without adding a value to the column. This means that the field will be saved with a NULL value.

NULL values are handled differently than other values.

NULL is used as a placeholder for unknown or non-applicable values.

Note: cannot compare NULL and 0; they are not equivalent.

NULL Value handling for SQL

Take a look at the "Persons" table below:

City

p_id	LastName	FirstName	Address
1	Hansen	Ola		Sandnes
2	Svendson	Tove	BORGVN 23	Sandnes
3	Pettersen	Kari		Stavanger

If the "Address" column in the "Persons" table is optional. This means that if you insert a record with no value in the Address column, the address column is saved with a NULL value.

So how do we test for NULL values?

You cannot use a comparison operator to test for NULL values, such as =, <, or <>.

We must use the is null and is not NULL operator.

SQL is NULL

How do we just pick a record with a NULL value in the "Address" column?

We must use the IS NULL operator:

SELECT  from Persons WHERE  is NULL

The result set is as follows:

LastName	FirstName	Address
Hansen	Ola
Pettersen	Kari

tip: always use is NULL to find null values.

SQL is not NULL

How do we just pick a record that doesn't have a NULL value in the Address column?

We must use the is not NULL operator:

SELECT  from Persons WHERE  is  not NULL

The result set is as follows:

SQL NULL Value