IF and IFS Versus CASE WHEN in SQL Compared Side by Side

Conditional logic in a workbook starts as one IF and grows into a nest six levels deep that nobody dares touch. SQL's CASE expression does the same job, with a flatter shape, stricter types and different answers where nothing matches. This post translates the family IF, IFS, SWITCH, AND and OR into SQL and covers the cases where the two behave differently. It is a spoke of translating Excel formulas into SQL.

The data

WITH orders(order_no, amount, region, discount) AS (
  VALUES
    (1, 120.00, 'North', 0.10),
    (2,  80.00, 'North', NULL),
    (3,  45.50, 'South', 0.00),
    (4, 200.00, 'East',  0.15),
    (5,  30.00, NULL,    NULL)
)

Orders 2 and 5 have no discount, and order 5 has no region. Those blanks are where translations go wrong.

IF becomes CASE WHEN

=IF(B2>=100, "large", "small")

SELECT order_no,
       CASE WHEN amount >= 100 THEN 'large' ELSE 'small' END AS size
FROM orders
ORDER BY order_no;

 order_no | size
----------+-------
        1 | large
        2 | small
        3 | small
        4 | large
        5 | small

The structure is the same: a condition, a result when true, a result when false. SQL spells the false branch ELSE. Some engines also have an IF or IIF function (SQL Server's IIF(amount >= 100, 'large', 'small'), MySQL's IF(...)), which are shortcuts for a two-way CASE. Write CASE in portable code.

Nested IFs and IFS become one CASE

=IF(B2>=150, "large", IF(B2>=75, "medium", "small"))
=IFS(B2>=150, "large", B2>=75, "medium", TRUE, "small")

Both are:

SELECT order_no,
       CASE
         WHEN amount >= 150 THEN 'large'
         WHEN amount >= 75  THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders
ORDER BY order_no;

 order_no | size
----------+--------
        1 | medium
        2 | medium
        3 | small
        4 | large
        5 | small

The branches are tested in order and the first true one wins, exactly as in nested IF. Order matters: put the most restrictive condition first. A nest that goes deeper than a few levels is easier to read as a CASE than as parentheses, and Excel's limit of 64 nested levels is irrelevant in SQL. The TRUE that people write as the last condition of an IFS to mean "otherwise" is ELSE.

When the conditions test one column against bands, as in the translation guide pivot tables versus GROUP BY and CASE, consider whether the logic belongs in a table instead. A band table with thresholds can be joined, which makes the rules data that people can change without editing a query (VLOOKUP and XLOOKUP versus SQL JOIN shows the approximate-match join).

What happens when nothing matches

The three forms answer differently:

A CASE with no ELSE returns NULL when no WHEN is true. That is quiet. The spreadsheet's #N/A shows up and gets fixed; the NULL flows into a sum that skips it. When the workbook ends its IFS with an error on purpose, to flag an unexpected value, replicate that by raising it, or by adding an ELSE 'unexpected' branch and counting those rows. Add an explicit ELSE to every CASE and decide what it should be.

AND, OR and NOT

=IF(AND(B2>=100, C2="North"), "priority", "normal") is:

CASE WHEN amount >= 100 AND region = 'North' THEN 'priority' ELSE 'normal' END

OR and NOT translate directly, with the same precedence rule as in most languages: AND binds tighter than OR, so parenthesize mixed expressions. =IF(OR(C2="North", C2="East"), ...) is region IN ('North', 'East'), which reads better. For the whole set:

SELECT order_no,
       CASE WHEN amount >= 100 AND region IN ('North', 'East') THEN 'priority' ELSE 'normal' END AS flag
FROM orders
ORDER BY order_no;

 order_no |  flag
----------+----------
        1 | priority
        2 | normal
        3 | normal
        4 | priority
        5 | normal

Order 5 has a null region, and region IN (...) with a null is unknown, so the WHEN is not true and the row falls through to ELSE. That is the behavior you want here and worth knowing: an unknown condition acts like false in CASE.

