SQL Academy · Lesson

The CASE Expression

IF/THEN logic inside a query.

The CASE Expression is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Branching Logic in SQL

Sometimes you need a query to make a decision: if this, then that, otherwise something else. SQL gives you the CASE expression for exactly this.

CASE lets you return different values based on conditions, right inside a SELECT. Think of it as SQL's version of an if/else chain.

SELECT name,
       CASE WHEN price > 100 THEN 'expensive'
            ELSE 'affordable'
       END AS price_band
FROM products;

Anatomy of a CASE

A CASE expression has a few parts:

WHEN condition THEN value — one or more branches
ELSE value — an optional fallback
END — closes the expression (required!)

SQL evaluates the WHEN branches top to bottom and returns the value of the first one that is true.

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     ELSE default_result
END

CASE Returns a Value

The key idea: a CASE expression evaluates to a single value per row. Because it produces a value, you can use it anywhere a value is allowed — in the SELECT list, in WHERE, in ORDER BY, even inside functions.

Here it labels each order with a simple status.

SELECT order_id,
       CASE WHEN shipped_at IS NOT NULL THEN 'shipped'
            WHEN paid_at IS NOT NULL THEN 'paid'
            ELSE 'pending'
       END AS status
FROM orders;

First Match Wins

Order matters. SQL checks each WHEN in sequence and stops at the first true one. Later branches are ignored even if they would also match.

Below, an order over 1000 is 'large', never 'medium', because the first matching branch wins.

SELECT order_id, total,
       CASE WHEN total >= 1000 THEN 'large'
            WHEN total >= 100  THEN 'medium'
            ELSE 'small'
       END AS size_band
FROM orders;

The ELSE Branch

The ELSE branch is the catch-all. If none of the WHEN conditions are true, CASE returns the ELSE value.

If you omit ELSE and nothing matches, CASE returns NULL. That is a common source of surprise nulls, so add an explicit ELSE when you want a defined fallback.

SELECT name,
       CASE WHEN stock = 0 THEN 'out of stock' END AS note
FROM products;
-- rows with stock > 0 get NULL in note (no ELSE)

Conditions Can Be Anything

A WHEN condition is just a boolean expression — the same kind you write in a WHERE clause. You can use comparisons, AND, OR, BETWEEN, IN, LIKE, and more.

SELECT email,
       CASE WHEN country IN ('US','CA') AND age >= 18 THEN 'eligible'
            WHEN email LIKE '%@test.com'             THEN 'test account'
            ELSE 'review'
       END AS segment
FROM users;

Result Types Must Be Compatible

Every branch of a CASE must return values of a compatible type, because the column needs one type. Mixing text and numbers will cause an error or unexpected casting.

If you need numbers in one branch and text in another, cast explicitly so the types line up.

SELECT product_id,
       CASE WHEN on_sale THEN price * 0.9
            ELSE price
       END AS effective_price   -- all branches numeric: good
FROM products;

Using CASE in WHERE

Because CASE produces a value, you can compare its result inside a WHERE clause. This is handy for dynamic filtering rules.

Often, though, plain boolean logic in WHERE is clearer — reach for CASE in WHERE only when the branching genuinely simplifies the condition.

SELECT *
FROM orders
WHERE CASE WHEN priority = 'high' THEN total > 0
           ELSE total > 500
      END;

Nesting CASE

You can place a CASE inside another CASE branch to express layered logic. This works, but deeply nested expressions get hard to read.

Keep nesting shallow; if it grows, a separate column or a lookup table is usually cleaner.

SELECT order_id,
       CASE WHEN region = 'EU' THEN
              CASE WHEN total > 100 THEN 'EU big'
                   ELSE 'EU small' END
            ELSE 'other'
       END AS bucket
FROM orders;

CASE with Aggregates

A powerful pattern: wrap a CASE inside an aggregate like SUM or COUNT. This conditionally counts or sums rows in a single pass — sometimes called conditional aggregation or a pivot.

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;

CASE Returning NULL on Purpose

Returning NULL from a branch is sometimes exactly what you want — for example to blank out a value you do not want to display, or to feed a later aggregate that ignores nulls.

Here, only large orders keep their total; others become NULL so AVG would skip them.

SELECT order_id,
       CASE WHEN total >= 1000 THEN total
            ELSE NULL
       END AS large_order_total
FROM orders;

Quick Check

Consider this expression for a row where total = 1500:

CASE WHEN total >= 100 THEN 'medium' WHEN total >= 1000 THEN 'large' ELSE 'small' END

What value does it return?

Recap: The CASE Expression

You learned the core of conditional logic in SQL:

CASE WHEN ... THEN ... ELSE ... END returns one value per row
Branches are checked top to bottom; first match wins
Omitting ELSE yields NULL when nothing matches
All branches must return compatible types
CASE works in SELECT, WHERE, and inside aggregates

Next, we'll compare the two syntactic forms of CASE.

SELECT name,
       CASE WHEN price > 100 THEN 'expensive'
            WHEN price > 20  THEN 'mid'
            ELSE 'cheap'
       END AS tier
FROM products;

Frequently asked questions

Is the “The CASE Expression” lesson free?

Yes — the full text of “The CASE Expression” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “The CASE Expression”?

IF/THEN logic inside a query. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “The CASE Expression” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy