CASE Statement in SQL

The SQL CASE statement brings IF-THEN-ELSE logic into your SELECT, WHERE, ORDER BY, and HAVING clauses without writing multiple queries.

What Is a CASE statement in SQL?

CASE is a conditional expression that evaluates conditions in order and returns the first matching result; if no condition matches, it returns an optional ELSE value or NULL.

Why use a CASE statement instead of multiple queries?

CASE lets you embed branching logic inside one query, reducing round-trips, avoiding temporary tables, and making reports easier to maintain.

What is the syntax of a CASE expression?

SQL supports two forms: Simple CASE compares one expression to many values; Searched CASE evaluates independent Boolean predicates.

Simple CASE syntax

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END

Searched CASE syntax

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

How do you embed CASE in SELECT, WHERE, ORDER BY?

Place CASE anywhere an expression is allowed: derive labeled columns, filter rows, or sort results dynamically, all inside one statement.

What are real-world examples of CASE?

Example: Binning continuous values

Transform numerical ages into age groups on the fly, ideal for dashboards and rollups.SELECT
age,
CASE
WHEN age < 13 THEN 'child'
WHEN age < 20 THEN 'teen'
WHEN age < 65 THEN 'adult'
ELSE 'senior'
END AS age_group
FROM users;

Example: Conditional aggregation

Sum only the rows that meet a condition without writing separate queries.SELECT
SUM(CASE WHEN status = 'paid' THEN amount END) AS revenue,
SUM(CASE WHEN status = 'unpaid' THEN amount END) AS outstanding
FROM invoices;

How do you write a CASE statement in Galaxy?

Galaxy’s AI copilot autocompletes CASE syntax, highlights matching WHEN/END pairs, and offers refactoring suggestions when your data model changes.

What are best practices for CASE statements?

Keep WHEN predicates mutually exclusive, cast return values to the same data type, and document complex logic with inline comments.

What mistakes should you avoid with CASE?

Don’t forget the ELSE branch; mismatched data types and overlapping conditions cause NULLs and logic errors.

Quick reference cheat-sheet

Remember: CASE returns the first true WHEN, ELSE is optional, END is required, and every result must share a compatible type.