CASE Expression

CASE adds conditional branching to SQL expressions.

It is ideal for labels, derived status fields, and conditional aggregations.

Core Concepts

flowchart TD
  A[Input row value] --> B[CASE evaluates conditions in order]
  B --> C[First matching branch selected]
  C --> D[ELSE branch if none match]

CASE form	Use case
Searched CASE (CASE WHEN condition)	Flexible condition checks
Simple CASE (CASE column WHEN value)	Exact value mapping

Searched CASE

SELECT
  name,
  price,
  CASE
    WHEN price >= 120 THEN 'premium'
    WHEN price >= 50  THEN 'standard'
    ELSE 'budget'
  END AS price_band
FROM products_case
ORDER BY price DESC;

name	price	price_band
Pro Keyboard	150.0	premium
Keyboard	100.0	standard
Mouse	50.0	standard
Notebook	20.0	budget

Simple CASE

SELECT
  name,
  category,
  CASE category
    WHEN 'hardware' THEN 'physical'
    WHEN 'stationery' THEN 'office'
    ELSE 'other'
  END AS category_label
FROM products_case
ORDER BY name;

name	category	category_label
Keyboard	hardware	physical
Mouse	hardware	physical
Notebook	stationery	office
Pro Keyboard	hardware	physical

Code Examples

-- Setup table for CASE examples.
CREATE TABLE products_case (
  product_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      REAL NOT NULL
);

INSERT INTO products_case (name, category, price)
VALUES
  ('Mouse', 'hardware', 50.0),
  ('Keyboard', 'hardware', 100.0),
  ('Pro Keyboard', 'hardware', 150.0),
  ('Notebook', 'stationery', 20.0);

Expected output
Table created and four rows inserted.

-- Conditional aggregation with CASE.
SELECT
  SUM(CASE WHEN category = 'hardware'   THEN 1 ELSE 0 END) AS hardware_count,
  SUM(CASE WHEN category = 'stationery' THEN 1 ELSE 0 END) AS stationery_count
FROM products_case;

hardware_count	stationery_count
3	1

SQLite-Specific Nuances

SQLite Nuance

CASE is an expression in SQLite, so it can be used in SELECT, ORDER BY, GROUP BY, and HAVING contexts.

Common Pitfalls / Best Practices

Pitfall

Omitting ELSE and then being surprised by NULL output for unmatched rows.

Best Practice

Order WHEN branches from most specific to most general and include an explicit ELSE.

Quick Challenge

Create a label expensive for prices >= 100, else affordable, from products_case.

View Solution

SELECT
  name,
  price,
  CASE
    WHEN price >= 100 THEN 'expensive'
    ELSE 'affordable'
  END AS price_label
FROM products_case
ORDER BY price DESC;

name	price	price_label
Pro Keyboard	150.0	expensive
Keyboard	100.0	expensive
Mouse	50.0	affordable
Notebook	20.0	affordable