Missing Values with NULL
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 8 of 20.
NULL represents a missing or unknown value. It is not zero and it is not an empty string. Ordinary comparisons with NULL produce unknown, so use IS NULL to find missing values.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, name FROM products WHERE note IS NULL ORDER BY id;Ids 1, 3 and 6 have no note. Writing note = NULL would not find them: WHERE keeps only true conditions, and equality with an unknown value is not true.
Test missing values with IS NULL or IS NOT NULL, never = NULL.
Challenge
EasyReturn id and note for products that have a note. An empty string still counts as a present note. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
2Filter the Catalog
Filter with WHERECombine ConditionsMissing Values with NULLSearch Text with ILIKEFind Dispatch CandidatesPractice on your own: PostgreSQL playground