Case expressions

SQL queries often need to incorporate conditional logic to transform data, categorize results, or implement business rules. PostgreSQL's CASE expression provides a powerful way to add if-else logic directly into your SQL queries, enabling sophisticated data transformations and analysis. This comprehensive guide covers all aspects of using CASE expressions effectively.

Introduction to CASE Expressions

The CASE expression in PostgreSQL is SQL's version of the if-else statement. It allows you to return different values based on conditions you define, making your queries more dynamic and powerful.

Think of CASE as a way to ask: "If this condition is true, return this value; otherwise, check the next condition or return a default value."

Basic CASE Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  [WHEN ...]
  [ELSE default_result]
END

Setting Up Sample Data

Let's create a comprehensive dataset to demonstrate CASE expressions:

CREATE TABLE sales (
  sale_id SERIAL PRIMARY KEY,
  customer TEXT,
  amount NUMERIC,
  sale_date DATE,
  region TEXT,
  product_category TEXT,
  sales_rep TEXT
);

INSERT INTO sales (customer, amount, sale_date, region, product_category, sales_rep) VALUES
('Alice Corp', 100, '2023-01-01', 'North', 'Electronics', 'John'),
('Bob Industries', 300, '2023-01-02', 'South', 'Software', 'Sarah'),
('Charlie LLC', 50, '2023-01-03', 'East', 'Electronics', 'Mike'),
('Alice Corp', 700, '2023-01-04', 'North', 'Software', 'John'),
('Delta Co', 120, '2023-01-05', 'West', 'Hardware', 'Lisa'),
('Echo Systems', 450, '2023-02-01', 'South', 'Software', 'Sarah'),
('Foxtrot Ltd', 80, '2023-02-15', 'East', 'Electronics', 'Mike');

Basic CASE in SELECT Statements

Simple Categorization

-- Categorize sales by amount
SELECT 
  customer, 
  amount,
  CASE
    WHEN amount >= 500 THEN 'High'
    WHEN amount >= 200 THEN 'Medium'
    ELSE 'Low'
  END AS spending_category
FROM sales;

Multiple Condition Examples

-- Complex categorization with multiple factors
SELECT 
  customer,
  amount,
  region,
  CASE
    WHEN amount >= 500 AND region = 'North' THEN 'Premium North'
    WHEN amount >= 500 THEN 'Premium Other'
    WHEN amount >= 200 AND region IN ('North', 'South') THEN 'Standard Priority'
    WHEN amount >= 100 THEN 'Standard'
    ELSE 'Basic'
  END AS customer_tier
FROM sales;

-- Date-based categorization
SELECT 
  customer,
  sale_date,
  CASE
    WHEN EXTRACT(MONTH FROM sale_date) <= 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM sale_date) <= 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM sale_date) <= 9 THEN 'Q3'
    ELSE 'Q4'
  END AS quarter
FROM sales;

CASE in WHERE Clauses

Use CASE expressions to create dynamic filtering logic:

-- Dynamic filtering based on customer type
SELECT * FROM sales
WHERE
  CASE
    WHEN customer LIKE '%Corp%' THEN amount > 100
    WHEN customer LIKE '%LLC%' THEN amount > 50
    ELSE amount > 200
  END;

-- Region-specific filtering
SELECT customer, amount, region FROM sales
WHERE
  CASE region
    WHEN 'North' THEN amount >= 150
    WHEN 'South' THEN amount >= 250
    WHEN 'East' THEN amount >= 75
    ELSE amount >= 100
  END;

CASE in ORDER BY Clauses

Create custom sorting logic with CASE expressions:

-- Priority-based sorting
SELECT customer, amount, region FROM sales
ORDER BY
  CASE
    WHEN amount >= 500 THEN 1
    WHEN amount >= 200 THEN 2
    ELSE 3
  END,
  amount DESC;

