Basics of selection I · Topic 6 Free
IS NULL, BETWEEN, IN
Data model — the tables you will query
How the tables join
| Relationship | Cardinality | Join condition |
|---|---|---|
| students.cohort_id → cohorts.id | many students → one cohort | JOIN cohorts ON students.cohort_id = cohorts.id |
| enrollments.student_id → students.id | many enrollments → one student | JOIN students ON enrollments.student_id = students.id |
| enrollments.course_id → courses.id | many enrollments → one course | JOIN courses ON enrollments.course_id = courses.id |
| job_offers.student_id → students.id | many job offers → one student | JOIN students ON job_offers.student_id = students.id |
In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.
Three small operators that make WHERE clauses shorter and clearer.
NULL and IS NULL
NULL means “no value”. It is not zero, not an empty string — it is the absence of data. In our dataset, enrollments.completed_at is NULL while the course is still in progress.
The trap every beginner hits:
WHERE completed_at = NULL -- WRONG: always returns nothing
WHERE completed_at IS NULL -- correctAny comparison with NULL (=, <>, >, …) returns NULL — neither true nor false — so the row is filtered out. The only correct tests are:
WHERE score IS NULL
WHERE score IS NOT NULLUseful companion: COALESCE(score, 0) returns the first non-NULL argument — a common way to substitute a default.
BETWEEN
A shorthand for a range check, inclusive on both ends:
WHERE salary_usd BETWEEN 90000 AND 110000
-- same as: salary_usd >= 90000 AND salary_usd <= 110000Works with dates too:
WHERE joined_at BETWEEN '2024-01-01' AND '2024-12-31'Careful with timestamps: BETWEEN ... AND '2024-12-31' cuts off anything after midnight on the 31st. For date ranges, >= start AND < next_day is safer.
IN
Checks membership in a list. Cleaner than a chain of ORs:
WHERE city IN ('Vancouver', 'Toronto', 'Calgary')
-- same as: city = 'Vancouver' OR city = 'Toronto' OR city = 'Calgary'NOT IN excludes the list — but beware: if the list contains a NULL, NOT IN returns no rows at all. This becomes important with subqueries later.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.