CASE adds if/then/else logic to a SQL query: it checks a list of conditions and returns the result for the first one that’s true. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.
How to use CASE
List WHEN condition THEN result pairs between CASE and END. CASE returns the result of the first condition that’s true, or the ELSE value if none is:
CASE
WHEN condition THEN result
[WHEN condition THEN result ...]
[ELSE result]
END
SELECT
CASE
WHEN 72 >= 90 THEN 'Hot'
WHEN 72 >= 60 THEN 'Mild'
ELSE 'Cold'
END;
-- Output: Mild
When every condition checks the same value for equality, the shorter “simple” form names that value once:
SELECT
CASE 'amber'
WHEN 'green' THEN 'Go'
WHEN 'amber' THEN 'Slow down'
WHEN 'red' THEN 'Stop'
ELSE 'Unknown'
END;
-- Output: Slow down
In a real query the conditions test columns, and CASE adds a new column to the result:
-- Input: orders
-- order_id | amount
-- ---------+--------
-- 1001 | 45.00
-- 1002 | 100.00
-- 1003 | 349.99
-- 1004 | 1250.00
SELECT
order_id,
amount,
CASE
WHEN amount < 100 THEN 'Small'
WHEN amount < 500 THEN 'Medium'
ELSE 'Large'
END AS order_size
FROM orders;
-- Output:
-- order_id | amount | order_size
-- ---------+---------+-----------
-- 1001 | 45.00 | Small
-- 1002 | 100.00 | Medium
-- 1003 | 349.99 | Medium
-- 1004 | 1250.00 | Large
Rules to know
-
The first true condition wins. Conditions are checked from top to bottom, so put the narrowest first. Above,
100.00is Medium becauseamount < 100is false andamount < 500is true. In the wrong order, small amounts never reach their branch:
SELECT
CASE
WHEN 45 < 500 THEN 'Medium'
WHEN 45 < 100 THEN 'Small' -- can never win
ELSE 'Large'
END;
-- Output: Medium
- Without ELSE, no match means NULL.
SELECT CASE WHEN 72 >= 90 THEN 'Hot' END;
-- Output: NULL
-
Test for NULL with IS NULL.
NULL = NULLisn’t true, so the simple form can’t match a NULL. Use the searched form:
SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'no match' END;
-- Output: no match
SELECT CASE WHEN NULL IS NULL THEN 'matched' ELSE 'no match' END;
-- Output: matched
- Every result needs a compatible type. Mixing a number and text in one CASE fails in PostgreSQL and SQL Server, so cast the number:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | 0
SELECT name, CASE WHEN stock > 0 THEN stock ELSE 'Sold out' END AS stock
FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "Sold out"
-- SQL Server: Conversion failed when converting the varchar value 'Sold out' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT
name,
CASE WHEN stock > 0 THEN CAST(stock AS VARCHAR(10)) ELSE 'Sold out' END AS stock
FROM products;
-- Output:
-- name | stock
-- -------------+---------
-- Desk Lamp | 12
-- Office Chair | Sold out
Alternative syntax
For a single condition, you’ll also see a shorter function:
-- MySQL
SELECT IF(72 >= 60, 'Mild', 'Cold');
-- Output: Mild
-- SQL Server and SQLite
SELECT IIF(72 >= 60, 'Mild', 'Cold');
-- Output: Mild
Two other functions are shorthand for common CASE expressions. COALESCE(nickname, name) means CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END, and NULLIF(a, b) means CASE WHEN a = b THEN NULL ELSE a END. See the SQL COALESCE guide for the first.
MySQL also has a CASE statement for stored procedures, which ends with END CASE. This post covers the CASE expression you use in queries.
When should you use CASE?
CASE is an expression, so it works anywhere a value can: in the SELECT list, in ORDER BY and GROUP BY, and inside aggregate functions. It works in WHERE too, but plain AND and OR are usually clearer there. Most uses fall into one of these five.
Put numbers into ranges
Turn a number into a band: price tiers, order sizes, age groups, grades. Write the conditions from one end of the scale to the other, so the first match is the right one.
Translate codes into labels
Databases store short codes like 'A', 'ENG' or 'gold'; people want words. A simple CASE maps each code to a label, or to a value such as a discount rate, and ELSE catches any code you didn’t expect.
Count or total only some rows
Put CASE inside COUNT or SUM to count or add up only the rows that meet a condition. COUNT(CASE WHEN ... THEN 1 END) works because unmatched rows give NULL, which COUNT skips. One aggregate per condition gives you several totals side by side, which is how you build a pivot table in SQL.
Handle missing values
Check columns for NULL in order of preference, and return a label when they’re all empty. For a plain fallback, COALESCE is shorter. CASE is the tool when each kind of gap needs a different result.
Sort in a custom order
Alphabetical and numeric order don’t fit everything: clothing sizes, ticket priorities, a list with featured products first. A CASE in ORDER BY gives each value the position you want.
Real examples of CASE
Classify products by department and price
A result can itself be a CASE, so each department gets its own price bands:
-- Input: products
-- product_name | category | price
-- -----------------+-------------+--------
-- Laptop Pro 14 | Electronics | 1499.00
-- Wireless Earbuds | Electronics | 129.00
-- Leather Jacket | Clothing | 349.00
-- Cotton T-Shirt | Clothing | 19.99
-- Coffee Mug | Kitchen | 12.50
SELECT
product_name,
CASE category
WHEN 'Electronics' THEN
CASE
WHEN price > 1000 THEN 'Premium Electronics'
ELSE 'Standard Electronics'
END
WHEN 'Clothing' THEN
CASE
WHEN price > 200 THEN 'Designer Clothing'
ELSE 'Regular Clothing'
END
ELSE 'Other Products'
END AS product_class
FROM products;
-- Output:
-- product_name | product_class
-- -----------------+---------------------
-- Laptop Pro 14 | Premium Electronics
-- Wireless Earbuds | Standard Electronics
-- Leather Jacket | Designer Clothing
-- Cotton T-Shirt | Regular Clothing
-- Coffee Mug | Other Products
Past two levels, nested CASE gets hard to read. A lookup table that you join to is usually clearer.
Apply a discount by customer tier
Each tier code maps to a discount rate, and customers without a tier get none:
-- Input: customers
-- id | customer_name | tier
-- ---+---------------+-------
-- 1 | Maya Brooks | gold
-- 2 | Leo Martin | silver
-- 3 | Ava Kim | bronze
-- 4 | Noah Price | NULL
-- Input: orders
-- id | customer_id | order_total
-- ----+-------------+------------
-- 101 | 1 | 200.00
-- 102 | 2 | 150.00
-- 103 | 3 | 80.00
-- 104 | 4 | 60.00
SELECT
customer_name,
order_total,
ROUND(order_total * CASE tier
WHEN 'gold' THEN 0.15
WHEN 'silver' THEN 0.10
WHEN 'bronze' THEN 0.05
ELSE 0
END, 2) AS discount
FROM customers c
JOIN orders o ON c.id = o.customer_id
ORDER BY o.id;
-- Output:
-- customer_name | order_total | discount
-- --------------+-------------+---------
-- Maya Brooks | 200.00 | 30.00
-- Leo Martin | 150.00 | 15.00
-- Ava Kim | 80.00 | 4.00
-- Noah Price | 60.00 | 0.00
Pivot monthly sales into columns
One SUM(CASE ...) per month turns rows of sales into a column for each month:
-- Input: sales
-- product_category | sale_date | amount
-- -----------------+------------+-------
-- Books | 2026-01-12 | 40.00
-- Games | 2026-01-20 | 60.00
-- Books | 2026-02-03 | 25.00
-- Books | 2026-03-15 | 30.00
-- Games | 2026-03-28 | 75.00
SELECT
product_category,
SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount ELSE 0 END) AS jan_sales,
SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount ELSE 0 END) AS feb_sales,
SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount ELSE 0 END) AS mar_sales
FROM sales
GROUP BY product_category
ORDER BY product_category;
-- Output:
-- product_category | jan_sales | feb_sales | mar_sales
-- -----------------+-----------+-----------+----------
-- Books | 40.00 | 25.00 | 30.00
-- Games | 60.00 | 0 | 75.00
In SQL Server, use MONTH(sale_date) = 1 instead of EXTRACT; in SQLite, use strftime('%m', sale_date) = '01'.
Tell missing values from blank ones
Imported data often has both NULLs and empty strings. CASE can label each so you know what to clean up:
-- Input: users
-- id | middle_name
-- ---+------------
-- 1 | Rose
-- 2 |
-- 3 | NULL
SELECT
id,
CASE
WHEN middle_name IS NULL THEN 'Missing'
WHEN middle_name = '' THEN 'Empty'
ELSE middle_name
END AS middle_name
FROM users;
-- Output:
-- id | middle_name
-- ---+------------
-- 1 | Rose
-- 2 | Empty
-- 3 | Missing
Put featured products first
Give each category a sort position, then sort by name within it:
-- Input: products
-- product_name | category
-- -------------+------------
-- Wool Scarf | Sale
-- Desk Lamp | Featured
-- Bike Helmet | Clearance
-- Smart Watch | New Arrival
-- Air Fryer | Featured
SELECT product_name, category
FROM products
ORDER BY
CASE category
WHEN 'Featured' THEN 1
WHEN 'New Arrival' THEN 2
WHEN 'Sale' THEN 3
ELSE 4
END,
product_name;
-- Output:
-- product_name | category
-- -------------+------------
-- Air Fryer | Featured
-- Desk Lamp | Featured
-- Smart Watch | New Arrival
-- Wool Scarf | Sale
-- Bike Helmet | Clearance
Further reading about CASE
On this blog:
- SQL COALESCE guide: the short way to fall back when a value is NULL
- SQL pivot table guide: CASE inside SUM and AVG to turn rows into columns
- SQL ORDER BY guide: sorting, including custom sort orders
- SQL GROUP BY guide: the grouping that conditional counts and totals build on
- SQL not equal guide: why comparisons with NULL never match
Official documentation:
Beekeeper Studio Is A Free & Open Source Database GUI
Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.
What Users Say About Beekeeper Studio
"Beekeeper Studio completely replaced my old SQL workflow. It's fast, intuitive, and makes database work enjoyable again."
"I've tried many database GUIs, but Beekeeper strikes the perfect balance between features and simplicity. It just works."