Conditional logic — CASE, COALESCE, NULLIF and PIVOT patterns

Conditional expressions in SQL allow a query to apply different logic to different rows based on data values — without requiring multiple queries, procedural code, or application-layer branching. CASE expressions are the primary conditional tool, supporting both simple value matching and complex boolean predicates per branch. COALESCE selects the first non-null value from a list, handling NULL substitution elegantly. NULLIF converts a specific value to NULL, enabling division-by-zero guards and sentinel value removal. Together with conditional aggregation patterns, these tools allow transformation logic that would otherwise require procedural code to be expressed entirely in set-based SQL.

For data engineers, conditional expressions are the primary mechanism for implementing business rules in SQL transformations. Tier classification (High Value / Regular / At Risk), format detection from match reference codes, outlier flagging, data quality tagging, metric segmentation, and NULL imputation all require conditional logic that CASE expressions handle natively. Every dbt model, Airflow SQL transformation, and Spark SQL query that implements business logic uses CASE expressions as its conditional backbone.

PIVOT transforms rows into columns — converting a long-format table (one row per category per entity) into a wide-format table (one row per entity with one column per category). This transformation is essential for producing reports, creating feature matrices for machine learning, and reshaping data for downstream BI tools that expect wide-format input. PostgreSQL does not have a native PIVOT keyword, so the pattern uses conditional aggregation (SUM(CASE WHEN category = 'X' THEN value END)) — the same approach used by data engineers in every SQL-based analytical platform.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.