NULL values ​​represent missing unknown data.

By default, table columns can store NULL values.

This chapter explains the IS NULL and IS NOT NULL operators.

SQL NULL value

If a column in the table is optional, then we can insert a new record or update an existing record without adding a value to the column record of. 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 inapplicable values.

NoteComments: NULL and 0 cannot be compared; they are not equivalent.

SQL NULL value processing

Please see the "Persons" table below:

##P_IdLastNameFirstNameAddressCity1HansenOla Sandnes2SvendsonToveBorgvn 23Sandnes3PettersenKari Stavanger


#Suppose the "Address" column in the "Persons" table is optional. This means that if you insert a record without a value in the "Address" column, the "Address" column will be saved with a NULL value.

So how do we test for NULL values?

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

We must use the IS NULL and IS NOT NULL operators.

SQL IS NULL

How do we select only records with NULL values ​​in the "Address" column?

We must use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
The result set is as follows Shown:

LastNameFirstNameAddressHansenOla##Pettersen

NoteTip: Always use IS NULL to find NULL values.

SQL IS NOT NULL

How do we select only the records that do not have a NULL value in the "Address" column?

We must use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

Result The set looks like this:


Kari
LastNameFirstNameAddress
SvendsonToveBorgvn 23

In the next section, we learn about ISNULL(), NVL(), IFNULL() and COALESCE() function.