What Is SQL CASE?

What Is SQL CASE?

SQL CASE is a conditional expression that evaluates rows and returns a value based on the first satisfied condition, offering if-then-else logic directly inside SQL queries.

Why Use SQL CASE?

SQL CASE lets you categorize, transform, or flag data without changing the table. By embedding conditions in the query, you avoid extra joins, subqueries, or post-processing in application code.

How Does SQL CASE Work?

CASE evaluates conditions in order. When a condition is true, the corresponding result is returned, and the evaluation stops. If no condition matches, CASE outputs the optional ELSE value or NULL.

What Is the Basic Syntax of SQL CASE?

SQL supports two syntaxes. Simple CASE compares one expression to several values. Searched CASE evaluates independent Boolean expressions.

-- simple case
CASE gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Other'
END

-- searched case
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
ELSE 'C'
END

Where Can I Place CASE in a Query?

CASE can appear in SELECT lists, ORDER BY, GROUP BY, HAVING, WHERE (in some engines), UPDATE SET, and even inside window functions, making it a versatile tool.

When Should I Use Simple CASE?

Use Simple CASE when comparing the same column to multiple constants. It produces cleaner code and can be optimized by the query planner.

When Should I Use Searched CASE?

Searched CASE is ideal for range checks, compound conditions, or cross-column logic because each WHEN can reference any expression.

How Do I Provide a Default Value?

Add an ELSE clause at the end of the CASE. It catches all rows that fail previous conditions, ensuring no NULLs leak into the result unless desired.

Can I Nest CASE Expressions?

Yes, you can nest CASE inside another CASE to create multi-level logic. Keep each level readable and consider common subexpressions for maintainability.

How Do I Use CASE in ORDER BY?

CASE in ORDER BY lets you sort rows conditionally, such as pushing NULLs to the end or prioritizing VIP customers.

SELECT *
FROM users
ORDER BY
CASE WHEN vip = true THEN 0 ELSE 1 END,
created_at DESC;

How Do I Update Rows Conditionally With CASE?

Place CASE in an UPDATE SET clause to assign different values per row. This avoids multiple UPDATE statements.

UPDATE employees
SET bonus = CASE
WHEN years_at_company >= 5 THEN salary * 0.10
WHEN years_at_company >= 2 THEN salary * 0.05
ELSE 0
END;

Can I Aggregate With CASE?

Yes. Wrap Boolean conditions in CASE to convert them to numeric flags, then feed them to SUM or AVG for conditional aggregation.

SELECT
SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_orders,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders;

Do Different Databases Handle CASE Differently?

ANSI SQL defines CASE, and most engines support identical basics. Minor differences include whether NULL comparisons need IS NULL and WHERE support.

What Are Performance Considerations?

CASE is computed per row. Ensure indexes support any columns referenced in WHEN predicates. Avoid heavy scalar functions inside CASE that hinder index use.

How Do I Debug a Complex CASE?

Isolate each WHEN predicate in a SELECT to test it. Add labels like 'rule hit 1' to trace which branch fires. Comment liberally and use indentation.

Best Practices for SQL CASE

Keep conditions mutually exclusive, order them by specificity, always include ELSE, and limit nesting to maintain readability.

Key Takeaways

SQL CASE adds if-then-else logic right in your query. Master both simple and searched forms, place it in any clause, and follow best practices to avoid confusion and performance issues.