WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL NULL Values



NULL values ​​represent missing unknown data.

By default, columns of a 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 new records or update existing records without adding values ​​to that 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 inappropriate values.

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


SQL NULL value processing

Look at the "Persons" table below:

P_Id LastName FirstName Address City
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?

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 just select 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 looks like this:

LastName FirstName Address
Hansen Ola
Pettersen Kari

NoteTip: Always use IS NULL to find NULL values.


SQL IS NOT NULL

How can we just select 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

The result set looks like this:

LastName FirstName Address
Svendson Tove Borgvn 23

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