Introduction

In MySQL, setting a column to NULL means storing the SQL null marker, not the string 'NULL' and not an empty string. The basic rules are simple, but many bugs come from quoting NULL, using the wrong comparison syntax, or forgetting that the column definition may not allow nulls.

Updating an existing row to NULL

To clear a column value in an existing row, use NULL without quotes in an UPDATE statement.

UPDATE employees
SET email = NULL
WHERE id = 42;

That stores an actual SQL null in email. If you write 'NULL' instead, MySQL stores the literal text NULL, which is a normal string value.

Inserting rows with NULL

You can also insert null values directly.

INSERT INTO employees (id, name, email)
VALUES (43, 'Alice', NULL);

This is valid only if the target column permits nulls. Otherwise, MySQL raises an error.

The column definition must allow nulls

A null assignment fails when the schema declares the column as NOT NULL.

1CREATE TABLE employees (
2    id INT PRIMARY KEY,
3    name VARCHAR(100) NOT NULL,
4    email VARCHAR(255) NULL
5);

If email were declared as NOT NULL, neither the INSERT nor the UPDATE example above would succeed.

When a null write does not work, inspect the table definition first rather than debugging the query blindly.

Querying for NULL

Nulls do not behave like ordinary values in SQL comparisons. You cannot reliably query them with =.

This is wrong:

SELECT *
FROM employees
WHERE email = NULL;

Use IS NULL instead:

SELECT *
FROM employees
WHERE email IS NULL;

And use IS NOT NULL for the opposite case.

SELECT *
FROM employees
WHERE email IS NOT NULL;

This rule is fundamental to SQL null handling, not just a MySQL quirk.

NULL versus empty string and zero

NULL, '', and 0 are three different states.

• 'NULL means missing or unknown'
• ''' means a known string with zero characters'
• '0 means a known numeric value'

Treating them as interchangeable causes reporting bugs, bad filters, and inconsistent validation logic. If your application wants to normalize one into another, do it intentionally.

Cleaning placeholder values into true nulls

Imported data often uses placeholders such as empty strings or N/A. You can convert those placeholders into real nulls with UPDATE.

UPDATE employees
SET email = NULL
WHERE email = '' OR email = 'N/A';

This is common after CSV imports or legacy migrations where missing values were not modeled properly.

Reading nullable columns with defaults

Sometimes you want to preserve nulls in storage but display a fallback in query results. That is what COALESCE is for.

SELECT id, COALESCE(email, '[email protected]') AS display_email
FROM employees;

COALESCE changes the result set, not the stored data. That is often preferable when the underlying absence of data should remain intact.

Common Pitfalls

The most common mistake is writing 'NULL' instead of NULL. Those values are not equivalent.

Another problem is using = NULL in a WHERE clause and wondering why no rows match. Use IS NULL and IS NOT NULL instead.

It is also easy to overlook schema constraints. If the column is NOT NULL, MySQL is correctly rejecting the write rather than misinterpreting your query.

Summary

• Use SET column = NULL or VALUES (..., NULL) to store a real null in MySQL.
• Do not quote NULL unless you literally want the text 'NULL'.
• The target column must allow null values.
• Query nulls with IS NULL and IS NOT NULL, not =.
• Keep the difference between null, empty string, and numeric zero clear in your schema and queries.