---
title: "CASE expression"
description: "The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query."
canonical: "https://motherduck.com/glossary/case-expression/"
related:
  - title: "Expressions | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/expressions/"
  - title: "SQL Golf: 5 SQL Tricks You Should (Probably) Never Use"
    url: "https://motherduck.com/blog/its-a-sql-golf-quackmas/"
  - title: "SQL Assistant | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/"
gated_asset:
  title: "AI Analytics Eval Field Guide"
  url: "https://motherduck.com/lp/ai-analytics-eval-field-guide-full/"
---

# CASE expression

> The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.

## Overview
`CASE` is SQL's conditional expression -- the closest thing to an inline if/else inside a query. It can appear anywhere a value is expected: in `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, or inside another function call. There are two forms: "searched" `CASE` (arbitrary boolean conditions) and "simple" `CASE` (comparing one expression against several values).

<glossary-callout guide="duckdb-cheatsheet-full" />

## Searched CASE
```sql
SELECT
  order_id,
  CASE
    WHEN order_total >= 1000 THEN 'large'
    WHEN order_total >= 100  THEN 'medium'
    ELSE 'small'
  END AS order_size
FROM orders;
```
Conditions are checked top to bottom, and the result of the first `WHEN` that evaluates to `TRUE` is returned. If no condition matches and there's no `ELSE`, the expression returns `NULL`.

## Simple CASE
```sql
SELECT
  status,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM subscriptions;
```
This shorthand compares `status` against each value in turn and is equivalent to a searched `CASE` using `WHEN status = 'A' THEN ...`.

## Conditional aggregation
A very common pattern pairs `CASE` with an aggregate function to pivot values into columns without a separate `PIVOT` step:

```sql
SELECT
  customer_id,
  SUM(CASE WHEN status = 'completed' THEN order_total ELSE 0 END) AS completed_revenue,
  SUM(CASE WHEN status = 'refunded'  THEN order_total ELSE 0 END) AS refunded_revenue
FROM orders
GROUP BY customer_id;
```

## DuckDB notes
DuckDB supports both `CASE` forms per ANSI SQL. For simple "first non-null" logic, `COALESCE` is more concise than a `CASE`/`IS NULL` chain, and for pivoting many distinct values into columns, DuckDB's `PIVOT` statement is often cleaner than a long `CASE` expression.