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).

Searched CASE

Copy code
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

Copy code
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:

Copy code
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.

Related terms

FILTER clause →

The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.

COALESCE →

COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.

PIVOT clause →

The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data.

NULLIF →

NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

NULL →

NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

FAQS

NULL. It's good practice to include an explicit ELSE branch unless a NULL result for unmatched rows is exactly what you want.

Simple CASE (CASE expr WHEN value THEN ...) only supports equality comparisons against a single expression; searched CASE (CASE WHEN condition THEN ...) allows arbitrary boolean conditions, including ranges and multiple columns.

Yes -- wrapping a CASE expression inside SUM(), COUNT(), or AVG() is the standard way to compute conditional aggregates, sometimes called "conditional SUM" pivoting.