Postgres in depth · Chapter 7 of 65
The missing phone number
By the end of this chapter you can find missing values, predict how they behave in comparisons and conditions, replace them with something readable, and avoid the NOT IN trap that has fooled many experienced developers. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Day one: an empty server
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Sana is setting up SMS reminders. Her first list of "students with no phone" came back empty, although she knows of at least three. Then Arjun's count of "students outside Mumbai" was two short, and his list of students in new cities came back with no rows at all. No query failed. Every one of them returned a wrong answer, quietly.
All three met the same thing: NULL, the value that is not there. The operators from chapter 6 treat it in a way that surprises everyone once. This chapter makes the surprise predictable.
Run lab load day-one to have LCO's courses and students in your own database. The examples use courses; the tasks use students.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
no-phone.sql: no phone on file
Sana's SMS tool needs the students whose phone is missing altogether (NULL).
Return two columns: name and email. Any order.
Om Joshi's phone is an empty text, which is a value, so he is not on this list.
- Task 2
contacts.sql: the contact list
Sana's contact list shows every student with their phone, and the words no phone when there is none: when the phone is missing (NULL) or is an empty text.
Return two columns: name and phone. One row per student, all 12.
Order: by name, A to Z.
Careful: an empty text is not NULL, so a replacement that only handles NULL leaves Om Joshi's phone blank.
- Task 3
outside-mumbai.sql: not known to be in Mumbai
Arjun counts the students who are not known to live in Mumbai. Students with no city on file count too: nobody knows they live in Mumbai.
Return two columns: name and city. Any order.
Careful: comparing a missing city with not-equal gives unknown, and the row is dropped.
- Task 4 · interview question
Interview: new-cities.sql, the NOT IN trap
LCO plans meetups in new cities. Arjun wants the students who live in a city where none of the first-week students live. The first-week students are those who signed up before 2026-01-10.
Only students whose city is on file count. Return two columns: name and city. Any order.
Careful: some first-week students have no city. Use a query inside the brackets of the not-in test, and make sure its list holds no missing value.
What you will learn
- NULL: a missing value, and why it is not 0 or empty text.
- Three-valued logic: true, false and unknown, and what WHERE keeps.
- IS NULL and IS DISTINCT FROM: comparisons that handle NULL.
- COALESCE and NULLIF: replacing NULL, and making NULL on purpose.
- The NOT IN trap: one NULL in a list empties the answer.
More in Day one: an empty server
Build The missing phone number yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.