SQL NULL Values - IS NULL and IS NOT NULL

SQL NULL value

---

NULL empty values represent missing unknown data.

By default, the table column can hold NULL values.

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

SQL NULL value

---

If the columns in the table are optional, we can insert a new record or update an existing record without adding a value to the column. T his means that the field will be stored as NULL.

NULL values are treated differently from other values.

NULL is a placeholder for unknown or inappropriate values.

Note: NULL and 0 cannot be compared;

SQL's NULL value processing

---

Take a look at the "Peoples" table below:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

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

So how do you test the value of null?

You cannot test NULL values with comparison operators, such as .

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

SQL IS NULL

---

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

Tip: Always use IS NULL to find NULL values.

SQL IS NOT NULL

---

How can we select a record that does 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'll look at the ISNULL(), NVL(), IFNULL(), and COALESCE() functions.