LEARN. PRACTICE. GROW.Each clause has a purpose
SELECT defines returned expressions. FROM identifies data sources. WHERE filters rows. ORDER BY establishes ordering. Without ORDER BY, do not rely on the order observed in a previous result. Select only necessary columns to make the contract clear.
Handle absence
NULL represents absence or unknown information according to the model. Test it with IS NULL or IS NOT NULL, not = NULL. WHERE retains rows only when the condition is true; an unknown result does not pass the filter.
Guided workplace application
For an incident dashboard, write the contract first: one row per incident, open incidents only, and creation-time ordering with an ID tie-breaker. Use resolved_at IS NULL according to the model convention and select necessary columns. Absence is not a fictional date and should not be compared using = NULL. When an exclusion list may contain NULL, reason through three cases: a matching ID, a different ID, and an unknown ID. NOT IN can produce unknown instead of true. If the rule excludes matches only and the outer ID is mandatory, correlated NOT EXISTS expresses that intention. Also test timestamp ties. A total order avoids arbitrary ties but does not freeze data changed between pages.
SELECT id, title
FROM incidents
WHERE resolved_at IS NULL
ORDER BY created_at, idA dashboard shows open incidents. Ordering by date and ID gives an explicit tie-breaker for incidents with the same timestamp, avoiding unstable pagination from ties.
Common pitfalls
Relying on observed ordering; using = NULL; overlooking NULL in NOT IN.
Related topics: Join without multiplying mistakes · Count and group carefully
Ordering must be requested; NULL needs dedicated predicates.
Reference: PostgreSQL 18: Comparison functions and operators · PostgreSQL 18 reference semantics; DR SQL 2026.2