MySQL CASE Expressions and Conditional Logic

Return values conditionally

A CASE expression selects and returns one value based on conditions. It is an expression, not a control-flow statement that runs a block of application code. CASE can appear in the SELECT list, ORDER BY, and aggregate expressions, among other valid expression contexts. It is useful when a result needs a derived category or when a report must calculate different outputs for different row states.

MySQL supports searched CASE, which tests independent Boolean conditions, and simple CASE, which compares one expression with candidate values. The first matching branch supplies the result. Order conditions from most specific to broadest; if an early condition covers a later one, that later branch can never be selected. Add ELSE when a fallback is meaningful. Without ELSE, unmatched rows produce NULL, which may propagate into further calculations.

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

The order matters: a total of 600 matches the first branch and returns large. If the thresholds were reversed, every value greater than or equal to 100 would match medium before the large condition could be reached. The branches should return compatible types. Mixing numbers and text can cause conversion behavior that is difficult for downstream code to interpret.

CASE with NULL and comparison semantics

A WHEN condition is considered matched only when it evaluates true. An UNKNOWN result, often caused by NULL, does not match. For example, CASE WHEN shipped_at = NULL will not identify missing timestamps; use IS NULL. A final ELSE can deliberately label such rows as unknown, but distinguish that label from a real business state. Review NULL and three-valued logic before building classification rules for nullable columns.

Use conditional aggregation for reports

CASE is often paired with SUM or COUNT to compute multiple conditional summaries in one grouped query. For each row, the CASE returns a value for the relevant condition and a neutral value for other rows. Choose that neutral value carefully: zero is appropriate for a sum of absent contributions, while NULL can be useful when the row should not participate in an average. Use explicit aliases for the resulting metrics.

SELECT customer_id,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_total,
       SUM(CASE WHEN status = 'refunded' THEN total ELSE 0 END) AS refunded_total
FROM orders
GROUP BY customer_id;

The query returns one row per customer and calculates separate totals. Ensure the source rows represent the correct grain; joining line items before summing order totals could multiply amounts. For large reports, inspect filtering and grouping costs with GROUP BY and HAVING and EXPLAIN.

Conditional sorting and transformations

CASE can define a custom sort order when alphabetical ordering does not match business priority. For instance, an order list can rank urgent, standard, and deferred statuses. Keep the mapping complete and use an explicit ELSE so an unexpected status does not silently receive an accidental ranking. In many systems, a lookup table or explicit priority column may be a more maintainable source of the ordering rule.

Do not move every conditional rule into SQL merely because CASE exists. Rules used by several applications need a clear source of truth. If the database owns the calculation, document the view, routine, or query contract. If it is presentation-only, the application may be clearer. Verify that any CASE in WHERE does not make an important index path less useful.

Testing CASE expressions

Test values immediately below, at, and above each threshold, along with NULL and unexpected category values. Verify that exactly the intended branch applies and that output types are stable for the driver. If CASE implements a financial or access-control decision, test it as business logic and do not assume a display label enforces authorization. Keep branch order and fallback behavior readable for future changes.

CASE makes conditional output explicit inside SQL. Use it for focused transformations and conditional aggregates, define its boundaries and NULL behavior, and avoid hiding an undocumented business rule in a long expression. Continue with MySQL functions, aggregates, and CTEs.