How to Handle NULL Values in MySQL Database

I still remember the first time a NULL value quietly broke one of my reports. I had written a query that summed up a “discount” column, the totals looked wrong, and I spent almost an hour convinced my math was off before I realized a handful of rows had NULL instead of 0. That one bug taught me more about relational databases than any tutorial had up to that point, so I want to walk you through everything I’ve learned about NULL in MySQL — from the basic definition all the way to how it behaves inside indexes, joins, and aggregate functions.

What NULL Actually Means

The first thing I tell anyone learning SQL is this: NULL is not zero, it’s not an empty string, and it’s not “false.” NULL means unknown or absence of a value. When a column is NULL, MySQL is telling you it has no idea what belongs there — maybe the data was never collected, maybe it doesn’t apply to that row, or maybe it’s simply missing.

This distinction matters because NULL behaves differently from every other value in comparisons, arithmetic, and logic. I like to think of NULL as a kind of database-shaped question mark. You can’t add a question mark to 5 and get a number, and you can’t compare a question mark to another question mark and say they’re “equal” — that’s exactly how MySQL treats NULL.

Creating a Table That Allows or Disallows NULL

Let me set up a simple employees table so I can demonstrate everything with real data.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    manager_id INT NULL,
    bonus DECIMAL(10,2) NULL,
    department VARCHAR(50)
);

Notice manager_id and bonus are explicitly allowed to be NULL — that’s the default in MySQL unless I add a NOT NULL constraint. I used NOT NULL on first_name and last_name because I never want a nameless employee row in my system.

Let me insert some sample data:

INSERT INTO employees (first_name, last_name, manager_id, bonus, department) VALUES
('Ayesha', 'Khan', NULL, 500.00, 'Engineering'),
('Bilal', 'Ahmed', 1, NULL, 'Engineering'),
('Sara', 'Malik', 1, 300.00, NULL),
('Usman', 'Tariq', 2, NULL, 'Sales');

Here, Ayesha has no manager (she’s the top of the chain), Bilal has no bonus this quarter, and Sara’s department wasn’t recorded. Each of these NULLs means something slightly different, which is exactly the point — NULL is a placeholder for “we don’t know,” not a single fixed meaning.

Why = NULL Never Works

This trips up almost everyone at some point, including me back when I started. If I run:

SELECT * FROM employees WHERE bonus = NULL;

I get an empty result set, even though two rows clearly have NULL bonuses. That’s because in three-valued logic, NULL = NULL doesn’t evaluate to TRUE — it evaluates to UNKNOWN. And WHERE only returns rows where the condition is TRUE, not UNKNOWN.

The correct way to check for NULL is:

SELECT * FROM employees WHERE bonus IS NULL;

Output:

+----+------------+-----------+------------+-------+------------+
| id | first_name | last_name | manager_id | bonus | department |
+----+------------+-----------+------------+-------+------------+
|  2 | Bilal      | Ahmed     |          1 |  NULL | Engineering|
|  4 | Usman      | Tariq     |          2 |  NULL | Sales      |
+----+------------+-----------+------------+-------+------------+

And to find rows that are not NULL:

SELECT * FROM employees WHERE bonus IS NOT NULL;

Three-Valued Logic in Practice

MySQL’s boolean logic has three possible outcomes: TRUE, FALSE, and UNKNOWN. Any comparison involving NULL returns UNKNOWN, and UNKNOWN behaves like FALSE for filtering purposes but propagates differently through AND/OR. Here’s a table I keep pinned above my desk for exactly this reason:

This matters a lot in WHERE clauses with multiple conditions. If one condition evaluates to UNKNOWN because of a NULL comparison, it can silently exclude rows you expected to see, and there’s no error message warning you.

Handling NULLs with Functions

MySQL gives me several functions specifically for dealing with NULLs gracefully instead of writing verbose CASE statements every time.

IFNULL()

SELECT first_name, IFNULL(bonus, 0) AS bonus FROM employees;

Output:

+------------+--------+
| first_name | bonus  |
+------------+--------+
| Ayesha     | 500.00 |
| Bilal      |   0.00 |
| Sara       | 300.00 |
| Usman      |   0.00 |
+------------+--------+

COALESCE()

COALESCE() is the ANSI-standard version and accepts multiple arguments, returning the first non-NULL value:

SELECT first_name, COALESCE(bonus, 0) AS bonus,
       COALESCE(department, 'Unassigned') AS department
FROM employees;

I personally use COALESCE() more often than IFNULL() because it’s portable across databases and it lets me chain fallback values — for example, COALESCE(mobile_phone, home_phone, 'No contact').

NULLIF()

NULLIF() does the opposite — it returns NULL if two expressions are equal, which is handy for avoiding division-by-zero errors:

SELECT sales_total / NULLIF(units_sold, 0) AS avg_price
FROM sales_report;

If units_sold is 0, this returns NULL instead of throwing a division error.

NULLs in Aggregate Functions

Aggregate functions like SUM(), AVG(), MIN(), MAX(), and COUNT() all ignore NULL values by default, except for COUNT(*).

SELECT SUM(bonus) AS total_bonus, AVG(bonus) AS avg_bonus, COUNT(bonus) AS bonus_count, COUNT(*) AS total_rows
FROM employees;

Output:

+-------------+-----------+-------------+------------+
| total_bonus | avg_bonus | bonus_count | total_rows |
+-------------+-----------+-------------+------------+
|      800.00 |    400.00 |           2 |          4 |
+-------------+-----------+-------------+------------+

Notice AVG(bonus) is 400, not 200 — it divided 800 by 2 (the non-NULL rows), not by 4. This is the exact bug I mentioned in my intro, and it’s a very common source of incorrect reporting if you’re not paying attention.

