NULL in SQL: IS NULL, COALESCE and Common Mistakes

NULL in SQL means a value is missing or unknown. It is not zero, and it is not an empty string. That single fact explains why comparisons with NULL behave in surprising ways.

NULL causes more silently wrong query results than almost anything else. It rarely throws an error. Rows just vanish, or totals come out slightly off, and the query looks perfectly fine. This guide covers IS NULL, COALESCE and the mistakes worth knowing before your next interview.

What is NULL in SQL?

Think of a paper form with an empty box. Writing “0” or “none” in the box is an answer. Leaving it blank means “we do not know.” NULL is the blank box.

Here is a customers table where two people have no phone number on record:

CREATE TABLE customers (customer_id INT, name VARCHAR(50), phone VARCHAR(15));
INSERT INTO customers VALUES
  (1, 'Asha',  '9000000001'),
  (2, 'Ravi',  NULL),
  (3, 'Meera', '9000000003'),
  (4, 'Kabir', NULL),
  (5, 'Neha',  '9000000005');

CREATE TABLE orders (order_id INT, customer_id INT, status VARCHAR(20));
INSERT INTO orders VALUES
  (101, 1, 'completed'),
  (102, 2, 'cancelled'),
  (103, NULL, 'completed'),
  (104, 1, NULL),
  (105, 2, NULL);

CREATE TABLE scores (student VARCHAR(50), score INT);
INSERT INTO scores VALUES ('Asha', 80), ('Ravi', 60), ('Meera', NULL);

How to check for NULL: IS NULL and IS NOT NULL

The natural first attempt is an equals sign, and it does not work:

SELECT name, phone
FROM customers
WHERE phone = NULL;

It runs without an error and returns no rows, even though two customers have no phone. The correct way is IS NULL:

SELECT name, phone
FROM customers
WHERE phone IS NULL;

And the opposite, IS NOT NULL, returns the customers who do have a phone number:

SELECT name, phone
FROM customers
WHERE phone IS NOT NULL;

Why NULL = NULL is not true

SQL uses three outcomes, not two: true, false and unknown. Because NULL means “unknown,” comparing it with anything gives “unknown.” Even NULL = NULL is unknown, since one unknown value may or may not equal another. A WHERE clause keeps a row only when the condition is true, so “unknown” rows are dropped just like false ones.

Mistake 1: the not-equal filter drops NULL rows

Here is an orders table where some orders have no status recorded:

You want every order that is not cancelled:

SELECT order_id, status
FROM orders
WHERE status <> 'cancelled';

Orders 104 and 105 are missing. Their status is NULL, so status <> 'cancelled' is “unknown” and they are filtered out. If missing statuses should be included, say so explicitly:

SELECT order_id, status
FROM orders
WHERE status <> 'cancelled' OR status IS NULL;

NULL in COUNT, SUM and AVG

COUNT(*) counts rows. COUNT(column) counts only the rows where that column is not NULL:

SELECT COUNT(*) AS all_rows, COUNT(phone) AS with_phone
FROM customers;

Both are correct. They answer different questions: “how many customers?” versus “how many customers have a phone number?”

SUM, AVG, MIN and MAX also ignore NULLs. Take this scores table, where Meera has not taken the test yet:

SELECT AVG(score) AS avg_ignoring_null,
       AVG(COALESCE(score, 0)) AS avg_null_as_zero
FROM scores;

The first average is (80 + 60) / 2 = 70, because Meera’s NULL is skipped entirely. The second treats her as a zero: (80 + 60 + 0) / 3 = 46.67. Neither is wrong. Which one you want depends on what NULL means in your data: a missing measurement, or a real zero?

Mistake 2: NOT IN with a NULL

This is a classic trap. You want customers who have never placed an order. Look back at the orders table: order 103 has a NULL customer_id (perhaps a guest checkout).

SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Zero rows, even though Meera, Kabir and Neha clearly have no orders. Here is why. NOT IN (1, 2, NULL) expands to <> 1 AND <> 2 AND <> NULL. The last part is always “unknown,” so the whole condition can never be true.

The fix is NOT EXISTS, which is not affected by NULLs:

SELECT name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

A LEFT JOIN with an IS NULL check, as shown in our SQL joins guide, is another safe way to write this.

Replacing NULL with COALESCE

COALESCE returns the first value in its list that is not NULL. It is the standard way to substitute a default:

SELECT name, COALESCE(phone, 'not provided') AS phone
FROM customers;

You will also see IFNULL (MySQL), ISNULL (SQL Server) and NVL (Oracle). They are database-specific versions of the same idea. COALESCE works in all of them, so it is the safe one to learn first.

A habit worth building

Before you trust any result, ask: could NULLs be hiding in the columns I am filtering, joining or aggregating? That one question catches a large share of wrong answers, and it is exactly the kind of healthy suspicion interviewers look for.

• Filtering with <> or NOT IN? Check for NULLs.
• Averaging or counting? Decide whether NULL should count as zero.
• Joining? A LEFT JOIN creates NULLs by design, so remember they are there.

Practise on data that actually contains NULLs. Our datasets in the SQL compiler include missing values on purpose, so you meet these traps before an interview does.

Frequently asked questions

Is NULL the same as zero or an empty string?

No. Zero is a number and an empty string is a text value of length zero. NULL means no value at all. They behave differently in comparisons, counts and averages.

Why does WHERE column = NULL return nothing?

Because comparing anything to NULL with = gives “unknown”, not true, and WHERE keeps only rows where the condition is true. Use IS NULL instead.

Does COUNT(*) count NULL values?

COUNT(*) counts every row, whether or not columns are NULL. COUNT(column) skips rows where that column is NULL.

What does COALESCE do in SQL?

It returns the first non-NULL value from a list of arguments, so COALESCE(phone, 'not provided') shows a default text when phone is NULL.

Where do NULLs appear when sorting?

It depends on the database. MySQL treats NULL as the lowest value, so it comes first in ascending order. PostgreSQL and Oracle put NULLs last in ascending order. PostgreSQL, Oracle and recent SQLite versions let you control it with NULLS FIRST or NULLS LAST.

Related reading

• SQL Joins Explained – LEFT JOIN creates NULLs; here is how to use them.
• SQL WHERE vs HAVING – filtering rows versus groups.
• SQL Window Functions Explained – LAG returns NULL on the first row.