SQL NULL Values in MySQL

NULL represents a missing or unknown value. It is different from 0, an empty string, or a blank space. To find NULL values, use IS NULL:

SELECT id, name, class, mark
FROM student3
WHERE class IS NULL;

To return rows where the value is present, use IS NOT NULL:

SELECT id, name, class, mark
FROM student3
WHERE class IS NOT NULL;

The examples on this page use a modified student table where class and mark can contain NULL values.

What Does NULL Mean in SQL? Top ↑

NULL means the database does not currently have a known value for that field. It should not be confused with other values that have their own meaning.

Find NULL and Non-NULL Rows Top ↑

IS NULL Top ↑

SELECT id, name, class, mark
FROM student3
WHERE class IS NULL;

IS NOT NULL Top ↑

SELECT id, name, class, mark
FROM student3
WHERE class IS NOT NULL;

This returns only rows where class contains a known non-NULL value.

Insert a NULL Value Top ↑

If a column allows NULL, use the SQL keyword NULL without quotes:

INSERT INTO student3
    (id, name, class, mark)
VALUES
    (36, 'Ravi Kumar', NULL, 72);

See SQL INSERT for the full insert workflow.

Update a Column to NULL Top ↑

Set one row to NULL:

UPDATE student3
SET class = NULL
WHERE id = 4;

To set the column to NULL for every row:

UPDATE student3
SET class = NULL;

See SQL UPDATE for safer update practices.

Allow NULL in a Column Top ↑

A column defined as NOT NULL cannot normally be assigned SQL NULL. In MySQL, the column definition can be changed when NULL values are appropriate for the data model:

ALTER TABLE student3
MODIFY class VARCHAR(10)
NULL DEFAULT NULL;

This changes the column so it can contain NULL and uses NULL as its default when no value is supplied.

See ALTER TABLE for structural changes.

Delete Rows Containing NULL Top ↑

First preview the rows:

SELECT id, name, class, mark
FROM student3
WHERE class IS NULL;

Then, if those rows really should be removed:

DELETE FROM student3
WHERE class IS NULL;

See SQL DELETE for destructive-query precautions.

Why = NULL and <> NULL Do Not Work Top ↑

SQL uses three-valued logic: a comparison can evaluate to true, false, or unknown. Because NULL represents an unknown value, normal equality comparison with NULL does not produce true.

This expression returns NULL/unknown in MySQL:

SELECT NULL = NULL;

Therefore this condition is wrong for finding NULL rows:

-- Do not use this to find NULL values
WHERE class = NULL

Use:

WHERE class IS NULL

Why <> also excludes NULL rows Top ↑

SELECT id, name, class
FROM student3
WHERE class <> 'Six';

Rows where class is NULL are not returned because NULL <> 'Six' is unknown, not true.

If NULL rows must also be included:

SELECT id, name, class
FROM student3
WHERE class <> 'Six'
   OR class IS NULL;

MySQL NULL-safe <=> Operator Top ↑

MySQL provides the null-safe equality operator <=>. Unlike =, it can treat two NULL operands as equal.

SELECT NULL <=> NULL; -- 1

Find rows where class is NULL:

SELECT id, name, class, mark
FROM student3
WHERE class <=> NULL;

For a direct NULL test, IS NULL is clearer. The null-safe operator is more useful when comparing two nullable expressions.

Compare two nullable columns Top ↑

Normal equality does not treat NULL and NULL as equal:

SELECT id, name, class, mark
FROM student3
WHERE class = mark;

Using MySQL's null-safe equality:

SELECT id, name, class, mark
FROM student3
WHERE class <=> mark;

can also match a row where both values are NULL.

COUNT() and NULL Values Top ↑

COUNT() handles NULL differently depending on what is counted.

SELECT COUNT(*) AS total_rows,
       COUNT(class) AS known_class_values
FROM student3;

• COUNT(*) counts every row.
• COUNT(class) ignores rows where class is NULL.

NULL Values with GROUP BY Top ↑

When grouping by a nullable column, rows with NULL in that grouping expression are placed into one NULL group.

SELECT class,
       COUNT(*) AS total_students
FROM student3
GROUP BY class;

To display a label instead of NULL:

SELECT IFNULL(class, 'Not Known') AS class,
       COUNT(*) AS total_students
FROM student3
GROUP BY class;

See GROUP BY and IFNULL / COALESCE.

NULL Values with DISTINCT Top ↑

DISTINCT removes duplicate result values. If the selected column contains several NULLs, the distinct result contains one NULL entry.

SELECT DISTINCT class
FROM student3;

For display:

SELECT DISTINCT
       IFNULL(class, 'Not Known') AS class
FROM student3;

NULL in Calculations Top ↑

Arithmetic involving NULL normally produces NULL because the missing value prevents the complete result from being known.

SELECT mark,
       mark + 5 AS increased_mark
FROM student3;

If mark is NULL, increased_mark is also NULL.

Aggregate functions have their own NULL rules. For example, AVG() ignores NULL values rather than treating them as zero.

Replace NULL with IFNULL() or COALESCE() Top ↑

MySQL IFNULL() can provide a replacement value:

SELECT id,
       name,
       IFNULL(class, 'Not Known') AS class
FROM student3;

For calculations, provide an appropriate numeric fallback only when the application meaning supports it:

SELECT id,
       name,
       COALESCE(mark, 0) + 5 AS adjusted_mark
FROM student3;

Using SQL NULL with PHP PDO Top ↑

Use PHP's real null value and bind it as PDO::PARAM_NULL when the database column should receive SQL NULL.

<?php
require 'config.php';

$id=4;
$class=null;

$stmt=$dbo->prepare(
    "UPDATE student3
     SET class=:class
     WHERE id=:id"
);

$stmt->bindValue(
    ':class',
    null,
    PDO::PARAM_NULL
);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->execute();

See PDO UPDATE and PDO INSERT for the PHP-side workflow.

Common SQL NULL Mistakes Top ↑

Using = NULL Top ↑

Normal equality with NULL evaluates as unknown. Use IS NULL.

Using <> NULL Top ↑

Use IS NOT NULL when testing whether a value is present.

Confusing NULL with zero or an empty string Top ↑

These represent different data states and should not be interchanged without a clear application rule.

Quoting NULL when inserting Top ↑

'NULL' is text. The SQL NULL keyword is written without quotes.

Forgetting that COUNT(column) ignores NULL Top ↑

Use COUNT(*) when every row should be counted.

Assuming NOT IN always behaves intuitively with NULL Top ↑

Because NULL introduces an unknown truth value, NOT IN requires extra care when the list or subquery can contain NULL. See SQL IN and NOT IN.

Using MySQL <=> as portable SQL Top ↑

The null-safe equality operator is MySQL-specific. Use database-appropriate NULL comparison syntax when portability matters.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: