0Pricing
SQL Academy · Lesson

IS NULL, IS NOT NULL and COALESCE

Detect NULLs with IS NULL, replace them with COALESCE / NULLIF, and design queries that survive missing data gracefully.

IS NULL, IS NOT NULL and COALESCE is a free SQL Academy lesson on CoddyKit — lesson 4 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 NULL

Use IS NULL and IS NOT NULL — never = NULL:

SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

Why = NULL Fails Silently

Comparing anything to NULL yields NULL (unknown). The row is dropped — but you don't get an error, so the bug ships:

SELECT * FROM users WHERE deleted_at = NULL;
-- → returns 0 rows, always

COALESCE: First Non-NULL

COALESCE takes any number of arguments and returns the first one that isn't NULL:

SELECT id,
       COALESCE(nickname, full_name, email, 'Anonymous') AS display_name
FROM users;

COALESCE in WHERE

Use COALESCE to turn NULL into a sortable / comparable default:

-- Treat NULL last_login_at as the epoch:
SELECT * FROM users
ORDER BY COALESCE(last_login_at, '1970-01-01') DESC;

NULLIF: The Inverse

NULLIF(a, b) returns NULL when a = b, otherwise a:

-- Treat 0 as missing so AVG ignores it:
SELECT AVG(NULLIF(price, 0)) FROM products;

-- Convert empty string to NULL on insert:
INSERT INTO contacts (phone) VALUES (NULLIF(:phone, ''));

CASE for Conditional Defaults

CASE is the general-purpose alternative when COALESCE isn't flexible enough:

SELECT id,
  CASE
    WHEN status IS NULL OR status = '' THEN 'unknown'
    WHEN status = 'A' THEN 'active'
    ELSE status
  END AS status_label
FROM users;

NULL in CHECK and Constraints

A CHECK constraint passes when the condition is NULL. Be explicit if you need to forbid NULL:

CREATE TABLE prices (
  amount NUMERIC(10,2),
  CHECK (amount > 0)        -- amount = NULL passes!
);

-- Better:
CREATE TABLE prices2 (
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0)
);

NULL in JOIN ON

Row pairs where the join key is NULL never match — NULL = NULL is NULL. Use IS NOT DISTINCT FROM if you want NULL to equal NULL:

SELECT * FROM a JOIN b ON a.key IS NOT DISTINCT FROM b.key;

NULLs and UNIQUE

Standard SQL treats NULLs as distinct in UNIQUE constraints — you can have many NULL rows:

CREATE TABLE invites (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);
INSERT INTO invites (email) VALUES (NULL), (NULL); -- both succeed!

NULLs and Aggregates Recap

Aggregates (except COUNT(*)) skip NULLs.

SELECT COUNT(*)            FROM users;  -- all rows
SELECT COUNT(email)        FROM users;  -- rows where email IS NOT NULL
SELECT COUNT(DISTINCT id)  FROM users;

Always Declare NOT NULL When You Can

NOT NULL is the simplest and strongest correctness guarantee. Add it to every column that should never be empty.

Recap

NULL handling is the single biggest source of subtle SQL bugs.

  • Test with IS NULL
  • Replace with COALESCE
  • Sentinel-to-NULL with NULLIF
  • Default to NOT NULL

Quick Check

Which expression returns email if not NULL, otherwise the literal 'no-email'?

Frequently asked questions

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

Yes — the full text of “IS NULL, IS NOT NULL and COALESCE” 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, IS NOT NULL and COALESCE”?

Detect NULLs with IS NULL, replace them with COALESCE / NULLIF, and design queries that survive missing data gracefully. 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 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “IS NULL, IS NOT NULL and COALESCE” 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

  1. LIKE Patterns and Wildcards
  2. IN and NOT IN for Sets
  3. BETWEEN for Ranges
  4. IS NULL, IS NOT NULL and COALESCE
← Back to SQL Academy