NULL is not a blank cell

This is the deepest difference. In Excel, an empty cell is 0 in a numeric comparison and "" in a text comparison. =IF(D2>0, "has discount", "none") treats a blank D2 as 0, so the answer is none. In SQL, discount > 0 with a null discount is unknown, and the CASE falls through to ELSE, which gives none as well, so the result happens to agree. It stops agreeing when the logic is the negation:

=IF(D2<>0, "has discount", "none")

Excel: a blank is not unequal to 0, so none. SQL: discount <> 0 with a null is unknown, so falls to ELSE, none. Same. Now a condition that tests for blanks:

=IF(D2="", "no discount recorded", "recorded")

Excel treats both empty cells and empty strings as "". In SQL, discount IS NULL, and for text columns col IS NULL OR col = ''. Translate each blank test explicitly. And with arithmetic: =D2*B2 gives 0 for a blank discount in Excel, and discount * amount gives NULL in SQL; use COALESCE(discount, 0) * amount when the workbook means zero.

SELECT order_no,
       amount * (1 - COALESCE(discount, 0)) AS net
FROM orders
ORDER BY order_no;

 order_no |  net
----------+--------
        1 | 108.00
        2 |  80.00
        3 |  45.50
        4 | 170.00
        5 |  30.00

Without COALESCE, orders 2 and 5 would have a null net amount, and a sum over the column would skip them without a warning.

Types of the branches

Excel's IF can return text in one branch and a number in the other. A CASE expression has one result type, and all branches must be compatible:

CASE WHEN amount >= 100 THEN 'large' ELSE 0 END      -- error in PostgreSQL: text and integer

Make the types agree, by casting (0::text) or by rethinking what the column means. A column that holds a number or the word n/a is two columns in disguise; return NULL for not applicable and format it in the report. Mixed-type results are also the reason a CASE output can come out as a different type than expected: if one branch is numeric and another integer, the result is numeric.

SWITCH becomes the simple CASE

=SWITCH(C2, "North", 1, "South", 2, "East", 3, 0) compares one expression with a list of values and has a default at the end:

CASE region WHEN 'North' THEN 1 WHEN 'South' THEN 2 WHEN 'East' THEN 3 ELSE 0 END

The simple form CASE expr WHEN value THEN ... compares with equality, so it cannot test for null (WHEN NULL never matches). Use the searched form for that: WHEN region IS NULL THEN .... For long lists of mappings, a lookup table and a join are easier to maintain than a CASE with thirty branches.

Evaluation order and errors

CASE evaluates branches in order and stops at the first true one, which you can rely on for guarding against errors: CASE WHEN qty = 0 THEN NULL ELSE total / qty END avoids a division by zero. Excel's IF also evaluates only the chosen branch, and the idiom carries over. A subtlety in some engines: constant expressions in later branches can be evaluated early at planning time and raise an error even if the branch would not run; the safe form is total / NULLIF(qty, 0), which is a null for a zero divisor and works in any branch.

Verify

Compare the CASE output with the workbook's cached values for the whole column, and count rows per outcome in both. Check the blank rows by hand. A translated classification that differs from the workbook in a few rows nearly always differs on rows with blanks or on boundary values (exactly 100 or 150): test the boundaries deliberately.

When a nested IF is better left alone

The logic is a dozen branches that change often. A mapping table beats a long CASE; leave the nest in the workbook until you can model it as data.

The formula is part of an interactive model. A user changing a threshold and watching the result needs the formula.

You do not understand the conditions. Translating a nest you cannot explain copies its bugs. Write the rules in words first and have the owner confirm them.

The branches return mixed types by design. Resolve the design before the types.

Excel documents the functions in its pages on IF and IFS, and PostgreSQL describes CASE in its conditional expressions reference. To check a small extract as JSON, use the CSV to JSON converter.