SQL CASE Statement: Conditional Logic in Your Queries

Published: 2026-02-11

The CASE statement is SQL’s way of adding conditional logic to your queries. It works like an if-then-else statement, allowing you to return different values based on conditions. CASE is supported in all major SQL databases including SQLite, PostgreSQL, MySQL, SQL Server, and Oracle.

Sample Data

We use two tables in all examples:

products table:

id	name	category	price	stock	rating
1	Laptop Pro	Electronics	1299.99	15	4.5
2	Wireless Mouse	Electronics	29.99	50	4.2
3	Office Chair	Furniture	249.99	8	4.7
4	Desk Lamp	Furniture	39.99	25	3.9
5	Notebook Pack	Stationery	12.99	100	4.0
6	Pen Set	Stationery	8.99	75	3.5
7	Monitor 27"	Electronics	399.99	0	4.6
8	Standing Desk	Furniture	599.99	3	4.8

orders table:

id	product_id	quantity	order_date	status
1	1	2	2026-02-01	delivered
2	2	5	2026-02-03	shipped
3	3	1	2026-02-05	processing
4	4	3	2026-02-06	delivered
5	5	10	2026-02-07	shipped
6	1	1	2026-02-08	cancelled
7	7	1	2026-02-09	pending
8	8	1	2026-02-10	processing

Simple CASE Expression

The simple CASE expression compares a column or expression to specific values. It’s concise when checking equality.

Syntax

CASE column_name
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Example: Categorize Order Status

Translate order status codes into user-friendly labels:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

INSERT INTO products (id, name, category, price, stock, rating) VALUES
  (1, &#39;Laptop Pro&#39;, &#39;Electronics&#39;, 1299.99, 15, 4.5),
  (2, &#39;Wireless Mouse&#39;, &#39;Electronics&#39;, 29.99, 50, 4.2),
  (3, &#39;Office Chair&#39;, &#39;Furniture&#39;, 249.99, 8, 4.7),
  (4, &#39;Desk Lamp&#39;, &#39;Furniture&#39;, 39.99, 25, 3.9),
  (5, &#39;Notebook Pack&#39;, &#39;Stationery&#39;, 12.99, 100, 4.0),
  (6, &#39;Pen Set&#39;, &#39;Stationery&#39;, 8.99, 75, 3.5),
  (7, &#39;Monitor 27&#34;&#39;, &#39;Electronics&#39;, 399.99, 0, 4.6),
  (8, &#39;Standing Desk&#39;, &#39;Furniture&#39;, 599.99, 3, 4.8);

INSERT INTO orders (id, product_id, quantity, order_date, status) VALUES
  (1, 1, 2, &#39;2026-02-01&#39;, &#39;delivered&#39;),
  (2, 2, 5, &#39;2026-02-03&#39;, &#39;shipped&#39;),
  (3, 3, 1, &#39;2026-02-05&#39;, &#39;processing&#39;),
  (4, 4, 3, &#39;2026-02-06&#39;, &#39;delivered&#39;),
  (5, 5, 10, &#39;2026-02-07&#39;, &#39;shipped&#39;),
  (6, 1, 1, &#39;2026-02-08&#39;, &#39;cancelled&#39;),
  (7, 7, 1, &#39;2026-02-09&#39;, &#39;pending&#39;),
  (8, 8, 1, &#39;2026-02-10&#39;, &#39;processing&#39;);

Searched CASE Expression

The searched CASE expression evaluates boolean conditions, allowing complex comparisons and ranges.

Syntax

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Example: Price Categories

Classify products by price range:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Example: Stock Availability Status

Determine inventory status with different thresholds:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Using CASE in Calculations

CASE statements can modify calculations, apply conditional formulas, or handle NULL values.

Example: Apply Category-Based Discounts

Calculate discounts based on product category:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Example: Priority Orders by Value and Status

Calculate order priority based on multiple factors:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

CASE in Aggregations

Use CASE inside aggregate functions to create pivot-like reports or conditional counts.

Example: Count Products by Availability

Count how many products are in each stock category:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Example: Revenue by Category and Status

Calculate different metrics using conditional aggregation:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

CASE in ORDER BY

Use CASE in ORDER BY to create custom sorting logic.

Example: Custom Sort Order

Sort products with custom priority (out of stock first, then by rating):

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Nested CASE Statements

CASE expressions can be nested for more complex logic.

Example: Complex Product Classification

Combine multiple criteria for detailed categorization:

SQL Query

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price DECIMAL(10,2),
  stock INTEGER,
  rating DECIMAL(3,2)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity INTEGER,
  order_date TEXT,
  status TEXT
);

Common Use Cases for CASE Statements

Data categorization — Group values into ranges or categories
Conditional formatting — Display user-friendly labels or status indicators
Dynamic calculations — Apply different formulas based on conditions
Pivot table creation — Combine with aggregations for crosstab reports
Custom sorting — Define specific sort orders for special business logic
Handling NULL values — Replace or flag missing data
Multi-criteria decisions — Implement complex business rules in SQL

CASE vs. IIF and COALESCE

CASE — Most flexible, handles multiple conditions
IIF(condition, true_value, false_value) — Simpler syntax for single condition (SQL Server, Access)
COALESCE(val1, val2, …) — Returns first non-NULL value (good for NULL handling)

SQLite supports both CASE and COALESCE, but not IIF. Use CASE for maximum compatibility.

Performance Tips

Order conditions wisely — Put most likely conditions first
Avoid redundant evaluations — CASE is evaluated sequentially; once a condition matches, it stops
Index appropriate columns — If CASE uses column comparisons, indexes can help
Consider computed columns — For frequently used CASE logic, some databases support indexed computed columns

Summary

CASE adds conditional logic to SQL queries with if-then-else behavior
Simple CASE compares a column to specific values: CASE column WHEN value THEN result
Searched CASE evaluates boolean conditions: CASE WHEN condition THEN result
Can be used in SELECT, WHERE, ORDER BY, and aggregate functions
CASE expressions can be nested for complex decision trees
Always include an ELSE clause to handle unexpected values
Supported in all major SQL databases with consistent syntax

Try modifying the examples to experiment with different conditions and see how CASE can simplify your conditional logic!