📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 8 of 12

CASE is SQL’s IF: it returns different values depending on conditions. You’ll use it to label rows, bucket values and turn rows into columns.

Searched CASE

SELECT name, price,
       CASE WHEN price < 1000  THEN 'Budget'
            WHEN price < 10000 THEN 'Mid'
            ELSE 'Premium'
       END AS band
FROM products;

Conditions are checked top to bottom; the first true one wins, like a nested IF in Excel. Without ELSE, unmatched rows get NULL.

Group by the label

SELECT CASE WHEN qty <= 3 THEN 'Small' WHEN qty <= 7 THEN 'Medium' ELSE 'Large' END AS size,
       COUNT(*) AS orders
FROM orders
GROUP BY size;

SQLite and PostgreSQL allow GROUP BY the alias; in SQL Server repeat the CASE expression in GROUP BY.

Conditional counts: SUM(CASE …)

SELECT SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status <> 'Delivered' THEN 1 ELSE 0 END) AS not_delivered
FROM orders;

This is SQL’s COUNTIF. Put a value instead of 1 for SUMIF: SUM(CASE WHEN p.category='Computers' THEN o.qty*p.price ELSE 0 END).

Simple CASE

CASE status WHEN 'Delivered' THEN 'Done' WHEN 'Cancelled' THEN 'Void' ELSE 'Open' END

NULL labels

SELECT name, CASE WHEN gstin IS NULL THEN 'Unregistered' ELSE 'Registered' END AS gst_status
FROM customers;

COALESCE(gstin, 'Not given') is a shorter way to replace NULL with a value.

CASE in ORDER BY

ORDER BY CASE status WHEN 'Pending' THEN 1 WHEN 'Shipped' THEN 2 ELSE 3 END

A custom sort order, like Excel’s custom lists.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.