-- Custom region ordering
SELECT customer, region, amount FROM sales
ORDER BY
  CASE region
    WHEN 'North' THEN 1
    WHEN 'South' THEN 2
    WHEN 'East' THEN 3
    WHEN 'West' THEN 4
    ELSE 5
  END,
  customer;

CASE in GROUP BY and Aggregations

Grouping with CASE

-- Group by spending categories
SELECT
  CASE
    WHEN amount >= 500 THEN 'High'
    WHEN amount >= 200 THEN 'Medium'
    ELSE 'Low'
  END AS category,
  COUNT(*) AS sale_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM sales
GROUP BY category
ORDER BY avg_amount DESC;

Conditional Aggregation

-- Conditional counting and summing
SELECT 
  region,
  COUNT(*) AS total_sales,
  COUNT(CASE WHEN amount >= 200 THEN 1 END) AS high_value_sales,
  SUM(CASE WHEN product_category = 'Software' THEN amount ELSE 0 END) AS software_revenue,
  AVG(CASE WHEN amount >= 100 THEN amount END) AS avg_significant_sale
FROM sales
GROUP BY region;

Advanced CASE Techniques

Nested CASE Statements

-- Complex nested logic
SELECT 
  customer, 
  amount, 
  region,
  CASE
    WHEN region = 'North' THEN
      CASE
        WHEN amount > 500 THEN 'North-Premium'
        WHEN amount > 200 THEN 'North-Standard'
        ELSE 'North-Basic'
      END
    WHEN region = 'South' THEN
      CASE
        WHEN amount > 400 THEN 'South-Premium'
        ELSE 'South-Standard'
      END
    ELSE 'Other-Region'
  END AS detailed_category
FROM sales;

CASE with Subqueries

-- Compare individual sales to regional averages
SELECT 
  customer,
  amount,
  region,
  CASE
    WHEN amount > (SELECT AVG(amount) FROM sales s2 WHERE s2.region = sales.region) 
      THEN 'Above Regional Average'
    WHEN amount = (SELECT AVG(amount) FROM sales s2 WHERE s2.region = sales.region)
      THEN 'At Regional Average'
    ELSE 'Below Regional Average'
  END AS performance_vs_region
FROM sales;

Simple CASE vs Searched CASE

Simple CASE (Value Matching)

-- Simple CASE for direct value matching
SELECT 
  customer,
  region,
  CASE region
    WHEN 'North' THEN 'Northern Territory'
    WHEN 'South' THEN 'Southern Territory'
    WHEN 'East' THEN 'Eastern Territory'
    WHEN 'West' THEN 'Western Territory'
    ELSE 'Unknown Territory'
  END AS territory_name
FROM sales;

Searched CASE (Condition Evaluation)

-- Searched CASE for complex conditions
SELECT 
  customer,
  amount,
  CASE
    WHEN amount BETWEEN 0 AND 100 THEN 'Tier 1'
    WHEN amount BETWEEN 101 AND 300 THEN 'Tier 2'
    WHEN amount BETWEEN 301 AND 500 THEN 'Tier 3'
    WHEN amount > 500 THEN 'Tier 4'
    ELSE 'Unclassified'
  END AS tier
FROM sales;

Real-World Applications

E-commerce Customer Segmentation

-- Customer loyalty and value segmentation
SELECT 
  customer,
  COUNT(*) AS purchase_count,
  SUM(amount) AS total_spent,
  AVG(amount) AS avg_purchase,
  CASE
    WHEN COUNT(*) >= 3 AND SUM(amount) > 1000 THEN 'VIP'
    WHEN COUNT(*) >= 3 OR SUM(amount) > 500 THEN 'Loyal'
    WHEN COUNT(*) = 2 THEN 'Regular'
    ELSE 'New'
  END AS customer_segment,
  CASE
    WHEN SUM(amount) > 1000 THEN 0.15  -- 15% discount
    WHEN SUM(amount) > 500 THEN 0.10   -- 10% discount
    WHEN COUNT(*) >= 2 THEN 0.05       -- 5% discount
    ELSE 0.00                          -- No discount
  END AS discount_rate
