5 Ways to Use CASE WHEN in SQL Queries

What Is CASE WHEN in SQL and How Do You Use It?

The CASE WHEN statement in SQL Server lets you apply conditional logic directly inside a query, returning different values based on conditions you define. It works like an IF-THEN-ELSE structure, but inside T-SQL. Knowing how to use it well can save you hours of post-processing and make your queries significantly more useful.

Most developers learn the basic syntax early and then stop there. That's a missed opportunity. CASE WHEN is one of those features that shows up constantly in production SQL Server environments, from payroll calculations to reporting pipelines to data quality checks. This guide covers five practical ways to use it, with real examples you can adapt immediately.

What Is the Basic Syntax of CASE WHEN?

There are two forms of the CASE expression in T-SQL.

The simple CASE compares a single expression against a list of values:

SELECT
  CASE column_name
    WHEN 'value1' THEN 'result1'
    WHEN 'value2' THEN 'result2'
    ELSE 'default_result'
  END AS new_column
FROM table_name;

The searched CASE evaluates independent conditions and is more flexible:

SELECT
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
  END AS new_column
FROM table_name;

SQL Server evaluates conditions in order and returns the result for the first condition that is true. If no condition matches and you haven't included an ELSE clause, the expression returns NULL. Always include an ELSE unless you specifically want NULL as your fallback.

For the full specification, refer to the Microsoft documentation on CASE expressions in Transact-SQL.

1. Categorise Values Based on Conditions

This is the most common use of CASE WHEN in day-to-day SQL work. Instead of pulling raw numbers and categorising them in Excel or a reporting tool, you do it at the query level. The result is cleaner, faster, and reproducible.

SELECT
  customer_name,
  purchase_amount,
  CASE
    WHEN purchase_amount >= 1000 THEN 'High Value'
    WHEN purchase_amount >= 500  THEN 'Medium Value'
    ELSE 'Low Value'
  END AS customer_segment
FROM sales;

SQL Server checks each WHEN condition in sequence. A customer with a purchase amount of $750 hits the second condition first and gets labelled "Medium Value", even though $750 is technically above $500. Order matters here.

This pattern is useful for customer segmentation, product tiering, risk classification, and any scenario where you need to bucket continuous values into discrete categories. Marketing and CRM teams frequently request this kind of output, and building it into the query means the logic is consistent every time rather than being manually applied in a spreadsheet.

2. Handle NULL Values Gracefully

NULL values in SQL Server behave differently from zero or an empty string. They represent the absence of data, and that causes problems in calculations, aggregations, and exports. CASE WHEN gives you a clean way to substitute a meaningful value in place of NULL.

SELECT
  employee_name,
  bonus,
  CASE
    WHEN bonus IS NULL THEN 0
    ELSE bonus
  END AS bonus_final
FROM payroll;

This is particularly relevant when preparing data for dashboards or spreadsheet exports. A NULL in a bonus column might render as blank in a report, which finance or HR can misread as a data error. Substituting 0 makes the intent explicit.

Worth noting: SQL Server also provides ISNULL() and COALESCE() for simple NULL substitution. CASE WHEN is the better choice when you need to apply different substitution logic depending on other conditions, or when you want the code to be immediately readable by someone unfamiliar with those functions.

3. Apply Conditional Logic Inside Aggregate Functions

This is where CASE WHEN becomes genuinely powerful. You can embed it inside aggregate functions like SUM(), COUNT(), and AVG() to perform conditional aggregation, calculating different totals for different subsets of data in a single query.

SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN employment_type = 'Full-Time' THEN 1 ELSE 0 END) AS fulltime_count,
  SUM(CASE WHEN employment_type = 'Part-Time' THEN 1 ELSE 0 END) AS parttime_count,
  SUM(CASE WHEN employment_type = 'Contractor' THEN 1 ELSE 0 END) AS contractor_count
FROM employees
GROUP BY department;

Without this technique, you'd need three separate queries or subqueries to get the same result. This approach runs in a single pass over the data, which matters for performance on large tables.

Conditional aggregation is also the standard technique for pivoting data without using SQL Server's PIVOT operator. It gives you more control over the output and is often easier to maintain when the number of categories changes.

4. Control Sort Order Dynamically

Standard ORDER BY clauses sort alphabetically or numerically. Sometimes you need a custom sort order that doesn't follow either of those patterns. CASE WHEN lets you assign a sort key to each value.

SELECT
  order_id,
  status,
  order_date
FROM orders
ORDER BY
  CASE status
    WHEN 'Urgent'     THEN 1
    WHEN 'Processing' THEN 2
    WHEN 'Pending'    THEN 3
    WHEN 'Completed'  THEN 4
    ELSE 5
  END,
  order_date ASC;

This query sorts orders by business priority first, then by date within each status group. Alphabetical sorting would put "Completed" before "Pending" and "Urgent" last, which is the opposite of what an operations team needs.

This technique is common in operational reporting and queue management systems where the display order reflects business rules rather than data values.

5. Use CASE WHEN in a HAVING Clause

The HAVING clause filters results after aggregation. Combining it with CASE WHEN lets you apply conditional logic to aggregated data, which is something WHERE cannot do.

SELECT
  sales_rep,
  SUM(sale_amount) AS total_sales,
  COUNT(*) AS total_orders
FROM sales
GROUP BY sales_rep
HAVING
  SUM(
    CASE
      WHEN sale_amount > 500 THEN 1
      ELSE 0
    END
  ) >= 5;

This query returns only sales reps who have closed at least five individual sales above $500. A simple WHERE clause filtering on sale_amount would exclude entire rows before aggregation, giving you a different and incorrect result. The HAVING approach lets you filter on the aggregated conditional count.

This pattern appears frequently in performance reporting, exception analysis, and compliance checks where you need to identify records that meet a threshold across multiple transactions.

Key Takeaways

• CASE WHEN evaluates conditions in order and returns the result for the first match. Sequence matters, especially when conditions overlap.
• Always include an ELSE clause unless you specifically want NULL as the fallback value.
• Embedding CASE WHEN inside SUM() or COUNT() lets you perform conditional aggregation in a single query pass, which is more efficient than multiple subqueries.
• Use CASE WHEN in ORDER BY to apply custom sort logic based on business rules rather than alphabetical or numeric order.
• CASE WHEN in a HAVING clause filters on aggregated conditional results, which WHERE cannot do.

Need Help Optimising Your SQL Server Queries?

CASE WHEN is a foundational skill, but writing efficient, maintainable T-SQL across a production environment involves a lot more than individual query techniques. DBA Services works with Australian businesses to review, optimise, and manage SQL Server environments, including query performance tuning, code standards, and ongoing database administration. If your team is dealing with slow queries, inconsistent reporting logic, or growing technical debt in your database layer, get in touch.