NULL & Three-Valued Logic — SQL Course

What you'll learn

Reason correctly about missing and unknown values using IS NULL, COALESCE, and SQL's three-valued logic. The lab uses PostgreSQL while identifying the semantics that transfer to other relational systems.

By the end of this lesson, you'll be able to:

Apply Meaning of NULL to a realistic data question
Apply UNKNOWN truth value to a realistic data question
Apply IS NULL to a realistic data question
Apply COALESCE to a realistic data question

Core mental model

SQL is declarative: describe the result or invariant you need, then let the database choose a physical execution strategy. Use this table to connect syntax to design decisions.

Concept	What it means	Decision rule
NULL	A marker for missing or unknown information	Model its business meaning and avoid sentinel values
UNKNOWN	The third logical result produced by comparisons with NULL	Use IS NULL rather than equals NULL
COALESCE	The first non-NULL expression	Use for presentation fallbacks only when meanings are compatible

Professional workflow

Work from a defined question and result grain, then verify correctness before performance.

State the missing-value policy question and the exact grain of the expected result.
Inspect table definitions, keys, constraints, representative values, and row counts.
Write the smallest correct query with explicit columns, aliases, and predicates.
Test missing, duplicate, boundary, and NULL cases before trusting the result.
Inspect the execution plan or affected rows when cost or data change matters.
Save the query with its assumptions, parameters, verification, and recovery notes.

Make results explainable

Keep each query in a saved SQL file with a short statement of its purpose, expected grain, assumptions, and verification query.

Guided SQL lab

Make missing values explicit

IS NULL tests missing facts while COALESCE provides a display label without changing stored data.

missing_publishers.sql

SELECT
  b.id,
  b.title,
  COALESCE(p.name, 'Independent') AS publisher_label
FROM course.books AS b
LEFT JOIN course.publishers AS p ON p.id = b.publisher_id
WHERE b.discontinued_at IS NULL;

Production practice

Contract

Define the expected row grain, inputs, output columns, invariants, and failure or empty-result behavior before writing SQL.

Verification

Use representative fixtures and independent row-count, uniqueness, NULL, and boundary checks; compare plans when cost matters.

Operations

Save reviewed SQL with explicit schema names where appropriate, bounded scope, least privilege, observability, and a recovery path for changes.

Common failure mode

NOT IN returns no true result when its subquery contains NULL. Prefer NOT EXISTS for anti-joins unless NULL behavior is proven.

Independent workshop

Build a review-ready missing-value policy lab against the course commerce dataset.

Your finished workshop must include:

Meaning of NULL
UNKNOWN truth value
IS NULL
COALESCE
NULL-safe comparisons
Verification notes and edge-case evidence

Definition of done

Run the expected case and at least two edge cases, verify row counts and grain, and add comments explaining any vendor-specific behavior.

Recap & quick check

Key takeaways

NULL: Model its business meaning and avoid sentinel values
UNKNOWN: Use IS NULL rather than equals NULL
COALESCE: Use for presentation fallbacks only when meanings are compatible

Quick check

1. Which rule best applies to NULL?

2. Which rule best applies to UNKNOWN?

3. Which rule best applies to COALESCE?

Next: ORDER BY, LIMIT & Reliable Pagination