SQL NULL Values – IS NULL, COALESCE, NULLIF, and Common Pitfalls | DbSchema

Table of Contents

1. What NULL means in SQL
2. IS NULL and IS NOT NULL
3. Three-valued logic and why = NULL does not work
4. COALESCE – replace NULL with a default
5. NULLIF – return NULL when two values match
6. Database-specific NULL functions
7. NULL in aggregate functions
8. NULL in NOT IN and NOT EXISTS
9. NULL in JOINs
10. NULL in WHERE and HAVING
11. NULL in ORDER BY
12. NULL and UNIQUE constraints
13. Designing tables: when to allow NULL
14. Inspect NULL values in DbSchema
15. FAQ
16. Conclusion

A SQL NULL value represents missing or unknown data — it is not zero, not an empty string, and not false. NULL is a special marker that means "no value exists here."

Understanding NULL is essential because it affects filtering, aggregation, joins, comparisons, and even subqueries in ways that surprise many developers. This guide covers every common NULL scenario with examples for MySQL, PostgreSQL, and SQL Server.

What NULL means in SQL

NULL appears when:

• a value was not provided during an INSERT
• a column was explicitly set to NULL
• a LEFT JOIN finds no matching row in the right table
• a function returns no result (for example, SUM() over zero rows)

NULL is not comparable. Any comparison with NULL returns UNKNOWN, not TRUE or FALSE. That is why = NULL never returns rows.

Sample table used in this guide:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    age        INT
);

INSERT INTO Students VALUES
    (1, 'John',  'Doe',   20),
    (2, 'Jane',  'Doe',   NULL),
    (3, 'Sam',   'Smith', 25),
    (4, 'Alice', 'Park',  NULL);

IS NULL and IS NOT NULL

Use IS NULL to find rows where a column has no value, and IS NOT NULL to find rows that do have a value.

Find students with no age recorded

SELECT * FROM Students WHERE age IS NULL;

Find students with age recorded

SELECT * FROM Students WHERE age IS NOT NULL;

Count NULL values in a column

SELECT COUNT(*) AS missing_ages
FROM Students
WHERE age IS NULL;

Three-valued logic and why = NULL does not work

A very common mistake is using = NULL instead of IS NULL:

-- Wrong: returns no rows
SELECT * FROM Students WHERE age = NULL;

-- Correct: returns rows where age is missing
SELECT * FROM Students WHERE age IS NULL;

The = operator compares values. Because NULL represents an unknown, comparing it to anything — even another NULL — always produces UNKNOWN, which evaluates the same as FALSE in a WHERE clause.

SELECT NULL = NULL;   -- NULL (not TRUE)
SELECT NULL IS NULL;  -- TRUE

Three-valued logic also explains why this query skips students with no age:

SELECT * FROM Students WHERE age <> 20;

For age = NULL, the predicate becomes UNKNOWN, not TRUE, so the row is filtered out unless you handle NULL explicitly.

COALESCE – replace NULL with a default

COALESCE(expr1, expr2, ...) returns the first non-NULL value from its argument list. It is supported in all major databases.

Replace missing age with 'Unknown'

SELECT first_name, last_name,
       COALESCE(CAST(age AS VARCHAR), 'Unknown') AS age_display
FROM Students;

COALESCE with multiple fallbacks

SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name
FROM Students;

The database tries each argument in order and returns the first that is not NULL.

NULLIF – return NULL when two values match

NULLIF(expr1, expr2) returns NULL if both arguments are equal, otherwise returns expr1. It is the inverse of COALESCE and is commonly used to avoid division-by-zero errors.

Avoid division by zero

SELECT order_id,
       revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;

When units_sold is 0, NULLIF converts it to NULL, so the division returns NULL instead of an error.

Database-specific NULL functions

Different databases provide additional functions that wrap the same logic as COALESCE:

Prefer COALESCE and NULLIF in portable SQL because they work across all major databases.

MySQL IFNULL example

SELECT first_name, IFNULL(age, 0) AS age FROM Students;

SQL Server ISNULL example

SELECT first_name, ISNULL(age, 0) AS age FROM Students;

NULL in aggregate functions

NULL handling in aggregates is one of the most surprising aspects of SQL:

SELECT
    COUNT(*)   AS total_students,
    COUNT(age) AS students_with_age,
    AVG(age)   AS average_age
FROM Students;

AVG(age) divides the sum by the number of non-NULL values (2), not the total row count (4).

For a full guide on aggregate functions, see SQL COUNT, AVG, and SUM Functions.

NULL in NOT IN and NOT EXISTS

NOT IN is a classic source of bugs when the subquery can return NULL.

SELECT student_id
FROM Students
WHERE student_id NOT IN (
    SELECT advisor_id
    FROM Advisors
);

