CASE, COALESCE, NULLIF & Type Conversion — SQL Course

What you'll learn

Express conditional business logic, supply fallbacks, prevent invalid arithmetic, and convert values explicitly. 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 CASE expressions to a realistic data question
Apply COALESCE to a realistic data question
Apply NULLIF to a realistic data question
Apply CAST 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
CASE	A conditional expression producing one value	Keep branches type-compatible and cover the intended ELSE
NULLIF	NULL when two expressions are equal	Use to turn known invalid divisors or sentinels into missing values
CAST	Explicit type conversion	Convert at trusted boundaries and handle invalid source data separately

Professional workflow

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

State the conditional expression 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

Calculate a safe status and ratio

NULLIF prevents division by zero and CASE expresses mutually exclusive stock bands.

inventory_health.sql

SELECT
  product_id,
  quantity,
  reorder_level,
  CASE
    WHEN quantity = 0 THEN 'out'
    WHEN quantity <= reorder_level THEN 'low'
    ELSE 'healthy'
  END AS stock_status,
  quantity::numeric / NULLIF(reorder_level, 0) AS coverage_ratio
FROM inventory.stock;

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

COALESCE can hide the difference between missing, zero, empty, and not-applicable. Substitute only when the business meanings match.

Independent workshop

Build a review-ready conditional expression lab against the course commerce dataset.

Your finished workshop must include:

CASE expressions
COALESCE
NULLIF
CAST
PostgreSQL :: syntax
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

CASE: Keep branches type-compatible and cover the intended ELSE
NULLIF: Use to turn known invalid divisors or sentinels into missing values
CAST: Convert at trusted boundaries and handle invalid source data separately

Quick check

1. Which rule best applies to CASE?

2. Which rule best applies to NULLIF?

3. Which rule best applies to CAST?

Next: Phase Project: Build a Clean Inventory Schema