MySQL NULL Values

What is a NULL Value?

A NULL value in MySQL represents a field with no value. It is used when data is missing or intentionally left blank.

• NULL vs. Zero or Spaces:

  • A NULL value indicates the absence of a value.
  • It is different from zero (0) or a field containing spaces (' '), which are actual values.

• Fields in a table can have NULL values if they are optional and no value is provided during record creation or update.

Testing for NULL Values

Why Comparison Operators Don’t Work

You cannot test for NULL values using comparison operators such as = or <. Instead, you must use the IS NULL or IS NOT NULL operators.

Syntax

Test for NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

Test for NOT NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

Example

Sample Table: Customers

Query 1: Find Records with NULL Values

List all customers where the Address field is NULL:

SELECT CustomerID, CustomerName
FROM Customers
WHERE Address IS NULL;

Result:

Query 2: Find Records with Non-NULL Values

List all customers where the Address field is NOT NULL:

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

Result:

Important Notes

1. Default Behavior: If no value is provided for a field that allows NULL values, it is automatically assigned NULL.
2. Null vs. Default Values: A column with a default value will use the default instead of NULL unless explicitly set to NULL.
3. Null Handling in Aggregates: Functions like COUNT ignore NULL values unless explicitly included.

By understanding and properly handling NULL values, you can maintain the accuracy and integrity of your data queries and operations.