How to Use CASE in SQLite
CASE expressions in SQLite allow you to implement conditional logic directly within your SQL queries. They evaluate conditions and return different values based on which condition matches, similar to...
Key Insights
- SQLite’s CASE expressions provide conditional logic directly in SQL queries, eliminating the need to process results in application code for many common transformations
- The searched CASE form (with WHEN conditions) is more flexible than simple CASE (matching values), handling complex logic like range checks and NULL handling
- CASE expressions combined with aggregate functions enable powerful single-query analytics that would otherwise require multiple queries or post-processing
Introduction to CASE Expressions
CASE expressions in SQLite allow you to implement conditional logic directly within your SQL queries. They evaluate conditions and return different values based on which condition matches, similar to if-else statements in programming languages.
Use CASE when you need to transform data during retrieval, categorize values, handle NULL values gracefully, or perform conditional aggregations. While you could always fetch raw data and process it in your application code, CASE expressions push this logic to the database where it’s often more efficient and keeps your data transformation logic close to your data model.
The alternative to CASE is often creating lookup tables or views, which makes sense for static mappings you’ll reuse across many queries. But for one-off transformations or dynamic logic based on multiple conditions, CASE is your best tool.
Basic CASE Syntax
SQLite supports two forms of CASE expressions: simple CASE and searched CASE.
Simple CASE compares an expression against specific values:
SELECT
order_id,
status,
CASE status
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Processing'
WHEN 3 THEN 'Shipped'
WHEN 4 THEN 'Delivered'
ELSE 'Unknown'
END as status_label
FROM orders;
This works well when you’re matching exact values, but it’s limited. The searched CASE form is more powerful because it evaluates boolean conditions:
SELECT
product_name,
price,
CASE
WHEN price < 10 THEN 'Budget'
WHEN price >= 10 AND price < 50 THEN 'Standard'
WHEN price >= 50 AND price < 200 THEN 'Premium'
WHEN price >= 200 THEN 'Luxury'
ELSE 'Uncategorized'
END as price_tier
FROM products;
The searched CASE evaluates each WHEN clause in order and returns the result for the first true condition. The ELSE clause is optional but recommended—without it, CASE returns NULL when no conditions match.
CASE in SELECT Statements
CASE expressions shine in SELECT statements where you need to transform or calculate values based on conditions.
Here’s a practical example applying different discount rates based on customer tier:
SELECT
customer_id,
customer_name,
tier,
order_total,
CASE tier
WHEN 'gold' THEN order_total * 0.85
WHEN 'silver' THEN order_total * 0.90
WHEN 'bronze' THEN order_total * 0.95
ELSE order_total
END as discounted_total,
CASE tier
WHEN 'gold' THEN '15% off'
WHEN 'silver' THEN '10% off'
WHEN 'bronze' THEN '5% off'
ELSE 'No discount'
END as discount_label
FROM customers
JOIN orders USING (customer_id);
CASE is particularly useful for handling NULL values gracefully. Rather than letting NULLs propagate through your results, provide meaningful defaults:
SELECT
user_id,
CASE
WHEN last_login IS NULL THEN 'Never logged in'
WHEN julianday('now') - julianday(last_login) < 1 THEN 'Active today'
WHEN julianday('now') - julianday(last_login) < 7 THEN 'Active this week'
WHEN julianday('now') - julianday(last_login) < 30 THEN 'Active this month'
ELSE 'Inactive'
END as activity_status,
CASE
WHEN email IS NOT NULL THEN email
WHEN phone IS NOT NULL THEN 'Contact via phone'
ELSE 'No contact info'
END as contact_method
FROM users;
CASE with Aggregate Functions
Combining CASE with aggregate functions lets you perform conditional aggregations in a single query—a powerful technique for analytics and reporting.
Count records meeting different criteria simultaneously:
SELECT
COUNT(*) as total_orders,
COUNT(CASE WHEN status = 'delivered' THEN 1 END) as delivered_count,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_count,
COUNT(CASE WHEN status IN ('pending', 'processing') THEN 1 END) as active_count,
ROUND(
100.0 * COUNT(CASE WHEN status = 'delivered' THEN 1 END) / COUNT(*),
2
) as delivery_rate
FROM orders
WHERE order_date >= date('now', '-30 days');
Note the pattern: COUNT(CASE WHEN condition THEN 1 END) counts only rows where the condition is true. We return 1 (or any non-NULL value) for matches and implicitly return NULL otherwise, which COUNT ignores.
For conditional sums, calculate different totals in one pass:
SELECT
strftime('%Y-%m', order_date) as month,
SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) as north_revenue,
SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) as south_revenue,
SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) as east_revenue,
SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) as west_revenue,
SUM(amount) as total_revenue
FROM orders
WHERE order_date >= date('now', '-12 months')
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;
This pivot-style query transforms rows into columns, giving you a revenue breakdown by region in a format that’s easy to chart or report on.
CASE in ORDER BY and WHERE Clauses
CASE expressions aren’t limited to SELECT—they’re useful in ORDER BY for custom sorting logic:
SELECT
task_id,
title,
priority,
status,
due_date
FROM tasks
ORDER BY
CASE status
WHEN 'overdue' THEN 1
WHEN 'in_progress' THEN 2
WHEN 'pending' THEN 3
WHEN 'completed' THEN 4
ELSE 5
END,
CASE priority
WHEN 'high' THEN 1
WHEN 'medium' THEN 2
WHEN 'low' THEN 3
END,
due_date ASC;
This sorts tasks by status first (overdue items at the top), then by priority within each status group, then by due date.
You can also use CASE in WHERE clauses for complex filtering logic, though this is less common:
SELECT
product_id,
product_name,
category,
stock_level,
reorder_point
FROM products
WHERE
CASE
WHEN category = 'perishable' THEN stock_level < reorder_point * 1.5
WHEN category = 'seasonal' THEN stock_level < reorder_point * 2.0
ELSE stock_level < reorder_point
END;
This query applies different reorder thresholds based on product category. While you could write this with OR conditions, CASE makes the logic clearer when thresholds vary significantly.
Common Patterns and Best Practices
Keep CASE expressions readable. When nesting becomes necessary, consider breaking complex logic into multiple CASE expressions or using a CTE:
-- Instead of deeply nested CASE
WITH categorized_customers AS (
SELECT
customer_id,
total_purchases,
CASE
WHEN total_purchases >= 10000 THEN 'vip'
WHEN total_purchases >= 5000 THEN 'gold'
WHEN total_purchases >= 1000 THEN 'silver'
ELSE 'bronze'
END as tier
FROM customer_totals
)
SELECT
customer_id,
tier,
CASE tier
WHEN 'vip' THEN 'Free shipping + 20% off'
WHEN 'gold' THEN 'Free shipping + 15% off'
WHEN 'silver' THEN '10% off'
ELSE 'Standard pricing'
END as benefits
FROM categorized_customers;
Performance-wise, CASE expressions are generally fast, but avoid using them in WHERE clauses on indexed columns when possible—they can prevent index usage. This query can’t use an index on the price column:
-- Prevents index usage
WHERE CASE WHEN category = 'sale' THEN price * 0.8 ELSE price END < 50
Rewrite it to preserve index usage:
-- Better: allows index on price
WHERE (category = 'sale' AND price < 62.5) OR (category != 'sale' AND price < 50)
Finally, know when to use lookup tables instead. If you’re repeatedly mapping the same values across many queries, create a reference table:
CREATE TABLE status_labels (
status_code INTEGER PRIMARY KEY,
label TEXT NOT NULL
);
-- Then join instead of using CASE
SELECT orders.*, status_labels.label
FROM orders
JOIN status_labels ON orders.status = status_labels.status_code;
This approach is more maintainable when mappings change frequently or are shared across multiple queries. But for one-off transformations or logic that depends on multiple columns, CASE expressions are cleaner and more efficient.
CASE expressions are a fundamental tool for data transformation in SQLite. Master them, and you’ll write more powerful, efficient queries that handle complexity at the database layer where it belongs.