SQL NULL Values: IS NULL, COALESCE, NULLIF, and Common Pitfalls

You write a filter that excludes one value, and rows you expected are missing from the result. Every missing row has nothing stored in the filtered column. SQL records a missing value as NULL, and a comparison against NULL is neither true nor false, so = NULL never matches a row. The test that does match is IS NULL, with IS NOT NULL for the rows that hold a value:

SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;

Replacing a NULL with something usable takes COALESCE or NULLIF.

What NULL means, and why = NULL returns nothing

A column ends up holding NULL in four situations:

• an INSERT that leaves the column out
• a column set to NULL explicitly
• a LEFT JOIN that finds no matching row on the right side
• an aggregate over zero rows, such as SUM()

The queries below run against two tables:

CREATE TABLE Advisors (
    advisor_id   INT PRIMARY KEY,
    advisor_name VARCHAR(50)
);

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

INSERT INTO Advisors VALUES
    (10, 'Turing'),
    (20, 'Hopper'),
    (30, 'Lovelace');

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

Two students have no age, two have no nickname, one has no advisor, and one advisor has no students.

SELECT * FROM Students WHERE age IS NULL;

Reversing the operator returns the other two students:

SELECT * FROM Students WHERE age IS NOT NULL;

To count the gaps instead of listing them, put the same condition in a COUNT:

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

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Written with = instead, the same filter is accepted by the database and returns no rows at all:

SELECT * FROM Students WHERE age = NULL;

= compares two values, and NULL stands for a value nobody knows. The comparison therefore produces UNKNOWN rather than TRUE or FALSE. A WHERE clause keeps a row only when its condition is TRUE, so UNKNOWN drops the row. Those three outcomes are what three-valued logic means, and they catch out a filter that looks unrelated to NULL:

SELECT * FROM Students WHERE age <> 20;

Sam is the only row returned. Jane and Alice have no age, so age <> 20 is UNKNOWN for them and they are dropped, even though neither of them is 20. Asking for the missing ages as well takes a second condition:

SELECT * FROM Students WHERE age <> 20 OR age IS NULL;

NULL in WHERE and HAVING

HAVING works on groups the way WHERE works on rows, with the same three outcomes, so a group whose aggregate comes out NULL is dropped by a HAVING comparison unless you test for it. The SQL WHERE clause article covers the operators themselves.

COALESCE, NULLIF, and the functions each engine adds

COALESCE takes any number of arguments and returns the first one that is not NULL. It comes from the SQL standard, which makes it the portable way to substitute a value:

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

Jane and Sam have no nickname, so the second argument supplies the name. Add a third argument and it is used only when the first two are both NULL. When every argument is NULL the result is NULL as well, so a chain that must never come back empty ends with a literal such as 'Unknown'.

NULLIF(expr1, expr2) returns NULL when the two arguments are equal, and expr1 otherwise. Turning a value into NULL sounds backwards until you need to keep a division alive:

CREATE TABLE Sales (
    order_id   INT PRIMARY KEY,
    revenue    INT,
    units_sold INT
);

INSERT INTO Sales VALUES
    (1, 120, 4),
    (2, 80,  0);

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

Order 2 sold nothing, and NULLIF(units_sold, 0) turns that zero into NULL, so the division returns NULL instead of dividing by zero. The report then shows a gap for order 2, which is what the data actually says.

Each engine also adds a two-argument function that does what COALESCE does:

Write COALESCE in anything that might move between engines. It is the standard form, and each two-argument function beside it is missing from at least one of the other engines here.

CONCAT is where the engines stop agreeing. A NULL argument does not produce the same string on all four:

MySQL 8.4 documents that "CONCAT() returns NULL if any argument is NULL", while SQL Server "implicitly converts null values to empty strings" and PostgreSQL 18 ignores NULL arguments. Oracle differs a step earlier, because it "treats a character value with a length of zero as null", and its concatenation "always results in the other operand". Wrap the nullable column in COALESCE and every engine returns the same string.

NULL in aggregates, GROUP BY and ORDER BY

An aggregate skips NULL rather than treating it as zero, with one exception:

The first two rows are the pair that changes a report. PostgreSQL 18 defines them as the number of input rows and the number of input rows "in which the input value is not null".

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

AVG(age) adds 20 and 25 and divides by 2, the number of rows that have an age, not by the 4 rows in the table. Reporting that 22.5 as the average age of the class would be wrong by two students. SQL COUNT, AVG, and SUM Functions covers the rest of the aggregates.

A GROUP BY keeps the NULL rows instead of dropping them, and collects them into one group:

SELECT advisor_id, COUNT(*) AS students
FROM Students
GROUP BY advisor_id;

