The page explains how SQL handles NULL values, focusing on the concepts, testing, and syntax used for identifying NULL and NOT NULL entries in a database:

• What is a NULL value?
  A NULL in SQL represents a field with no value (not zero or spaces). It indicates that data for the field was left blank when the record was created.

• Testing for NULL:
  Standard comparison operators like =, <, >, or <> cannot test for NULL. Instead, SQL uses the operators IS NULL and IS NOT NULL.

• Syntax examples:

  • To select records where a column is NULL:

  SELECT column_names
  FROM table_name
  WHERE column_name IS NULL;
  

  • To select records where a column is NOT NULL:

  SELECT column_names
  FROM table_name
  WHERE column_name IS NOT NULL;
  

• Usage tip:
  Always use IS NULL to check for NULL values in SQL.

• Examples:
  The page provides example queries that return customers with either NULL or NOT NULL values in the "Address" field.

• Common confusions:
  NULL is not the same as zero (0) or a field with only spaces.

There’s also a summary quiz and a video, reinforcing the differences between NULL, zero, and blank fields, and emphasizing correct NULL checking syntax in SQL.[1]

1

SQL NULL Values

What is a NULL Value?

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

We will have to use the IS NULL and IS NOT NULL operators instead.

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

Below is a selection from the Customers table used in the examples:

The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

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 used to test for non-empty values (NOT NULL values).

The following SQL lists all customers with a value in the "Address" field:

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