⚡ AdvancedintermediateCommonly used

How to use CASE in SQL

Add conditional logic to SQL queries — like an if/else.

Basic CASE WHENAll databases

SELECT
  name,
  age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

CASE evaluates conditions top to bottom and returns the first matching THEN value.

Simple CASE (match one value)All databases

SELECT
  name,
  status,
  CASE status
    WHEN 'pending'   THEN '⏳ Waiting'
    WHEN 'completed' THEN '✅ Done'
    WHEN 'failed'    THEN '❌ Failed'
    ELSE '❓ Unknown'
  END AS status_label
FROM orders;

Simpler syntax when comparing a single column to multiple values.

CASE in aggregate (conditional count)All databases

SELECT
  country,
  COUNT(*)                                    AS total,
  COUNT(CASE WHEN active = true  THEN 1 END)  AS active_count,
  COUNT(CASE WHEN active = false THEN 1 END)  AS inactive_count
FROM users
GROUP BY country;

Counts rows conditionally within a GROUP BY. A very common reporting pattern.

When to use: Pivot-style reports, conditional breakdowns.

CASE in ORDER BYAll databases

SELECT name, status
FROM orders
ORDER BY
  CASE status
    WHEN 'urgent'    THEN 1
    WHEN 'pending'   THEN 2
    WHEN 'completed' THEN 3
    ELSE 4
  END;

Custom sort order using CASE in ORDER BY.

When to use: Non-alphabetical sort orders like priority queues.

Common Mistakes

→Forgetting the ELSE clause — without it, unmatched rows return NULL
→CASE returns the FIRST matching condition — order your conditions from most to least specific

Common Mistakes

Related Topics