SQL CASE WHEN: Conditional Logic in Queries (Complete Guide)

Overview

CASE WHEN is SQL's if-then-else. It works everywhere: SELECT, WHERE, ORDER BY, GROUP BY, UPDATE, and inside aggregate functions.

All Databases

-- Simple CASE:
SELECT name,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    WHEN 'P' THEN 'Pending'
    ELSE 'Unknown'
  END AS status_label
FROM users;

-- Searched CASE (with conditions):
SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 50000 THEN 'Mid'
    WHEN salary >= 30000 THEN 'Junior'
    ELSE 'Intern'
  END AS level
FROM employees;

-- CASE in ORDER BY:
SELECT * FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    ELSE 4
  END;

-- CASE in aggregate (conditional counting):
SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive,
  SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions;

-- CASE in UPDATE:
UPDATE products
SET price = CASE
  WHEN category = 'Electronics' THEN price * 1.10
  WHEN category = 'Clothing' THEN price * 1.05
  ELSE price
END;

-- CASE in WHERE:
SELECT * FROM orders
WHERE CASE
  WHEN @show_all = 1 THEN 1
  ELSE CASE WHEN status = 'active' THEN 1 ELSE 0 END
END = 1;

Skip the Syntax Lookup

Instead of memorizing CASE WHEN syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions