SQL NULL Values

What is a NULL Value?

A field containing a NULL value indicates that it has no assigned value.

For optional fields in a table, it’s permissible to insert or update records without providing a value for such fields. Consequently, the field will be stored with a NULL value.

Please note: A NULL value differs from a zero value or a field filled with spaces. A field containing a NULL value indicates it was intentionally left blank during record creation.

How to Test for NULL Values?

Testing for NULL values using comparison operators like =, <, or <> is not possible.

Instead, we need to utilize the IS NULL and IS NOT NULL operators.

IS NULL Syntax

SELECT column_names
FROM table_name

  WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names
FROM table_name

  WHERE column_name IS NOT NULL;

Demo Database

Here is an excerpt from the Customers table that has been referenced in the examples:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

The IS NULL Operator

The IS NULL operator is employed to assess empty values (NULL values).

The subsequent SQL query lists all customers with a NULL value in the “Address” field:

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Tip: Always use IS NULL to look for NULL values.

The IS NOT NULL Operator

The IS NOT NULL operator is utilized to examine non-empty values (NOT NULL values).

The subsequent SQL query lists all customers with a value present in the “Address” field:

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;