If Advisors.advisor_id contains even one NULL, the comparison becomes uncertain and the query may return no rows at all.

The safer alternative is usually NOT EXISTS:

SELECT s.student_id
FROM Students s
WHERE NOT EXISTS (
    SELECT 1
    FROM Advisors a
    WHERE a.advisor_id = s.student_id
);

That pattern is often easier to reason about and behaves better when NULLs are present.

NULL in JOINs

NULL values in join columns cause rows to be excluded from INNER JOIN results because NULL does not equal anything — including another NULL.

-- If customer_id is NULL in orders, those orders will be excluded
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

A LEFT JOIN preserves rows from the left table even when there is no match, filling the right-side columns with NULL:

SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- c.name is NULL for unmatched orders

Use IS NULL after a LEFT JOIN to find orphaned rows:

SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

For more on joins, read SQL Joins Explained.

NULL in WHERE and HAVING

Because NULL comparisons return UNKNOWN, rows with NULL in the filtered column are excluded by most WHERE conditions:

-- Excludes rows where age IS NULL
SELECT * FROM Students WHERE age > 18;

-- Includes NULL rows
SELECT * FROM Students WHERE age > 18 OR age IS NULL;

The same applies to HAVING with aggregate conditions.

NULL in ORDER BY

Different databases sort NULL values differently by default:

PostgreSQL and Oracle support explicit control:

ORDER BY age ASC NULLS LAST;
ORDER BY age DESC NULLS FIRST;

For more on sorting, see SQL ORDER BY.

NULL and UNIQUE constraints

A frequent schema-design question is whether a UNIQUE column can contain NULL. The answer depends on the database, but many engines allow multiple NULL rows because NULL means "unknown", not a duplicate value.

That is different from a primary key, which can never be NULL. If you need a refresher on that distinction, see Primary Key in SQL.

Designing tables: when to allow NULL

Best practices for NULL in schema design:

• Use NOT NULL for required fields — primary keys, foreign keys, status flags, timestamps.
• Allow NULL for truly optional data — a middle name, a second phone number, a discount percentage that might not apply.
• Avoid using NULL as a flag value — instead of is_deleted = NULL, use an explicit deleted_at TIMESTAMP or a boolean column.
• Document your NULL policy — a nullable column should have a clear meaning for NULL in the team's data dictionary.

To model nullable columns visually and enforce constraints across tables, connect your database to DbSchema and inspect the column definitions in the schema editor.

Inspect NULL values in DbSchema

DbSchema makes it easy to identify and manage NULL columns across a schema.

1. Connect to your database using the PostgreSQL JDBC driver or MySQL JDBC driver.
2. Open any table in the Table Editor and see which columns are marked NOT NULL vs nullable.
3. Run IS NULL queries in the SQL Editor to audit missing data.
4. Use the Random Data Generator to fill tables with test data, including columns with intentional NULLs.
5. Review the relationships and nullable columns visually in the diagram documentation before you change constraints.

This is especially useful when auditing legacy schemas or validating data pipelines that may produce unexpected NULL values.

FAQ

What is NULL in SQL?

NULL is a marker for missing or unknown data. It is not zero, not an empty string, and not false. It means "no value exists."

Why doesn't = NULL work?

Because NULL represents an unknown, comparing it with = always returns UNKNOWN rather than TRUE or FALSE. Use IS NULL instead.

Is NULL equal to NULL in SQL?

No. NULL = NULL evaluates to UNKNOWN. To check whether a value is NULL, always use IS NULL.

Can a primary key be NULL?

No. Primary keys must be unique and non-null. A NULL value has no identity.

How does COALESCE handle multiple NULLs?

COALESCE returns the first non-NULL value from its list. If all arguments are NULL, it returns NULL.

Does AVG() count NULL rows in its denominator?

No. AVG() ignores NULL rows entirely; it divides the sum only by the count of non-NULL values.

What is the difference between NULL and an empty string?

An empty string '' is a known value with zero length. NULL means the value is absent or unknown. They are stored differently and behave differently in comparisons.

Why does NOT IN sometimes return no rows when NULL is present?

Because one NULL in the NOT IN list makes the comparison uncertain. In those cases, NOT EXISTS is usually the safer pattern.

Conclusion

SQL NULL values are one of the most nuanced parts of the language. They require special operators (IS NULL, IS NOT NULL), explicit handling in aggregates, and careful design decisions when building schemas.

Use COALESCE to substitute defaults, NULLIF to prevent division-by-zero errors, and always test nullable column behavior in JOIN and WHERE conditions before deploying to production.

Connect DbSchema to your database to audit NULL columns visually, run targeted queries, and model nullable relationships clearly for your entire team.