SQL Academy · Lesson

IS NULL and IS NOT NULL

Test for missing values correctly.

IS NULL and IS NOT NULL is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Testing for Missing Values

Since comparisons with NULL always return unknown, SQL gives you two dedicated operators to test for missing values: IS NULL and IS NOT NULL.

These are the only reliable way to find or exclude NULLs. In this lesson you'll learn to use them correctly.

-- Rows where phone is missing
SELECT name FROM customers WHERE phone IS NULL;

-- Rows where phone is present
SELECT name FROM customers WHERE phone IS NOT NULL;

Why = NULL Fails

It's tempting to write WHERE phone = NULL, but it never matches anything. The condition evaluates to unknown for every row, and WHERE keeps only true rows.

The result is an empty set — a silent bug, since no error is raised.

-- Always returns 0 rows, even if NULLs exist
SELECT * FROM customers WHERE phone = NULL;

-- The fix
SELECT * FROM customers WHERE phone IS NULL;

IS NULL in Action

IS NULL returns true exactly when the value is missing, and false otherwise. It never returns unknown.

That makes it safe to use anywhere you need a clean true/false result.

SELECT id, name, (phone IS NULL) AS missing_phone
FROM customers;

-- id | name  | missing_phone
-- ---+-------+--------------
--  1 | Alice | f
--  2 | Bob   | t
--  3 | Carol | t

IS NOT NULL in Action

IS NOT NULL is the exact opposite: true when the value is present, false when it is missing.

Use it to filter to rows that actually have data, such as customers you can call.

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

-- name  | phone
-- ------+----------
-- Alice | 555-0101

Combining with AND / OR

You can combine NULL tests with other conditions using AND and OR.

For example, find active customers who still need a phone number on file — a common data-quality query.

SELECT id, name
FROM customers
WHERE is_active = true
  AND phone IS NULL;

-- Active customers missing a phone number

NULL in NOT IN: A Trap

NOT IN behaves badly when the list contains a NULL. If any value in the set is NULL, NOT IN can return unknown for every row, dropping results you expected.

Prefer NOT EXISTS or filter NULLs out of the subquery first.

-- Risky: if blocked_ids contains a NULL, this returns nothing
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blocked);

-- Safer
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blocked WHERE user_id IS NOT NULL);

IS DISTINCT FROM

PostgreSQL offers IS DISTINCT FROM and IS NOT DISTINCT FROM — NULL-safe comparison operators.

Unlike =, they treat two NULLs as equal and a NULL versus a value as different, always returning true or false, never unknown.

SELECT
  NULL IS NOT DISTINCT FROM NULL AS a, -- true: both NULL = same
  NULL IS DISTINCT FROM 5        AS b, -- true: NULL differs from 5
  5 IS DISTINCT FROM 5          AS c; -- false: same value

Comparing Two Nullable Columns

When comparing two columns that might both be NULL, plain = misses the case where both are NULL. IS NOT DISTINCT FROM handles it cleanly.

This is great for finding rows that haven't changed, even when the value is unknown.

-- Rows where old and new phone are 'the same',
-- counting NULL = NULL as same
SELECT id
FROM customer_changes
WHERE old_phone IS NOT DISTINCT FROM new_phone;

Counting NULLs

A practical use of IS NULL is auditing data quality — counting how many rows are missing a value.

Combine it with FILTER (PostgreSQL) or a CASE inside COUNT to count NULLs and non-NULLs side by side.

SELECT
  count(*) AS total,
  count(*) FILTER (WHERE phone IS NULL)     AS missing,
  count(*) FILTER (WHERE phone IS NOT NULL) AS present
FROM customers;

NULL Checks in CHECK Constraints

You can use NULL tests inside CHECK constraints to enforce rules like "if a row is shipped, it must have a ship date".

Note: a CHECK constraint passes when its condition is true or unknown, so think carefully about NULL cases.

CREATE TABLE orders (
  id        integer PRIMARY KEY,
  status    text NOT NULL,
  ship_date date,
  CHECK (status <> 'shipped' OR ship_date IS NOT NULL)
);

Best Practices

Keep these habits to stay safe around NULLs:

Always test with IS NULL / IS NOT NULL, never = NULL.
Watch out for NOT IN with nullable subqueries.
Use IS DISTINCT FROM for NULL-safe equality.
Audit missing data with count(*) FILTER (...).

-- The reliable toolkit
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE a IS DISTINCT FROM b
WHERE a IS NOT DISTINCT FROM b

Quick Check

You want all customers whose phone column has no value. Which WHERE clause is correct?

Recap

You learned the right way to test for missing values with IS NULL and IS NOT NULL, and why = NULL never works.

You also met the NOT IN trap, the NULL-safe IS DISTINCT FROM operator, and how to audit NULLs with FILTER. Next you'll learn to replace NULLs with sensible defaults using COALESCE and NULLIF.

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

Frequently asked questions

Is the “IS NULL and IS NOT NULL” lesson free?

Yes — the full text of “IS NULL and IS NOT NULL” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “IS NULL and IS NOT NULL”?

Test for missing values correctly. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “IS NULL and IS NOT NULL” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy