Skip to content
aviral gupta
Get in touch

// B2.4 · ~30 min · Beginner

NULL, IS NULL and COALESCE

After this lesson you can find the missing values in a table, keep them from silently removing rows, and show a sensible default in their place.

Lesson 4 of 5 in B2 Querying one table

You will be able to

  • Explain what NULL means and predict comparisons and WHERE conditions that meet a NULL
  • Find missing values with IS NULL, IS NOT NULL and IS DISTINCT FROM, and avoid the NOT IN trap
  • Replace NULL with COALESCE, turn a value into NULL with NULLIF, and join text with concat()

  1. Warm-up · Activity 1 of 7

    Warm-up: Linus gave no email address, and his row was inserted with NULL in the email column. What does the column hold?

  2. Predict · Activity 2 of 7

    Predict before you read on. Exactly one member, Linus, has no email. What does this return?

    SELECT count(*) FROM members WHERE email = NULL;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the query finds the member without an email.

    SELECT name FROM members WHERE email ____ NULL;
    SELECT name FROM members WHERE email NULL;

  4. Practice · Activity 4 of 7

    Match each expression to its result.

  5. Practice · Activity 5 of 7

    Two loans came back, on 2026-09-10 and 2026-09-20; the other three are still out (returned_on is NULL). What does this return?

    SELECT count(*) FROM loans WHERE returned_on > '2026-09-15';

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Ada and Linus live in Berlin, Grace in Munich, and Margaret’s city is NULL. Someone added NULL to the list "to leave out the members without a city". What does this return?

    SELECT count(*) FROM members WHERE city NOT IN ('Munich', NULL);

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example, write two queries: every member with name, email and city, where a missing email shows as (none) and a missing city as unknown; and the ids of the loans that are still open. Then replace IS NULL with = NULL, predict the result, and run it.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Finding and filling the gaps in the library

setup.sql loads the library of module B1: books, members and loans. main.sql finds the members with a missing value, shows a contact list with the gaps filled, and lists the loans that are still open. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Who is missing an email or a city?
SELECT name, email, city
FROM members
WHERE email IS NULL OR city IS NULL
ORDER BY id;

-- 2. The same gaps, filled for display.
SELECT name,
       coalesce(email, '(no email)') AS contact,
       name || ' (' || city || ')' AS with_pipes,
       concat(name, ' (', city, ')') AS with_concat
FROM members
ORDER BY id;

-- 3. Loans that are still open.
SELECT id, book_id, member_id, loaned_on
FROM loans
WHERE returned_on IS NULL
ORDER BY id;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

   name   |        email         |  city
----------+----------------------+--------
 Linus    |                      | Berlin
 Margaret | margaret@example.com |
(2 rows)

   name   |       contact        |   with_pipes   |  with_concat
----------+----------------------+----------------+----------------
 Ada      | ada@example.com      | Ada (Berlin)   | Ada (Berlin)
 Grace    | grace@example.com    | Grace (Munich) | Grace (Munich)
 Linus    | (no email)           | Linus (Berlin) | Linus (Berlin)
 Margaret | margaret@example.com |                | Margaret ()
(4 rows)

 id | book_id | member_id | loaned_on
----+---------+-----------+------------
  2 |       3 |         1 | 2026-09-12
  4 |       1 |         3 | 2026-09-15
  5 |       6 |         2 | 2026-09-18
(3 rows)
  • psql shows NULL as an empty cell: Linus’s email and Margaret’s city in the first result.
  • coalesce() puts (no email) where the email is NULL and leaves the other emails as they are.
  • With ||, one NULL piece makes the whole label NULL, so Margaret's with_pipes cell is empty. concat() skips the NULL and returns Margaret ().
  • returned_on IS NULL finds the three open loans; returned_on = NULL would find none.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

A contact list and a loan status without gaps

Write two queries. First: every member's name, email and city, sorted by id, where a missing email shows as (no email) and a missing city as unknown; keep the column names name, email and city. Second: every loan's id and loaned_on, plus a column returned that shows the return date, or open when there is none, sorted by id.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    coalesce(email, '(no email)') AS email keeps the column name email.

  2. Hint 2

    returned_on is a date and 'open' is text: coalesce needs one type for both.

  3. Hint 3

    Cast the date to text first: coalesce(returned_on::text, 'open').

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT name,
       coalesce(email, '(no email)') AS email,
       coalesce(city, 'unknown') AS city
FROM members
ORDER BY id;

SELECT id, loaned_on, coalesce(returned_on::text, 'open') AS returned
FROM loans
ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
SELECT * FROM members ORDER BY id;

test.sql

-- test: The contact list fills the gaps, then every loan shows its return date or open
-- output:
--    name   |        email         |  city
-- ----------+----------------------+---------
--  Ada      | ada@example.com      | Berlin
--  Grace    | grace@example.com    | Munich
--  Linus    | (no email)           | Berlin
--  Margaret | margaret@example.com | unknown
-- (4 rows)
--
--  id | loaned_on  |  returned
-- ----+------------+------------
--   1 | 2026-09-01 | 2026-09-10
--   2 | 2026-09-12 | open
--   3 | 2026-09-05 | 2026-09-20
--   4 | 2026-09-15 | open
--   5 | 2026-09-18 | open
-- (5 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Everyone outside Berlin

The starter should list every member who does not live in Berlin, with name and city, sorted by id. It finds Grace but misses Margaret, whose city is unknown and so certainly not recorded as Berlin. Fix the condition so that both are listed. Try both repairs: IS DISTINCT FROM, and an extra OR … IS NULL.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    For Margaret, city <> 'Berlin' is NULL, not true, so WHERE drops her.

  2. Hint 2

    IS DISTINCT FROM treats NULL as a value of its own: NULL IS DISTINCT FROM 'Berlin' is true.

  3. Hint 3

    Or keep <> and add the missing case: city <> 'Berlin' OR city IS NULL.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Members who do not live in Berlin.
SELECT name, city
FROM members
WHERE city IS DISTINCT FROM 'Berlin'
ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Members who do not live in Berlin.
SELECT name, city
FROM members
WHERE city <> 'Berlin'
ORDER BY id;

test.sql

-- test: Grace and Margaret are listed, Margaret with an empty city
-- output:
--    name   |  city
-- ----------+--------
--  Grace    | Munich
--  Margaret |
-- (2 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A text default for a number column

SELECT title, coalesce(year, 'unknown') AS year
FROM books;

What psql prints

ERROR:  invalid input syntax for type integer: "unknown"

Why, and the fix

coalesce() returns one type for all its arguments. year is an integer, so PostgreSQL tries to read 'unknown' as an integer, and fails. Make both sides text: coalesce(year::text, 'unknown'). Or pick a default of the column's own type, such as coalesce(copies, 0).

IS as a general "equals"

SELECT name FROM members WHERE city IS 'Berlin';

What psql prints

ERROR:  syntax error at or near "'Berlin'"

Why, and the fix

IS works only with a fixed set of words: IS NULL, IS NOT NULL, IS TRUE, IS DISTINCT FROM and a few more. It is not another way to write =. For a value, write city = 'Berlin'; to treat NULL as a value of its own, write city IS NOT DISTINCT FROM 'Berlin'.

Dividing by a column that can be 0

SELECT member, pages * 60 / minutes AS pages_per_hour
FROM readings;

What psql prints

ERROR:  division by zero

Why, and the fix

One row with minutes = 0 stops the whole query, so no row is shown at all. Divide by NULLIF(minutes, 0) instead: for that row the divisor is NULL, and the result is NULL rather than an error. Show a default afterwards with coalesce() if the report needs one.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

NULL means unknown, and unknown spreads

Linus has no email: the column holds NULL, not an empty string and not the word 'NULL'. NULL means the value is unknown, and almost everything done with an unknown is unknown too. email = NULL is not true but NULL, and so is NULL = NULL: two unknowns are not known to be equal. 1 + NULL is NULL, and 'Linus <' || NULL is NULL. SQL's logic has three values: true, false and unknown. NOT NULL stays NULL; NULL AND false is false, NULL OR true is true, because the unknown part cannot change those results. WHERE keeps a row only when its condition is true, so a condition that is NULL drops the row, as false does.

Ask for NULL with IS NULL, never with =

To find missing values, write email IS NULL; to find present ones, email IS NOT NULL. Both are always true or false, never NULL. A condition such as city <> 'Berlin' quietly leaves out Margaret, whose city is unknown: for her the comparison is NULL. When NULL should count as different, write city IS DISTINCT FROM 'Berlin', or add OR city IS NULL. NOT IN hides the sharpest trap: city NOT IN ('Munich', NULL) means city <> 'Munich' AND city <> NULL, and the second part is never true, so no row at all comes back. Keep NULL out of NOT IN lists, and prefer a positive condition where you can.

COALESCE fills gaps, NULLIF makes them

coalesce(email, '(no email)') returns the first of its arguments that is not NULL: the email when there is one, otherwise the text. All its arguments need one common type, so coalesce(year, 'unknown') fails for an integer year; write coalesce(year::text, 'unknown'). NULLIF works the other way: nullif(minutes, 0) is NULL when minutes is 0, and minutes otherwise. Dividing by it turns a division by zero, which stops the query, into NULL for that row. For text, || gives NULL as soon as one piece is NULL, while concat(name, ' (', city, ')') skips the NULL pieces and still returns the rest.

Sources

Last reviewed September 30, 2026