FROM sales
GROUP BY customer
ORDER BY total_spent DESC;

Sales Performance Analysis

-- Sales rep performance evaluation
SELECT 
  sales_rep,
  COUNT(*) AS total_sales,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_sale_amount,
  CASE
    WHEN SUM(amount) >= 1000 AND COUNT(*) >= 3 THEN 'Excellent'
    WHEN SUM(amount) >= 500 OR COUNT(*) >= 2 THEN 'Good'
    WHEN SUM(amount) >= 200 THEN 'Satisfactory'
    ELSE 'Needs Improvement'
  END AS performance_rating,
  CASE
    WHEN SUM(amount) >= 1000 THEN SUM(amount) * 0.05  -- 5% commission
    WHEN SUM(amount) >= 500 THEN SUM(amount) * 0.03   -- 3% commission
    ELSE SUM(amount) * 0.02                           -- 2% commission
  END AS commission
FROM sales
GROUP BY sales_rep
ORDER BY total_revenue DESC;

Financial Reporting

-- Monthly revenue analysis with targets
SELECT 
  EXTRACT(MONTH FROM sale_date) AS month,
  COUNT(*) AS sales_count,
  SUM(amount) AS monthly_revenue,
  CASE
    WHEN SUM(amount) >= 1500 THEN 'Target Exceeded'
    WHEN SUM(amount) >= 1000 THEN 'Target Met'
    WHEN SUM(amount) >= 750 THEN 'Close to Target'
    ELSE 'Below Target'
  END AS target_status,
  CASE
    WHEN SUM(amount) >= 1500 THEN 'Green'
    WHEN SUM(amount) >= 1000 THEN 'Yellow'
    ELSE 'Red'
  END AS status_color
FROM sales
GROUP BY EXTRACT(MONTH FROM sale_date)
ORDER BY month;

CASE with Data Transformation

Data Cleaning and Standardization

-- Standardize region names
UPDATE sales
SET region = CASE
  WHEN UPPER(region) IN ('N', 'NORTH', 'NORTHERN') THEN 'North'
  WHEN UPPER(region) IN ('S', 'SOUTH', 'SOUTHERN') THEN 'South'
  WHEN UPPER(region) IN ('E', 'EAST', 'EASTERN') THEN 'East'
  WHEN UPPER(region) IN ('W', 'WEST', 'WESTERN') THEN 'West'
  ELSE region
END;

-- Clean and categorize product categories
SELECT 
  product_category,
  CASE
    WHEN LOWER(product_category) LIKE '%software%' OR LOWER(product_category) LIKE '%app%' THEN 'Software'
    WHEN LOWER(product_category) LIKE '%hardware%' OR LOWER(product_category) LIKE '%device%' THEN 'Hardware'
    WHEN LOWER(product_category) LIKE '%electronic%' THEN 'Electronics'
    ELSE 'Other'
  END AS standardized_category
FROM sales;

Creating Derived Columns

-- Add computed columns with business logic
ALTER TABLE sales ADD COLUMN priority_level TEXT;
ALTER TABLE sales ADD COLUMN follow_up_date DATE;

UPDATE sales
SET 
  priority_level = CASE
    WHEN amount >= 500 THEN 'High'
    WHEN amount >= 200 THEN 'Medium'
    ELSE 'Low'
  END,
  follow_up_date = CASE
    WHEN amount >= 500 THEN sale_date + INTERVAL '3 days'
    WHEN amount >= 200 THEN sale_date + INTERVAL '7 days'
    ELSE sale_date + INTERVAL '14 days'
  END;

Performance Considerations

Optimizing CASE Expressions

Performance Testing

-- Test performance of different CASE approaches
EXPLAIN ANALYZE
SELECT customer, 
  CASE
    WHEN amount >= 500 THEN 'High'
    WHEN amount >= 200 THEN 'Medium'
    ELSE 'Low'
  END
FROM sales;

-- Compare with pre-computed column
EXPLAIN ANALYZE
SELECT customer, priority_level
FROM sales;

