# Module 21 — CASE (Conditional Logic) `CASE` is SQL's if/else. It lets you transform data based on conditions as it's being retrieved. By the end of this module you will: - Add conditional labels to your query results - Use CASE in SELECT, ORDER BY, and aggregates - Handle complex multi-condition logic --- ## 1. Searched CASE (Most Common) **What it is:** The searched CASE expression evaluates a series of boolean conditions (WHEN) and returns the result (THEN) of the first condition that's true. If no condition matches, ELSE provides a default. It's SQL's equivalent of if/else if/else. **Why we use it:** Raw data often needs transformation for display — converting status codes to labels, categorizing values, or applying business logic. CASE does this in the database, keeping application code clean. **When we use it:** When displaying human-readable labels, categorizing data into tiers/buckets, applying conditional formatting, or implementing business logic in queries. ```sql SELECT name, price, CASE WHEN price > 50000 THEN 'Premium' WHEN price > 10000 THEN 'Mid-Range' ELSE 'Budget' END AS price_category FROM products; ``` ### How it works 1. Postgres evaluates each `WHEN` condition top to bottom 2. The first match wins 3. If no match, `ELSE` is used 4. `END` closes the CASE block ### CASE Logic Flow --- ## 2. Simple CASE (Equality Check) **What it is:** The simple CASE syntax compares a single expression against multiple values using equality. It's a shorthand for searched CASE when all conditions check the same column. **Why we use it:** When mapping codes to labels — status='active' → 'Active', role='admin' → 'Administrator'. It's more concise than writing `WHEN status = 'active'` for each value. **When we use it:** When converting enum-like values to display labels, mapping codes to descriptions, or any one-to-one value mapping. ```sql SELECT name, status, CASE status WHEN 'active' THEN '✅ Active' WHEN 'pending' THEN '⏳ Pending' WHEN 'banned' THEN '❌ Banned' ELSE '❓ Unknown' END AS status_label FROM users; ``` --- ## 3. CASE in ORDER BY **What it is:** You can use CASE inside ORDER BY to create custom sort orders. Instead of sorting alphabetically or numerically, you define the exact order you want — "high first, medium second, low third". **Why we use it:** Default sorting (alphabetical, numerical) doesn't always match business logic. CASE lets you define priority-based ordering — "urgent tasks first", "active users before inactive", "premium products at the top". **When we use it:** When building priority-based lists, when the sort order doesn't match alphabetical/numerical order, or when implementing custom ranking logic. ```sql -- Sort by priority: high first, medium second, low last SELECT name, priority FROM tasks ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 END; ``` --- ## 4. CASE in Aggregates **What it is:** CASE inside aggregate functions (SUM, COUNT) lets you count or sum based on conditions — creating pivot tables, conditional counts, or tiered summaries without multiple queries. **Why we use it:** Instead of running separate queries for each condition, CASE in aggregates computes everything in one pass — "count of active users", "count of inactive users", "total revenue from premium products". **When we use it:** When building pivot tables, conditional summaries, tiered reports, or any time you need different counts/sums for different conditions in one query. ```sql -- Count products per price tier SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE price > 50000) AS premium, COUNT(*) FILTER (WHERE price <= 50000 AND price > 10000) AS mid_range, COUNT(*) FILTER (WHERE price <= 10000) AS budget FROM products; -- Pivot with CASE SELECT category, SUM(CASE WHEN price > 50000 THEN 1 ELSE 0 END) AS expensive_count, SUM(CASE WHEN price <= 50000 THEN 1 ELSE 0 END) AS affordable_count FROM products GROUP BY category; ``` --- ## 5. CASE with NULL Handling **What it is:** CASE can handle NULL values explicitly — checking for IS NULL, providing fallback values, or converting NULLs to meaningful defaults. **Why we use it:** NULL values often need special handling in display — "No description" instead of empty, "Unknown" instead of NULL. CASE provides fine-grained control over NULL display. **When we use it:** When displaying data that may contain NULLs, when building user-friendly output, or when NULL values need different treatment than empty strings. ```sql SELECT name, CASE WHEN description IS NULL THEN 'No description' WHEN description = '' THEN 'Empty description' ELSE description END AS safe_description FROM products; ``` --- ## Common Mistakes to Avoid 1. **Forgetting END** — Every CASE needs an END 2. **Wrong order of WHEN clauses** — First match wins, so put specific conditions first 3. **Missing ELSE** — Without ELSE, unmatched rows return NULL --- ## Practice Exercise Using your `courses` table: 1. Label courses as 'Active' if published, 'Draft' if not 2. Create a price tier: 'Free' (0), 'Cheap' (<2000), 'Moderate' (<5000), 'Expensive' (5000+) 3. Use CASE in ORDER BY to sort published courses before drafts 4. Count courses per price tier using CASE in aggregates **Next up:** NULL values can be tricky. In the next module, you'll learn `COALESCE` and `NULLIF` for robust NULL handling.