Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 39 ยท Functions and Expressions

Conditional Expressions: CASE, IIF, COALESCE and ISNULL

Add logic to queries with CASE, IIF, CHOOSE, COALESCE, ISNULL and NULLIF, learn how NULLs flow through them and use CASE for conditional aggregation and custom sorting.

Sometimes a query must decide: label a price as cheap or expensive, replace a missing phone number with a dash, count only the shipped orders. T-SQL has a small family of expressions for this. They run per row inside a query, unlike IF ... ELSE, which controls the flow of a whole script. This lesson covers CASE (the workhorse), IIF, CHOOSE, COALESCE, ISNULL and NULLIF, then the patterns you will use most: conditional aggregation, pivoting by hand and custom sort orders.

CASE: the general conditional

CASE comes in two forms. The searched form evaluates conditions in order and returns the first match:

SQL
SELECT name, price,
       CASE
         WHEN price < 20  THEN N'Budget'
         WHEN price < 100 THEN N'Standard'
         ELSE N'Premium'
       END AS price_band
FROM dbo.products;

The simple form compares one expression with values:

SQL
CASE status WHEN N'shipped' THEN 1 WHEN N'delivered' THEN 2 ELSE 0 END

Rules to remember: the first true WHEN wins, so order from most specific to most general; without ELSE, unmatched rows return NULL; and all branches must return compatible types (the result type follows data type precedence, which can turn a string result into a failed conversion if another branch returns a number). CASE is an expression, so it works anywhere a value fits: SELECT, WHERE, ORDER BY, GROUP BY and inside aggregates.

SQL Price bands with CASE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

IIF and CHOOSE

IIF(condition, if_true, if_false) (2012+) is shorthand for a two-way CASE. CHOOSE(index, a, b, c) picks the item at a 1-based position:

SQL
SELECT IIF(stock = 0, N'Sold out', N'Available')        AS availability,
       CHOOSE(MONTH(GETDATE()), N'Jan', N'Feb', N'Mar', N'Apr', N'May', N'Jun',
                                N'Jul', N'Aug', N'Sep', N'Oct', N'Nov', N'Dec') AS month_label
FROM dbo.products;

Under the hood both are rewritten to CASE; use whichever reads better. IIF is not portable to other databases, but CASE is.

NULL handling: COALESCE, ISNULL, NULLIF

Function What it does
COALESCE(a, b, c, ...) returns the first non-NULL argument (standard SQL, any number of arguments)
ISNULL(a, b) returns b if a is NULL (T-SQL only, exactly two arguments)
NULLIF(a, b) returns NULL if a = b, otherwise a

COALESCE and ISNULL differ in subtle ways. ISNULL takes the data type of the first argument, so ISNULL(@short_var, 'a longer replacement') may truncate the replacement to the variable's length. COALESCE follows the precedence rules and picks the highest-precedence type among all arguments. COALESCE is the better habit because it is standard and portable.

NULLIF is most useful for avoiding division by zero and for turning placeholder values into real NULLs:

SQL
SELECT revenue / NULLIF(units, 0)            AS price_per_unit,
       NULLIF(LTRIM(RTRIM(phone)), N'')      AS phone_or_null
FROM dbo.sales;
SQL COALESCE and NULLIF ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Conditional aggregation

Put a CASE inside an aggregate to count or sum only some rows, several different ways in one pass. This replaces multiple queries and is the portable way to pivot:

SQL
SELECT customer_id,
       COUNT(*)                                          AS all_orders,
       SUM(CASE WHEN status = N'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = N'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM dbo.orders
GROUP BY customer_id;

The trick: CASE returns 1 for matching rows and 0 otherwise, so SUM counts the matches. COUNT(CASE WHEN ... THEN 1 END) works too, because COUNT skips the NULL produced when no branch matches.

SQL Pivot statuses into columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

CASE for custom sorting and conditional updates

ORDER BY CASE lets you impose an order that is not alphabetical:

SQL
SELECT * FROM dbo.orders
ORDER BY CASE status
           WHEN N'pending'   THEN 1
           WHEN N'shipped'   THEN 2
           WHEN N'delivered' THEN 3
           ELSE 4
         END;

In UPDATE, one statement can apply different changes by condition (SET price = CASE WHEN category_id = 1 THEN price * 1.05 ELSE price END), which is much faster than looping. This is covered again in the data-modification chapter.

Tracing a CASE through a mini diagram

flowchart TD
  A["Row: price = 45"] --> B{"price < 20?"}
  B -- yes --> C["Budget"]
  B -- no --> D{"price < 100?"}
  D -- yes --> E["Standard"]
  D -- no --> F["Premium"]

A searched CASE checks each WHEN in order and stops at the first true one.

Common mistakes

  • Overlapping WHEN conditions in the wrong order. WHEN price < 100 before WHEN price < 20 makes the second branch unreachable.
  • Forgetting ELSE. Unmatched rows silently become NULL.
  • Mixed result types. One branch returning text and another a number causes a conversion error.
  • Using = NULL in a WHEN. Write WHEN col IS NULL.
  • ISNULL truncation. The result takes the first argument's type and length.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CASE return when no WHEN matches and there is no ELSE?

    Without ELSE, unmatched rows produce NULL.
  2. 2. Which function returns the first non-NULL value from a list of any length?

    COALESCE accepts two or more arguments; ISNULL takes exactly two.
  3. 3. What does NULLIF(5, 5) return?

    NULLIF returns NULL when its two arguments are equal, otherwise the first argument.
  4. 4. How can you count only delivered orders per customer in one query alongside the total?

    A CASE inside an aggregate counts only the matching rows while the other aggregates see all rows.

Summary

  • CASE is the standard per-row conditional; first true WHEN wins, no ELSE means NULL.
  • IIF and CHOOSE are T-SQL shortcuts that compile to CASE.
  • COALESCE is portable and flexible; ISNULL is T-SQL only and keeps the first argument's type.
  • NULLIF prevents division by zero and turns placeholders into NULL.
  • CASE inside aggregates builds pivots; ORDER BY CASE makes custom sort orders.

Next: Inner and Outer Joins