Sam is the row behind the NULL group. MySQL 8.4 states the rule as "Two NULL values are regarded as equal in a GROUP BY", which is the one place where two unknowns are allowed to match.

Sorting has to put the unknown values somewhere, and the engines disagree on where:

Each row is the default documented on that engine's own ORDER BY page: PostgreSQL 18, Oracle 23, SQL Server and MySQL 8.4.

PostgreSQL and Oracle let you say where they go, with NULLS LAST or NULLS FIRST after the sort column:

SELECT student_id, age
FROM Students
ORDER BY age ASC NULLS LAST, student_id;

Move a report from one engine to another and the two NULL rows change ends. The row at the top is then a student with no age on file rather than the youngest student. SQL ORDER BY covers sorting in general.

NULL in joins and NOT IN subqueries

A join condition is a comparison, so a NULL on either side of it never matches:

SELECT s.student_id, s.first_name, a.advisor_name
FROM Students s
INNER JOIN Advisors a ON s.advisor_id = a.advisor_id;

Sam is gone from the result, because his advisor_id is NULL and NULL equals nothing. A LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL where there is no match:

SELECT s.student_id, s.first_name, a.advisor_name
FROM Students s
LEFT JOIN Advisors a ON s.advisor_id = a.advisor_id;

Those manufactured NULLs are useful: filtering on them lists exactly the rows that found no partner, which is how you find orphaned records.

SELECT s.student_id, s.first_name
FROM Students s
LEFT JOIN Advisors a ON s.advisor_id = a.advisor_id
WHERE a.advisor_id IS NULL;

SQL Joins Explained walks through the join types themselves.

Asking the same question through a subquery goes wrong the moment the subquery returns a NULL. Advisor 30 has no students, so this query should return one row:

SELECT advisor_id, advisor_name
FROM Advisors
WHERE advisor_id NOT IN (
    SELECT advisor_id
    FROM Students
);

It returns nothing. NOT IN expands into a chain of <> comparisons, and Sam's advisor_id is NULL, so one of those comparisons is UNKNOWN for every candidate row. UNKNOWN is not TRUE, so no row survives. The same question asked with NOT EXISTS returns the advisor:

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

NOT EXISTS asks whether the subquery produced a row, which is a yes or no question with no third answer. Reach for it whenever the subquery selects a nullable column.

When to allow NULL in a table you design

Mark a column NOT NULL when a row without that value would be meaningless: identifiers, foreign keys, status flags, and the timestamp that records when the row was created. Leave a column nullable when the value is genuinely optional, such as a middle name, a second phone number, or a discount that does not apply to every order. The cost of a nullable column is that every query against it needs the NULL handling shown above, so a column left nullable for no stated reason buys you nothing and adds a case to each filter.

Avoid using NULL as a flag. Reading a NULL in is_deleted as not deleted is a convention, and nothing in the column says which way to read it. A deleted_at timestamp says it instead: the column is NULL while the row is live. Write that meaning down next to the column, because a nullable column with no stated meaning turns into a guess for whoever queries it next.

NULL and UNIQUE constraints

A UNIQUE constraint compares values, and two NULLs do not compare as equal, so a unique column usually accepts more than one row with nothing in it. SQL Server is the engine that does not:

The PostgreSQL 18 documentation puts the common case as "By default, two null values are not considered equal in this comparison", and adds NULLS NOT DISTINCT as the clause that rejects the second NULL row. MySQL 8.4 and Oracle 23 accept the repeated NULLs as well. SQL Server counts them "as duplicate values for indexing purposes", so there the second row with nothing in the column is refused.

A primary key is the opposite case. The constraint implies NOT NULL on every column it covers, so an INSERT that leaves the key out is refused whatever the engine. Primary Key in SQL covers what else the constraint brings with it.

Inspect NULL values in DbSchema

Finding the nullable columns in a schema you did not write is faster when you can see them all at once.

1. Click Connect to Database and pick your engine in Choose Your Database, then fill in the Connection Dialog.
2. Let DbSchema reverse-engineer the schema and draw it as an interactive diagram.
3. Double-click a table header to open the Table Dialog and read the Columns tab, which marks each column NOT NULL or nullable.
4. Open the SQL Editor from the Editors menu, paste the IS NULL queries above, and click Execute Query to see how much of each column is actually missing.

Reverse-engineering reads the schema from the live database and builds the model, which DbSchema keeps in a file of its own. The Columns tab reads that model, so neither step writes to the database. The SQL Editor is the step that runs against the database, which is why the counts it returns are the current data.

Download DbSchema at https://dbschema.com/download.html, connect to your database, and open the Columns tab of the table that keeps surprising your reports. Connecting, the diagram, the Table Dialog, and the SQL Editor are all in the free Community Edition.