CASE Expressions: Conditional Logic in SQL

CASE lets you write if/then/else logic directly inside a query — turning raw values into meaningful categories.

Simple CASE

SELECT product_name, price,
  CASE
    WHEN price < 2000 THEN 'Budget'
    WHEN price < 15000 THEN 'Mid-range'
    ELSE 'Premium'
  END AS price_tier
FROM products;

Conditions are checked top to bottom — the first one that's true wins. Always include an ELSE or unmatched rows return NULL.

CASE comparing a single column to fixed values

SELECT id, status,
  CASE status
    WHEN 'completed' THEN 'Done'
    WHEN 'pending' THEN 'In progress'
    WHEN 'cancelled' THEN 'Not fulfilled'
  END AS status_label
FROM orders;

CASE inside aggregate functions

A very common analytics pattern — counting how many rows fall into each category, all in one query:

SELECT
  COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;

This works because COUNT() ignores NULLs — so for each row, the CASE only contributes a 1 to the category it belongs to.

CASE inside ORDER BY

-- Custom sort order that isn't alphabetical
SELECT * FROM orders
ORDER BY
  CASE status
    WHEN 'pending' THEN 1
    WHEN 'completed' THEN 2
    WHEN 'cancelled' THEN 3
  END;

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;