SQL NULL and IS NULL Explained for Beginners

What is NULL in SQL, and how do you check for it? NULL means "no value" or "unknown". To find it, you write IS NULL, not = NULL. This is one of the first things that confuses beginners, so this guide explains it in plain words, step by step.

You only need to know the basics of SELECT and WHERE. If you are new to them, read our beginner guide first (link below). A database is a place that stores data in tables. A table has rows (one record each) and columns (one kind of fact each).

What does NULL mean in SQL?

NULL is a special marker. It does not mean zero. It does not mean an empty text. It means that the value is missing or not known. Think of a form where someone left a box blank: you do not know the answer, so the box has no value.

Here is a small table called students. Two students have no phone number saved. In those rows, the phone column is NULL.

NULL is not the same as 0 or ''. A phone number of 0 is a real value. An empty text is also a real value (text with nothing in it). NULL is the absence of any value.

Create the example table

CREATE TABLE students (
  id INT,
  name VARCHAR(50),
  phone VARCHAR(20)
);

INSERT INTO students (id, name, phone) VALUES
  (1, 'Ana', '555-0101'),
  (2, 'Ben', NULL),
  (3, 'Chen', '555-0103'),
  (4, 'Dara', NULL);

The first command makes a table with three columns. The second command adds four rows. For Ben and Dara we write the word NULL without quotes. With quotes, 'NULL' would be just a piece of text, not a real NULL.

Why = NULL does not work

Your first guess may be to write WHERE phone = NULL. Try it and you get zero rows, even though two rows are empty.

SELECT * FROM students
WHERE phone = NULL;

This returns nothing. Why? Because NULL means unknown. Is an unknown value equal to another unknown value? The database cannot say yes. The answer is also unknown, so the row is not returned. The same happens with phone <> NULL and phone != NULL. Comparing anything to NULL with = never gives a true result.

How to use IS NULL

To find rows with a missing value, use the IS NULL operator (an operator is a small word or symbol that makes a test).

SELECT id, name
FROM students
WHERE phone IS NULL;

Line 1 picks the columns id and name. Line 2 says which table to read. Line 3 keeps only rows where phone has no value. The result is Ben and Dara.

How to use IS NOT NULL

To find rows that do have a value, add the word NOT.

SELECT id, name, phone
FROM students
WHERE phone IS NOT NULL;

This returns Ana and Chen, because they both have a phone number saved.

NULL in math and text

If you add, subtract or join text with NULL, the result is NULL too. Unknown plus anything is still unknown.

SELECT 10 + NULL;
-- result: NULL

The comment after the two dashes is ignored by the database. It only helps humans read the code. The result of 10 + NULL is NULL, not 10.

Replace NULL with a friendly value using COALESCE

Sometimes you want to show a default text instead of NULL. The function COALESCE returns the first value that is not NULL.

SELECT name, COALESCE(phone, 'No phone') AS phone_display
FROM students;

COALESCE looks at phone. If it has a value, you get that value. If it is NULL, you get 'No phone'. The AS keyword gives the new column a clear name. Ben and Dara now show 'No phone'. The data in the table does not change; only the result you see changes.

NULL with COUNT

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

SELECT COUNT(*) AS all_students,
       COUNT(phone) AS with_phone
FROM students;

The first number is 4 because there are four rows. The second number is 2 because only two rows have a phone value. This is a handy way to see how much data is missing.

Quick reference

Common beginner mistakes

• Using = NULL instead of IS NULL.
• Writing 'NULL' in quotes, which makes it plain text.
• Thinking NULL means 0 or an empty text.
• Forgetting that math with NULL gives NULL.
• Forgetting that COUNT(column) skips NULL values.