Common Patterns and Best Practices

Handling NULL Values

-- Safe NULL handling in CASE
SELECT 
  customer,
  amount,
  CASE
    WHEN amount IS NULL THEN 'No Amount'
    WHEN amount = 0 THEN 'Zero Amount'
    WHEN amount > 0 THEN 'Positive Amount'
    ELSE 'Negative Amount'
  END AS amount_status;

-- Using COALESCE with CASE
SELECT 
  customer,
  CASE
    WHEN COALESCE(amount, 0) >= 500 THEN 'High'
    WHEN COALESCE(amount, 0) >= 200 THEN 'Medium'
    ELSE 'Low'
  END AS category
FROM sales;

Error Prevention

-- Prevent division by zero
SELECT 
  region,
  total_sales,
  total_customers,
  CASE
    WHEN total_customers = 0 THEN 0
    ELSE total_sales / total_customers
  END AS sales_per_customer
FROM (
  SELECT 
    region,
    SUM(amount) AS total_sales,
    COUNT(DISTINCT customer) AS total_customers
  FROM sales
  GROUP BY region
) regional_stats;

Best Practices Summary

1. Always include ELSE: Provide a default value to handle unexpected cases
2. Order conditions logically: Put most specific or frequent conditions first
3. Use meaningful names: Create clear aliases for CASE expressions
4. Handle NULLs explicitly: Consider NULL values in your conditions
5. Keep it readable: Break complex CASE expressions into multiple lines
6. Test edge cases: Verify behavior with boundary values and NULLs
7. Consider performance: Index columns used in CASE conditions
8. Document complex logic: Add comments for business rules

Practice Exercises

Try these exercises with the sample data:

1. Create a discount system: 15% for amounts over $400, 10% for over $200, 5% for others
2. Categorize customers as "Frequent" (3+ purchases), "Regular" (2 purchases), or "New" (1 purchase)
3. Create a seasonal analysis: categorize sales by quarter and season
4. Build a commission structure for sales reps based on performance
5. Create a priority system combining amount, region, and date factors

Exercise Solutions

-- 1. Discount system
SELECT customer, amount,
  CASE
    WHEN amount > 400 THEN amount * 0.15
    WHEN amount > 200 THEN amount * 0.10
    ELSE amount * 0.05
  END AS discount
FROM sales;

-- 2. Customer frequency categorization
SELECT customer,
  COUNT(*) AS purchase_count,
  CASE
    WHEN COUNT(*) >= 3 THEN 'Frequent'
    WHEN COUNT(*) = 2 THEN 'Regular'
    ELSE 'New'
  END AS customer_type
FROM sales
GROUP BY customer;

-- 3. Seasonal analysis
SELECT 
  EXTRACT(QUARTER FROM sale_date) AS quarter,
  CASE EXTRACT(QUARTER FROM sale_date)
    WHEN 1 THEN 'Winter'
    WHEN 2 THEN 'Spring'
    WHEN 3 THEN 'Summer'
    ELSE 'Fall'
  END AS season,
  COUNT(*) AS sales_count,
  SUM(amount) AS total_revenue
FROM sales
GROUP BY quarter
ORDER BY quarter;

-- 4. Sales rep commission structure
SELECT sales_rep,
  SUM(amount) AS total_sales,
  CASE
    WHEN SUM(amount) >= 1000 THEN SUM(amount) * 0.08
    WHEN SUM(amount) >= 500 THEN SUM(amount) * 0.05
    ELSE SUM(amount) * 0.03
  END AS commission
FROM sales
GROUP BY sales_rep;

-- 5. Priority system
SELECT customer, amount, region, sale_date,
  CASE
    WHEN amount >= 500 AND region = 'North' THEN 'Critical'
    WHEN amount >= 400 OR region IN ('North', 'South') THEN 'High'
    WHEN amount >= 200 THEN 'Medium'
    ELSE 'Low'
  END AS priority
FROM sales;