SQL CASE Expression: If-Then-Else Logic in Queries

CASE brings if-then-else logic into SQL. It looks at each row, checks conditions in order, and returns a value for the first one that is true. You use it to label data, group values into bands, count conditionally, and much more.

The example table#

Syntax#

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

• Conditions are checked top to bottom; the first true one wins.
• ELSE is optional. Without it, rows that match nothing get NULL.
• CASE is an expression: it produces a value, so it can go anywhere a value can (in SELECT, WHERE, ORDER BY, inside functions).

Labelling values: salary bands#

SELECT name,
       salary,
       CASE
         WHEN salary >= 75000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Low'
       END AS band
FROM employees
ORDER BY salary DESC;

name  | salary | band
------+--------+-------
Meera | 81000  | High
Ravi  | 78000  | High
Priya | 61000  | Medium
Asha  | 52000  | Medium
John  | 46000  | Low

Because conditions are checked in order, the second line does not need salary < 75000: anyone earning that much was already caught by the first line.

Simple CASE: compare one value#

When every condition compares the same column with =, the shorter simple CASE form is neater:

SELECT name,
       department,
       CASE department
         WHEN 'Engineering' THEN 'Tech'
         WHEN 'Sales'       THEN 'Business'
         ELSE 'Other'
       END AS division
FROM employees;

name  | department  | division
------+-------------+---------
Asha  | Sales       | Business
Ravi  | Engineering | Tech
Meera | Engineering | Tech
John  | Marketing   | Other
Priya | Sales       | Business

Grouping by a CASE#

Count employees in each band:

SELECT CASE
         WHEN salary >= 75000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Low'
       END AS band,
       COUNT(*) AS employees
FROM employees
GROUP BY band
ORDER BY MIN(salary) DESC;

band   | employees
-------+----------
High   | 2
Medium | 2
Low    | 1

(PostgreSQL and SQL Server need you to repeat the whole CASE in GROUP BY instead of using the alias band, or to wrap the query in a CTE.)

Conditional aggregation#

CASE inside an aggregate lets one query compute several different totals at once. This is one of the most useful patterns in reporting:

SELECT COUNT(*) AS total,
       SUM(CASE WHEN city = 'Mumbai' THEN 1 ELSE 0 END) AS in_mumbai,
       SUM(CASE WHEN salary > 60000 THEN salary ELSE 0 END) AS high_payroll
FROM employees;

total | in_mumbai | high_payroll
------+-----------+-------------
5     | 2         | 220000

Pivoting rows into columns#

The same trick turns rows into columns. The monthly_sales table has one row per region per month; this shows each region on one row with a column per month:

SELECT region,
       SUM(CASE WHEN month = '2026-01' THEN revenue END) AS jan,
       SUM(CASE WHEN month = '2026-02' THEN revenue END) AS feb,
       SUM(CASE WHEN month = '2026-03' THEN revenue END) AS mar
FROM monthly_sales
GROUP BY region;

region | jan | feb | mar
-------+-----+-----+----
North  | 120 | 150 | 110
South  | 90  | 90  | 130

Custom sort order#

Sort by a priority that is not alphabetical, such as Engineering first, then Sales, then everyone else:

SELECT name, salary
FROM employees
ORDER BY CASE department
           WHEN 'Engineering' THEN 1
           WHEN 'Sales'       THEN 2
           ELSE 3
         END,
         salary DESC;

name  | salary
------+-------
Meera | 81000
Ravi  | 78000
Priya | 61000
Asha  | 52000
John  | 46000

Common mistakes#

Practice#

SELECT name,
       stock,
       CASE
         WHEN stock = 0  THEN 'Out of stock'
         WHEN stock < 10 THEN 'Low'
         ELSE 'In stock'
       END AS status
FROM products
ORDER BY stock;

name       | stock | status
-----------+-------+-------------
Monitor    | 0     | Out of stock
Bookshelf  | 5     | Low
Desk Chair | 8     | Low
Laptop     | 12    | In stock
Headphones | 40    | In stock
Notebook   | 500   | In stock

Key takeaway#