How to Use CASE WHEN in MariaDB

Galaxy Glossary

How do I use CASE WHEN for conditional logic in MariaDB?

CASE WHEN lets you return different values based on Boolean conditions inside SELECT, UPDATE, ORDER BY, and HAVING clauses.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is the purpose of CASE WHEN?

CASE WHEN evaluates each listed condition in order and returns the first matching result. If no condition is met, ELSE supplies a fallback. This avoids multiple sub-queries or joins for simple conditional logic.

How do I write a basic CASE WHEN?

Start with CASE, add one or more WHEN condition THEN value pairs, finish with optional ELSE value, and close with END. Embed it anywhere an expression is allowed.

Where can I use CASE WHEN clauses?

Use the expression in SELECT lists, ORDER BY sorting, GROUP BY aggregations, HAVING filters, UPDATE/DELETE SET clauses, and even inside stored routines.

Can I nest or chain CASE WHEN statements?

Yes—nest them for multi-level logic or chain multiple conditions in a single CASE block. Keep readability high by limiting depth to two levels.

How does CASE WHEN compare to IF()?

IF() handles a single condition, returning one of two values. CASE WHEN supports many conditions, is ANSI-SQL compliant, and integrates cleanly with GROUP BY and ORDER BY.

Best practices for CASE WHEN

Evaluate mutually exclusive conditions first, keep expressions deterministic, alias the result column, and index columns referenced in conditions for speed.

Why How to Use CASE WHEN in MariaDB is important

How to Use CASE WHEN in MariaDB Example Usage


-- Flag customers with recent high-value orders
SELECT c.id,
       c.name,
       CASE
           WHEN SUM(o.total_amount) > 5000             THEN 'VIP'
           WHEN MAX(o.order_date)  >= CURDATE() - INTERVAL 30 DAY THEN 'Active'
           ELSE 'Standard'
       END AS customer_status
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

How to Use CASE WHEN in MariaDB Syntax


CASE [expression]
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    ELSE default_result
END [AS alias]

-- Example in an ecommerce SELECT
SELECT
    id,
    total_amount,
    CASE
        WHEN total_amount >= 1000 THEN 'High-Value'
        WHEN total_amount >= 500  THEN 'Medium-Value'
        ELSE 'Low-Value'
    END AS order_tier
FROM Orders;

Common Mistakes

Using overlapping conditions causes unexpected matches. Fix by ordering mutually exclusive WHEN clauses from most to least restrictive or adding extra predicates.
Forgetting ELSE returns NULL for unmatched rows. Always include ELSE to avoid nulls unless null is the intended result.

Frequently Asked Questions (FAQs)

Is CASE WHEN slower than multiple queries?

No. It usually executes faster because it eliminates additional scans, but complex expressions may still require indexes.

Can I use CASE WHEN in an UPDATE?

Yes. Combine CASE WHEN with SET to update rows differently based on conditions.

Does MariaDB support searched and simple CASE?

Both forms work. Omit the expression after CASE for searched CASE, or supply a column/expression for simple CASE.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie