SQL CASE WHEN: Conditional Logic in Queries with Examples

Picture the parcel counter at a courier office in Rajwada. The clerk weighs each parcel and drops it into one of three trays: under 1 kg goes to "Light", up to 5 kg goes to "Standard", anything heavier goes to "Heavy". A parcel with no weight slip goes to a separate shelf until someone checks it.

The clerk checks the rules in order and stops at the first one that fits.

The courier rules are WHEN conditions. The tray names are the THEN results. The "check it later" shelf is the ELSE. And the habit of stopping at the first rule that fits is exactly how SQL evaluates a CASE expression.

SQL CASE WHEN is a conditional expression that checks a list of conditions in order and returns the result of the first condition that is true, or the ELSE value if none are.

Malwa Mart, the four-store grocery chain used throughout this series, wants every March order labelled by size, its payment modes grouped, and a one-row-per-store summary of UPI, cash and card sales. CASE handles all three. You already know window functions and GROUP BY; CASE slots into both.

What is the syntax of CASE WHEN in SQL?

CASE comes in two forms. The searched form tests any condition you like. The simple form compares one expression against a list of values.

-- Searched CASE: each WHEN has its own condition
CASE
    WHEN amount IS NULL THEN 'Not billed'
    WHEN amount < 250   THEN 'Small'
    WHEN amount < 700   THEN 'Medium'
    ELSE 'Large'
END

-- Simple CASE: one expression compared with values
CASE payment_mode
    WHEN 'UPI'  THEN 'Digital'
    WHEN 'Card' THEN 'Digital'
    WHEN 'Cash' THEN 'Cash'
    ELSE 'Unknown'
END

Every CASE starts with CASE and finishes with END. Between them you write one or more WHEN ... THEN ... pairs and an optional ELSE. The whole thing is one expression, so it can take an alias with AS.

CASE is standard SQL, so this syntax runs unchanged in SQLite, MySQL, PostgreSQL, SQL Server and Oracle.

How does SQL evaluate a CASE expression?

SQL reads the WHEN branches from top to bottom. The first condition that is true wins, its THEN value is returned, and the remaining branches are skipped. If no condition is true, the ELSE value is returned. If there is no ELSE, the result is NULL.

Order 105 for 680 rupees fails the first two tests, passes amount < 700 and stops there. The ELSE branch never sees it.

Because the first match wins, branch order matters. Each test above only describes an upper edge: by the time SQL asks amount < 700, every amount under 250 has already been caught.

The NULL test comes first for a reason. Order 111 has no amount, so amount < 250 is neither true nor false but unknown. Unknown is not true, so without the first branch the row would fall through to ELSE and be called "Large", which is wrong.

Using CASE WHEN in SELECT to label rows

The most common job for CASE is to turn a number into a category that a manager can read. Here it labels every order by size.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES
 (101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
 (104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
 (107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
 (110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")

query = """
SELECT order_id, amount,
       CASE
           WHEN amount IS NULL THEN 'Not billed'
           WHEN amount < 250   THEN 'Small'
           WHEN amount < 700   THEN 'Medium'
           ELSE 'Large'
       END AS size_band
FROM orders
ORDER BY order_id
"""
for row in con.execute(query):
    print(row)

Three orders are Small, five are Medium and two are Large. Order 111 is marked Not billed instead of being forced into a band it does not belong to.

Simple CASE for mapping values

When every test is "is this column equal to X", the simple form is shorter. Malwa Mart's finance team only cares whether money arrived digitally or as cash.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES
 (101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
 (104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
 (107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
 (110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")

query = """
SELECT CASE payment_mode
           WHEN 'UPI'  THEN 'Digital'
           WHEN 'Card' THEN 'Digital'
           WHEN 'Cash' THEN 'Cash'
           ELSE 'Not recorded'
       END AS channel,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY channel
ORDER BY revenue DESC
"""
for row in con.execute(query):
    print(row)

Grouping by the alias works in SQLite, MySQL and PostgreSQL. Some databases, such as SQL Server and older Oracle versions, do not allow an alias in GROUP BY, so there you repeat the full CASE expression.

Conditional aggregation: CASE inside SUM and COUNT

Putting CASE inside an aggregate is the trick that turns rows into columns. Each SUM looks at every row but only adds the amount when its condition is true. Otherwise it adds 0.

Each SUM(CASE ...) column acts as a filter for one payment mode. GROUP BY store_id then gives one row per store.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES
 (101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
 (104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
 (107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
 (110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")

query = """
SELECT store_id,
       SUM(CASE WHEN payment_mode = 'UPI'  THEN amount ELSE 0 END) AS upi,
       SUM(CASE WHEN payment_mode = 'Cash' THEN amount ELSE 0 END) AS cash,
       SUM(CASE WHEN payment_mode = 'Card' THEN amount ELSE 0 END) AS card,
       COUNT(CASE WHEN amount >= 700 THEN 1 END)                    AS large_orders,
       SUM(amount)                                                  AS total
FROM orders
GROUP BY store_id
ORDER BY store_id
"""
print("store | upi | cash | card | large | total")
for row in con.execute(query):
    print(row)

Vijay Nagar (store 1) took all 1,535 rupees by UPI, while Rajwada's 1,740 came entirely by card. Bhawarkuan's 340-rupee order 109 has no payment mode, so it appears in the total but in none of the three columns. That gap is a data-quality flag worth showing the owner.

The COUNT(CASE WHEN amount >= 700 THEN 1 END) column uses a different idea. With no ELSE, rows that fail the test become NULL, and COUNT ignores NULLs. The aggregate functions article covers that NULL rule in detail.

FILTER, IIF and IF: shortcuts in some databases

Only the full CASE runs everywhere, and it is what interviewers expect. The block below checks your SQLite build.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES (101,1040,'UPI'), (102,330,'Cash'), (103,275,'UPI'), (107,1560,'Card');
""")
print("SQLite", sqlite3.sqlite_version)

tests = {
    "FILTER (3.30+)": "SELECT SUM(amount) FILTER (WHERE payment_mode = 'UPI') FROM orders",
    "IIF (3.32+)":    "SELECT IIF(MAX(amount) >= 700, 'has a large order', 'all small') FROM orders",
    "CASE (always)":  "SELECT SUM(CASE WHEN payment_mode = 'UPI' THEN amount ELSE 0 END) FROM orders",
}
for label, sql in tests.items():
    try:
        print(label, "->", con.execute(sql).fetchone()[0])
    except sqlite3.OperationalError as err:
        print(label, "-> not supported by this SQLite build:", err)

CASE in ORDER BY, WHERE and UPDATE

CASE is an expression, so it can go anywhere a value can. Three places are especially handy.

1. Custom sort order. ORDER BY CASE payment_mode WHEN 'Card' THEN 1 WHEN 'UPI' THEN 2 ELSE 3 END puts card orders first, then UPI, then everything else. Alphabetical sorting cannot do that.
2. Conditional filters. WHERE amount >= CASE WHEN store_id = 3 THEN 1000 ELSE 300 END uses a stricter threshold for the busiest store.
3. Rule-based updates. One UPDATE can apply different changes to different rows, instead of running three separate statements.

-- Different price changes per category in one statement
UPDATE products
SET price = CASE category
                WHEN 'Grocery' THEN price + 10
                WHEN 'Dairy'   THEN price + 5
                ELSE price
            END;

The ELSE price line matters. Without it, every Snacks and Household product would get a NULL price, because a CASE with no matching branch and no ELSE returns NULL. If you are new to UPDATE, review INSERT, UPDATE and DELETE first and always test the CASE with a SELECT before running it.

Common mistakes with SQL CASE WHEN

• Wrong branch order. If WHEN amount < 700 comes before WHEN amount < 250, every small order is labelled Medium. Put the narrowest or most specific test first.
• Forgetting ELSE. Missing ELSE returns NULL, which in an UPDATE silently wipes values.
• Ignoring NULL inputs. Comparisons with NULL are unknown, so NULL rows fall to ELSE. Test IS NULL explicitly when the column can be empty.
• Mixed result types. THEN 'Large' in one branch and THEN 0 in another may work in SQLite, but PostgreSQL raises an error. Keep every branch the same type.
• Writing END CASE. In a query, the expression ends with plain END. END CASE belongs to the CASE statement in MySQL stored procedures, a different thing.

To get comfortable, label and pivot small tables of your own. If you want that drill with a mentor, SQL training built on retail-style datasets at Debugshala uses this kind of reporting query in class, and you can try CASE WHEN practice questions on Debugshala Test between sessions.

Next in the series, handling NULL in SQL goes deeper into the unknown values that kept appearing here.

Key takeaways

• CASE WHEN returns a value based on the first true condition; ELSE catches the rest, and a missing ELSE means NULL.
• Use the searched form for ranges and complex tests, and the simple form for mapping exact values.
• Order the branches from most specific to least specific, and test NULL first when the column can be empty.
• SUM(CASE WHEN ... THEN amount ELSE 0 END) with GROUP BY pivots rows into columns.
• CASE works in SELECT, ORDER BY, WHERE, GROUP BY and UPDATE, on every major database.