SQL NULL Values

In SQL, NULL represents a missing, unknown, or absent value. It is not the same as zero (0) or an empty string (''). NULL simply means "no value has been provided for this field."

Think of it like a form where someone left a field blank. The form was submitted, but that particular field was not filled in. That blank field is NULL in the database.

The Reference Table

In the following Students table, some values are missing (NULL):

Here, Priya's City is unknown, Arjun's Age and Phone are missing, and Sneha's Phone is missing.

NULL vs Zero vs Empty String

For example, a student with Age = 0 means their age is recorded as zero, while a student with Age = NULL means their age was never entered.

Checking for NULL: IS NULL

To find rows where a column has no value, use IS NULL. Using = NULL does not work in SQL — it will never return any rows.

-- Correct way to check for NULL
SELECT * FROM Students
WHERE City IS NULL;

Result:

Checking for Not NULL: IS NOT NULL

IS NOT NULL finds rows where a column has a value (not missing).

SELECT * FROM Students
WHERE Phone IS NOT NULL;

Result: Ravi Sharma, Priya Mehta, and Rohit Das — the three students who have a phone number on record.

Why = NULL Does Not Work

-- This does NOT work — returns no rows
SELECT * FROM Students WHERE City = NULL;

-- This is correct
SELECT * FROM Students WHERE City IS NULL;

NULL is not a regular value — it cannot be compared using = because NULL equals nothing, not even itself. SQL requires the special IS NULL syntax for this check.

Using COALESCE to Handle NULL

The COALESCE() function returns the first non-NULL value from a list of expressions. It is commonly used to replace NULL values with a meaningful default in query results.

SELECT StudentName, COALESCE(City, 'City Not Available') AS City
FROM Students;

Result:

Where the City is NULL (Priya Mehta), it is displayed as 'City Not Available' instead.

Using IFNULL (MySQL Specific)

In MySQL, IFNULL() works similarly to COALESCE() for a single column:

SELECT StudentName, IFNULL(Phone, 'No Phone') AS Phone
FROM Students;

If the Phone is NULL, it displays 'No Phone' instead.

NULL in Aggregate Functions

Most SQL aggregate functions like COUNT(), SUM(), and AVG() ignore NULL values automatically.

SELECT AVG(Age) FROM Students;

Arjun Nair's age is NULL. The database will calculate the average using only the 4 students who have an age value — it will not count the NULL row.

Setting a Column to NULL With UPDATE

UPDATE Students
SET Phone = NULL
WHERE StudentID = 1;

This removes Ravi's phone number by setting it to NULL.

Key Points to Remember

• NULL means no value — it is not zero or an empty string.
• Always use IS NULL or IS NOT NULL to check for NULL — never use = NULL.
• Use COALESCE() or IFNULL() to display a default value when a NULL is encountered.
• Aggregate functions like AVG(), SUM(), and COUNT(column) skip NULL values.
• Columns defined as NOT NULL during table creation will never allow NULL values to be inserted.

Summary

NULL is a special marker in SQL indicating that a value is absent or unknown. It requires special handling — use IS NULL and IS NOT NULL for filtering, and COALESCE() or IFNULL() to substitute default values in the output. Understanding how NULL behaves is essential for writing accurate queries and avoiding unexpected results.