SQL CASE

SQL
POSTGRES
INTERACTIVE

Complete SQL CASE Expression Technical Guide

1. Fundamental Syntax 
-- Simple CASE (equality comparison)
SELECT product_name,
  CASE category_id
    WHEN 1 THEN 'Electronics'
    WHEN 2 THEN 'Clothing'
    WHEN 3 THEN 'Home Goods'
    ELSE 'Other'
  END AS category_name
FROM products;

-- Searched CASE (conditional logic)
SELECT order_id, total_amount,
  CASE
    WHEN total_amount > 1000 THEN 'Premium'
    WHEN total_amount > 500 THEN 'Standard'
    WHEN total_amount > 0 THEN 'Basic'
    ELSE 'Invalid'
  END AS order_tier
FROM orders;
  • Two Forms: Simple (value comparison) and Searched (conditional expressions)
  • Evaluation Order: Conditions are checked sequentially, first match wins
  • ELSE Clause: Optional but recommended, defaults to NULL if omitted
  • Type Consistency: All result expressions must return compatible data types
2. Practical Applications
Data Categorization
-- Customer segmentation
SELECT customer_id,
  CASE
    WHEN purchase_count > 50 THEN 'Gold'
    WHEN purchase_count > 20 THEN 'Silver'
    WHEN purchase_count > 0 THEN 'Bronze'
    ELSE 'Prospect'
  END AS customer_tier,
  CASE
    WHEN last_purchase_date > CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
    WHEN last_purchase_date > CURRENT_DATE - INTERVAL '90 days' THEN 'Lapsing'
    ELSE 'Inactive'
  END AS engagement_status
FROM customers;
NULL Handling
-- Special NULL handling
SELECT employee_id,
  CASE
    WHEN termination_date IS NULL THEN 'Active'
    WHEN termination_date > CURRENT_DATE THEN 'Notice Period'
    ELSE 'Terminated'
  END AS employment_status,
  CASE
    WHEN COALESCE(bonus_eligibility, FALSE) THEN 'Eligible'
    ELSE 'Not Eligible'
  END AS bonus_status
FROM employees;
3. Advanced Patterns
Nested CASE Expressions
-- Complex business logic
SELECT product_id,
  CASE
    WHEN discontinued THEN 'Discontinued'
    WHEN stock_quantity = 0 THEN
      CASE
        WHEN restock_date IS NULL THEN 'Out of Stock - No Restock'
        WHEN restock_date > CURRENT_DATE + 30 THEN 'Out of Stock - Delayed'
        ELSE 'Out of Stock - Restocking Soon'
      END
    WHEN stock_quantity < 10 THEN 'Low Stock'
    ELSE 'In Stock'
  END AS inventory_status
FROM products;
CASE in Aggregate Functions
-- Conditional aggregation
SELECT department_id,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count,
  AVG(CASE WHEN salary > 0 THEN salary ELSE NULL END) AS avg_salary
FROM employees
GROUP BY department_id;
4. Performance Optimization
Index Usage with CASE
-- Index-friendly CASE expressions
SELECT * FROM orders
WHERE
  CASE
    WHEN status = 'Shipped' THEN shipped_date
    WHEN status = 'Delivered' THEN delivered_date
    ELSE order_date
  END > '2023-01-01';

-- Better approach (rewrite for index usage)
SELECT * FROM orders
WHERE (status = 'Shipped' AND shipped_date > '2023-01-01')
   OR (status = 'Delivered' AND delivered_date > '2023-01-01')
   OR (status NOT IN ('Shipped','Delivered') AND order_date > '2023-01-01');
Materialized CASE Results
-- Store computed values for frequent queries
CREATE TABLE customer_segments AS
SELECT customer_id,
  CASE
    WHEN lifetime_value > 5000 THEN 'High Value'
    WHEN lifetime_value > 1000 THEN 'Medium Value'
    ELSE 'Low Value'
  END AS value_segment
FROM customers;

CREATE INDEX idx_segment ON customer_segments(value_segment);
5. Platform-Specific Extensions
  • PostgreSQL: CASE with FILTER clause for aggregates
  • Oracle: DECODE function as CASE alternative
  • SQL Server: IIF and CHOOSE shorthand functions
  • MySQL: IF() function for simple conditions
Vendor-Specific Examples
-- PostgreSQL FILTER with CASE
SELECT
  COUNT(*) FILTER (WHERE status = 'Active') AS active_count,
  COUNT(*) FILTER (WHERE status = 'Inactive') AS inactive_count
FROM users;

-- SQL Server IIF
SELECT product_name,
  IIF(discontinued = 1, 'Discontinued', 'Available') AS availability
FROM products;

-- Oracle DECODE
SELECT employee_name,
  DECODE(department_id, 10, 'Finance', 20, 'HR', 'Other') AS dept_name
FROM employees;

Key Additional Insights:

Expression Evaluation

  1. CASE stops evaluating when a condition is met (short-circuit evaluation)
  2. Complex conditions should be ordered from most to least restrictive
  3. Conditions with expensive functions should be placed later

Alternatives to CASE

-- COALESCE/NULLIF for NULL handling
SELECT COALESCE(discount, 0) AS effective_discount FROM orders;

-- UNION ALL for complex conditional logic
SELECT 'High' AS priority, * FROM tasks WHERE urgency > 8
UNION ALL SELECT 'Medium', * FROM tasks WHERE urgency BETWEEN 5 AND 8
UNION ALL SELECT 'Low', * FROM tasks WHERE urgency < 5;

Dynamic SQL with CASE

-- Building conditional queries
SET @sql = CONCAT('SELECT * FROM products WHERE ', 
  CASE 
    WHEN @category IS NOT NULL THEN CONCAT('category_id = ', @category)
    ELSE '1=1'
  END);
PREPARE stmt FROM @sql;
EXECUTE stmt;

Window Functions with CASE

-- Conditional window calculations
SELECT employee_id,
  SUM(CASE WHEN year = 2023 THEN sales ELSE 0 END) OVER () AS total_2023_sales
FROM sales_data;

Common Pitfalls

  1. Forgetting ELSE can lead to unexpected NULLs
  2. Over-nesting CASE expressions reduces readability
  3. Using CASE in WHERE clauses often prevents index usage
  4. Assuming consistent evaluation order across databases

Advanced Patterns

  1. Pivoting data with CASE and aggregation
  2. Dynamic column selection
  3. State machine implementation
  4. Complex business rule encapsulation