NULLs and JOINs

NULL handling becomes especially important once JOINs enter the picture. Consider a LEFT JOIN between employees and a departments table:

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department = d.name;

For Sara, whose department column is NULL, the join condition e.department = d.name evaluates to UNKNOWN, so no match is found, and d.department_name comes back as NULL for her row — which is exactly the behavior a LEFT JOIN guarantees for unmatched rows.

NULLs and Indexing

Something that surprised me when I first got serious about performance tuning: MySQL does allow NULL values in indexed columns, including unique indexes — because, again, NULL <> NULL, so multiple NULLs don’t violate uniqueness. However, indexes on nullable columns can be less efficient for certain query patterns, and IS NULL lookups can still use an index (the optimizer treats NULL as a distinct value the index can point to), but comparisons like column != value on nullable columns sometimes force a full scan if not careful.

CREATE UNIQUE INDEX idx_email ON employees(email);

If email is nullable, I can insert several rows with email = NULL without violating the unique constraint — only actual duplicate non-NULL values are rejected.

Storage Engine Perspective

Under the hood, InnoDB (MySQL’s default storage engine since 5.5) stores a NULL bitmap in each row’s header. Every nullable column gets one bit in this bitmap indicating whether the value is NULL, so InnoDB doesn’t need to store any data for that column when it’s NULL — this is actually a small storage optimization. Fixed-length columns that are NULL still reserve space in some layouts, but variable-length columns skip storage entirely when NULL.

flowchart LR
    A[Row Header] --> B[NULL Bitmap]
    A --> C[Variable-Length Field Lengths]
    A --> D[Actual Column Data]
    B -->|bit=1| E[Column is NULL - no data stored]
    B -->|bit=0| F[Column has value - data stored in row]

Real-World DBA Scenarios

In my own database work, I’ve run into a few recurring NULL-related situations:

1. Migrating legacy data — old systems often used empty strings or the literal text "NULL" instead of a true NULL, so I always run a cleanup pass: UPDATE table SET col = NULL WHERE col = '';
2. Reporting dashboards — I wrap every nullable numeric column in COALESCE() before it reaches a BI tool, because most charting libraries render NULL as a gap or, worse, silently drop the row.
3. API responses — when I serialize query results to JSON, I decide explicitly whether NULL becomes null, 0, or an omitted key, because different frontend teams expect different conventions.
4. Data validation triggers — I’ve written BEFORE INSERT triggers that reject rows where a business-critical column is NULL, even if the schema technically allows it, because sometimes NULL is legal but not meaningful for that particular workflow.

Best Practices I Follow

• I add NOT NULL to any column where the business logic genuinely requires a value — this catches bugs at insert time instead of query time.
• I avoid using NULL to mean “zero” or “false” — I use an actual 0 or FALSE/BOOLEAN default instead, and reserve NULL strictly for “unknown” or “not applicable.”
• I always use IS NULL / IS NOT NULL, never = NULL.
• I wrap aggregates and calculations with COALESCE() when a report needs to treat NULL as zero.
• I document, in the schema or a data dictionary, what NULL means for each nullable column, because “unknown” and “not applicable” often require different downstream handling.

Troubleshooting Checklist

FAQs

Does NULL take up storage space in MySQL? For InnoDB, a NULL value uses one bit in the row’s NULL bitmap and typically no additional storage for the column data itself, so NULLs are cheap to store.

Can a primary key column be NULL? No. Primary key columns are implicitly NOT NULL in MySQL because primary keys must uniquely identify every row.

What’s the difference between NULL and an empty string? An empty string '' is a known value — a string with zero length. NULL means the value is unknown or missing entirely. '' IS NULL returns FALSE.

How do I sort so NULLs appear last? ORDER BY column IS NULL, column ASC — this pushes NULL rows (where the expression is 1/TRUE) to the end.

Is there a NULL-safe equality operator? Yes — <=> (the NULL-safe equal operator) treats NULL <=> NULL as TRUE, unlike the regular = operator.

SELECT * FROM employees WHERE bonus <=> NULL;

Common Interview Questions

1. Why does WHERE column = NULL always return zero rows in MySQL?
2. What is the difference between IFNULL() and COALESCE()?
3. How do aggregate functions handle NULL values?
4. Can a UNIQUE index contain multiple NULL values? Why?
5. What is the NULL-safe equal operator, and when would you use it?
6. How does NULL affect three-valued logic in a compound WHERE clause?
7. How is NULL physically represented in an InnoDB row?

Optimization Tips

• Avoid != NULL-style mistakes that silently return empty sets — always test filters against known NULL rows during development.
• When filtering on a nullable indexed column with IS NULL, check EXPLAIN to confirm the optimizer is using the index rather than scanning the whole table.
• For very large tables where a column is NULL in the vast majority of rows, consider whether a separate “exception” table is more efficient than a mostly-empty nullable column.
• Use generated columns with COALESCE() if you frequently query “NULL as zero” logic, so you can index the generated column instead of recalculating it every query.

Summary and Key Takeaways

Handling NULL correctly is one of those skills that looks small on the surface but quietly underlies almost every serious bug I’ve debugged in production SQL. NULL means “unknown,” not zero and not empty. It requires IS NULL/IS NOT NULL for comparisons, it gets ignored by most aggregate functions, and it interacts with JOINs and indexes in ways that are easy to overlook until they cost you an afternoon. Once I internalized three-valued logic and made COALESCE() a habit rather than an afterthought, NULL stopped being a source of mysterious bugs and became just another normal part of designing a schema.

References

• MySQL 8.0 Reference Manual — Working with NULL Values
• MySQL 8.0 Reference Manual — Comparison Functions and Operators
• MySQL 8.0 Reference Manual — Control Flow Functions