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
| Component | Purpose | Required |
|---|---|---|
CASE |
Starts the expression | Yes |
WHEN condition THEN result |
Conditional logic | At least one |
ELSE result |
Default value if no conditions match | No (returns NULL if omitted) |
END |
Closes the expression | Yes |
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;
| Type | Syntax | Use Case |
|---|---|---|
| Simple CASE | CASE column WHEN value THEN result |
Direct value matching |
| Searched CASE | CASE WHEN condition THEN result |
Complex conditions and comparisons |
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
| Optimization Technique | Description | Example |
|---|---|---|
| Order conditions by frequency | Put most common conditions first | Most frequent WHEN clause first |
| Use indexes on CASE columns | Index columns used in CASE conditions | CREATE INDEX ON sales(amount) |
| Avoid complex subqueries | Use JOINs instead of subqueries in CASE | JOIN with aggregated data |
| Consider computed columns | Pre-compute CASE results for frequently used logic | Add derived columns with triggers |
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
- Always include ELSE: Provide a default value to handle unexpected cases
- Order conditions logically: Put most specific or frequent conditions first
- Use meaningful names: Create clear aliases for CASE expressions
- Handle NULLs explicitly: Consider NULL values in your conditions
- Keep it readable: Break complex CASE expressions into multiple lines
- Test edge cases: Verify behavior with boundary values and NULLs
- Consider performance: Index columns used in CASE conditions
- Document complex logic: Add comments for business rules
Practice Exercises
Try these exercises with the sample data:
- Create a discount system: 15% for amounts over $400, 10% for over $200, 5% for others
- Categorize customers as "Frequent" (3+ purchases), "Regular" (2 purchases), or "New" (1 purchase)
- Create a seasonal analysis: categorize sales by quarter and season
- Build a commission structure for sales reps based on performance
- 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;