CASE expressions

Why CASE?

Many reports need a label that is not stored: a grade from marks, "Low stock" from a number, "In progress" from a status. You could work it out in the app, but then every app and report has to repeat the rules. CASE puts the rule in the query, and the database applies it to every row.

Searched CASE

The searched CASE has a list of WHEN conditions. The database checks them from top to bottom, and the first true one wins; the rest are skipped. If none is true, ELSE gives the value. The whole thing ends with END and gives one value, so you can name it with AS.

SELECT name, marks,
       CASE
         WHEN marks IS NULL THEN 'Absent'
         WHEN marks >= 85   THEN 'A'
         WHEN marks >= 70   THEN 'B'
         WHEN marks >= 50   THEN 'C'
         ELSE 'D'
       END AS grade
FROM students;
-- Asha Verma  | 76   | B
-- Priya Nair  | 91   | A
-- Sneha Patil | NULL | Absent
-- Arjun Reddy | 45   | D   ... 8 rows

Order of the WHENs matters

SELECT name, marks,
       CASE WHEN marks >= 50 THEN 'Pass'
            WHEN marks >= 85 THEN 'Distinction'
            ELSE 'Fail' END AS result
FROM students WHERE id IN (3, 6);
-- Priya Nair  | 91 | Pass        <- never reaches Distinction
-- Arjun Reddy | 45 | Fail

Simple CASE

When you compare one column with several fixed values, the simple CASE is shorter: write the column once after CASE, then WHEN value THEN result.

SELECT id, status,
       CASE status WHEN 'Delivered' THEN 'Done'
                   WHEN 'Cancelled' THEN 'Closed'
                   ELSE 'In progress' END AS stage
FROM orders;
-- 101 | Delivered | Done
-- 104 | Cancelled | Closed
-- 105 | Shipped   | In progress   ... 8 rows

ELSE and NULL

If no WHEN is true and there is no ELSE, CASE gives NULL. Sometimes that is what you want (a blank "alert" column for products that are fine). Sometimes it hides a bug. And watch the other way round: in a grade CASE without a NULL check, a student with no marks fails every comparison and falls into ELSE, getting a D she never earned.

SELECT name, stock,
       CASE WHEN stock = 0  THEN 'Out of stock'
            WHEN stock < 15 THEN 'Low' END AS alert
FROM products;
-- Basmati Rice 5kg    | 40 | NULL
-- Steel Tiffin Box    | 12 | Low
-- Power Bank 10000mAh | 0  | Out of stock   ... 8 rows

CASE in ORDER BY: a custom order

Statuses sorted A to Z come out Cancelled, Delivered, Placed, Shipped, which is not how anyone thinks about an order. Give each status a number with CASE in ORDER BY, and sort by that number. The number never appears in the result.

SELECT id, status FROM orders
ORDER BY CASE status WHEN 'Placed'    THEN 1
                     WHEN 'Shipped'   THEN 2
                     WHEN 'Delivered' THEN 3
                     WHEN 'Cancelled' THEN 4 END,
         id;
-- 107 Placed, 108 Placed, 105 Shipped, 101 Delivered, ... 104 Cancelled

CASE works almost anywhere

CASE is an expression, so it can go wherever a value can: in SELECT, WHERE, ORDER BY, inside ROUND, and, in the next module, inside SUM and COUNT to build pivot tables. All results of one CASE should be the same type; PostgreSQL refuses to mix text and numbers in one CASE, while SQLite allows it.

Common mistakes

• Forgetting END. The error is usually "near FROM: syntax error". Every CASE ends with END.
• Wide bands first: WHEN marks >= 50 before WHEN marks >= 85 means nobody gets the top band. Order from narrowest to widest.
• No NULL check, so missing values land in ELSE. Add WHEN col IS NULL THEN ... as the first line.
• Writing CASE col WHEN NULL. It never matches; use CASE WHEN col IS NULL.
• Leaving gaps between bands, like < 500 and > 500, so exactly 500 gets NULL. Use >= and < consistently.