What is the CASE Expression?
The CASE expression allows you to perform conditional logic
inside an SQL query. It checks conditions and returns a value when a
condition is true. CASE can be used with SELECT,
WHERE, ORDER BY, GROUP BY, and
other SQL statements.
The examples below use SQLite-compatible SQL and can be run directly in the SQL editor.
Basic CASE Syntax
The basic CASE expression evaluates one or more conditions and returns the corresponding result.
DROP TABLE IF EXISTS students;
CREATE TABLE students (
student_id INTEGER,
student_name TEXT,
score INTEGER
);
INSERT INTO students VALUES
(1, 'Aarav', 86),
(2, 'Meera', 72),
(3, 'Kabir', 94),
(4, 'Riya', 61);
SELECT
student_name,
score,
CASE
WHEN score >= 80 THEN 'Excellent'
WHEN score >= 60 THEN 'Pass'
ELSE 'Needs Improvement'
END AS performance
FROM students
ORDER BY score DESC;CASE with Multiple Conditions
You can use multiple WHEN clauses to check several
conditions.
DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id INTEGER,
product_name TEXT,
price INTEGER
);
INSERT INTO products VALUES
(1, 'Wireless Mouse', 900),
(2, 'Mechanical Keyboard', 2800),
(3, '4K Monitor', 18500),
(4, 'USB Cable', 350);
SELECT
product_name,
price,
CASE
WHEN price >= 10000 THEN 'Premium'
WHEN price >= 2000 THEN 'Mid-Range'
WHEN price >= 500 THEN 'Budget'
ELSE 'Low Cost'
END AS price_category
FROM products
ORDER BY price DESC;CASE with ELSE
The ELSE clause specifies the value returned when none of
the WHEN conditions are true.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id INTEGER,
employee_name TEXT,
department TEXT
);
INSERT INTO employees VALUES
(1, 'Anaya', 'Engineering'),
(2, 'Rohan', 'Design'),
(3, 'Mira', 'Sales'),
(4, 'Dev', 'Support');
SELECT
employee_name,
department,
CASE
WHEN department = 'Engineering' THEN 'Technical'
WHEN department = 'Design' THEN 'Creative'
WHEN department = 'Sales' THEN 'Business'
ELSE 'Other'
END AS department_type
FROM employees
ORDER BY employee_id;CASE Without ELSE
If no condition is true and there is no ELSE clause, CASE
returns NULL.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER,
customer_name TEXT,
amount INTEGER
);
INSERT INTO orders VALUES
(101, 'Neha', 1200),
(102, 'Arjun', 4500),
(103, 'Tara', 800),
(104, 'Vikram', 6200);
SELECT
order_id,
customer_name,
amount,
CASE
WHEN amount >= 5000 THEN 'Large Order'
WHEN amount >= 2000 THEN 'Medium Order'
END AS order_size
FROM orders
ORDER BY amount DESC;Simple CASE Expression
A simple CASE compares one expression against several possible values.
DROP TABLE IF EXISTS tasks;
CREATE TABLE tasks (
task_id INTEGER,
task_name TEXT,
status TEXT
);
INSERT INTO tasks VALUES
(1, 'Design Homepage', 'completed'),
(2, 'Write Documentation', 'pending'),
(3, 'Test Login Form', 'completed'),
(4, 'Update Database', 'in_progress');
SELECT
task_name,
status,
CASE status
WHEN 'completed' THEN 'Done'
WHEN 'in_progress' THEN 'Working'
WHEN 'pending' THEN 'Waiting'
ELSE 'Unknown'
END AS status_label
FROM tasks
ORDER BY task_id;CASE with Text Values
CASE can compare text values and return descriptive labels.
DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
ticket_id INTEGER,
subject TEXT,
priority TEXT
);
INSERT INTO tickets VALUES
(1, 'Password Reset', 'high'),
(2, 'Profile Update', 'low'),
(3, 'Payment Issue', 'critical'),
(4, 'Login Problem', 'medium');
SELECT
ticket_id,
subject,
priority,
CASE priority
WHEN 'critical' THEN 'Immediate Attention'
WHEN 'high' THEN 'High Attention'
WHEN 'medium' THEN 'Normal Attention'
WHEN 'low' THEN 'Low Attention'
ELSE 'Unclassified'
END AS priority_label
FROM tickets
ORDER BY ticket_id;CASE with Calculations
CASE can also perform different calculations depending on a condition.
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
sale_id INTEGER,
product TEXT,
amount INTEGER
);
INSERT INTO sales VALUES
(1, 'Laptop Bag', 3200),
(2, 'Desk Lamp', 1800),
(3, 'Office Chair', 12500),
(4, 'Notebook', 450);
SELECT
product,
amount,
CASE
WHEN amount >= 10000 THEN amount * 0.90
WHEN amount >= 3000 THEN amount * 0.95
ELSE amount
END AS final_amount
FROM sales
ORDER BY amount DESC;CASE with NULL Values
CASE can be combined with IS NULL to handle missing values.
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
customer_id INTEGER,
customer_name TEXT,
phone TEXT
);
INSERT INTO customers VALUES
(1, 'Isha', '9876543210'),
(2, 'Karan', NULL),
(3, 'Simran', '9123456780'),
(4, 'Aditya', NULL);
SELECT
customer_name,
CASE
WHEN phone IS NULL THEN 'Phone Not Available'
ELSE 'Phone Available'
END AS phone_status
FROM customers
ORDER BY customer_id;CASE with AND and OR
Multiple conditions can be combined with AND and
OR inside a CASE expression.
DROP TABLE IF EXISTS applicants;
CREATE TABLE applicants (
applicant_id INTEGER,
applicant_name TEXT,
age INTEGER,
score INTEGER
);
INSERT INTO applicants VALUES
(1, 'Aarav', 24, 88),
(2, 'Meera', 31, 76),
(3, 'Kabir', 27, 92),
(4, 'Riya', 22, 68);
SELECT
applicant_name,
age,
score,
CASE
WHEN score >= 85 AND age <= 30 THEN 'Strong Candidate'
WHEN score >= 75 OR age <= 25 THEN 'Potential Candidate'
ELSE 'Needs Review'
END AS evaluation
FROM applicants
ORDER BY score DESC;CASE with ORDER BY
CASE can be used in ORDER BY to create a custom sorting
order.
DROP TABLE IF EXISTS support_tickets;
CREATE TABLE support_tickets (
ticket_id INTEGER,
subject TEXT,
priority TEXT
);
INSERT INTO support_tickets VALUES
(101, 'Email Problem', 'low'),
(102, 'Payment Failed', 'critical'),
(103, 'Login Error', 'high'),
(104, 'Profile Update', 'medium');
SELECT
ticket_id,
subject,
priority
FROM support_tickets
ORDER BY
CASE priority
WHEN 'critical' THEN 1
WHEN 'high' THEN 2
WHEN 'medium' THEN 3
WHEN 'low' THEN 4
ELSE 5
END;CASE with Aggregate Functions
CASE can be combined with aggregate functions to perform conditional counting or summing.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
order_id INTEGER,
customer TEXT,
amount INTEGER
);
INSERT INTO orders VALUES
(1, 'Aman', 1200),
(2, 'Neha', 6500),
(3, 'Ravi', 2800),
(4, 'Tanya', 9100),
(5, 'Vikram', 1500);
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN amount >= 5000 THEN 1 ELSE 0 END) AS large_orders,
SUM(CASE WHEN amount < 5000 THEN 1 ELSE 0 END) AS regular_orders
FROM orders;CASE with GROUP BY
You can use CASE inside aggregate queries to classify rows and then calculate totals for those classifications.
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
transaction_id INTEGER,
amount INTEGER
);
INSERT INTO transactions VALUES
(1, 450),
(2, 1200),
(3, 3500),
(4, 7800),
(5, 920);
SELECT
CASE
WHEN amount >= 5000 THEN 'Large'
WHEN amount >= 1000 THEN 'Medium'
ELSE 'Small'
END AS transaction_size,
COUNT(*) AS transaction_count,
SUM(amount) AS total_amount
FROM transactions
GROUP BY
CASE
WHEN amount >= 5000 THEN 'Large'
WHEN amount >= 1000 THEN 'Medium'
ELSE 'Small'
END
ORDER BY total_amount DESC;CASE vs IF
SQL does not use the same general-purpose IF statement
syntax found in many programming languages. The CASE
expression is commonly used when conditional values are needed inside
SQL queries.
| Feature | CASE | Purpose |
|---|---|---|
| WHEN | Checks a condition | Defines when a result should be returned |
| THEN | Specifies a result | Value returned when WHEN is true |
| ELSE | Optional | Result when no condition matches |
| END | Required | Ends the CASE expression |
Common Uses of CASE
- Classify numeric values into categories.
- Convert status codes into readable labels.
- Handle NULL values.
- Create conditional calculations.
- Create custom sorting rules.
- Perform conditional aggregation.
- Build categories for reports and dashboards.
Best Practices
- Put the most specific conditions before broader conditions.
- Use an ELSE clause when an unmatched condition needs a meaningful result.
- Use clear aliases for calculated CASE expressions.
- Keep complex CASE expressions readable by formatting each WHEN condition on a separate line.
- Remember that CASE returns the result of the first matching WHEN condition.
- Use CASE with aggregate functions for conditional reporting.
The CASE expression brings conditional logic into SQL queries. It can be used to classify data, handle different conditions, calculate values, create custom sorting rules, and produce more meaningful query results.
🧪 Test Your SQL Code
Edit the SQL code on the left and click "Run Code" to see the result on the right.
Click "Run Code" to see the result here.