SQL CASE WHEN Generator

Write each branch as condition => result, one per line, and this generator builds a correctly formatted CASE expression for PostgreSQL, MySQL, SQL Server, Oracle or SQLite. Choose a searched CASE (WHEN condition THEN) or a simple CASE (CASE column WHEN value THEN), then pick where it goes: a SELECT column, a WHERE filter, a custom ORDER BY, an UPDATE ... SET, or a conditional aggregation pivot with COUNT(CASE ...) and SUM(CASE ...). String results are quoted for you, numbers and NULL are left as literals, and the tool points out the classic mistakes: = NULL that never matches, mixed result types, missing ELSE, and branch order. It also shows the dialect shortcut (IF, IIF, DECODE, ELT/FIELD, CHOOSE) when one exists. Everything runs in your browser.

DialectCASE typeUse in

ELSEAliasTableBase columnsResults are raw SQL (do not quote)

Branches: condition => result (one per line)

CASE expression

CASE
  WHEN salary >= 100000 THEN 'Senior'
  WHEN salary >= 60000 THEN 'Mid'
  WHEN salary IS NULL THEN 'Unknown'
  ELSE 'Junior'
END

Full statement

SELECT id, name,
       CASE
         WHEN salary >= 100000 THEN 'Senior'
         WHEN salary >= 60000 THEN 'Mid'
         WHEN salary IS NULL THEN 'Unknown'
         ELSE 'Junior'
       END AS level
FROM employees;

Dialect shortcuts and alternatives

-- PostgreSQL has no IF()/IIF() in plain SQL: CASE is the only conditional expression.

-- NULL-only fallback: COALESCE(status, 'Junior')

-- Portable everywhere: COALESCE(expr, fallback) and NULLIF(a, b) are ANSI SQL.

Notes and warnings

First matching WHEN wins; later branches are never evaluated for that row, so order branches from most specific to least specific.

PostgreSQL CASE short-circuits, but not in aggregate arguments or constant-folded expressions; do not rely on it to hide division by zero inside SUM(). Use NULLIF(divisor, 0).

Do more than sql case when generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Pick the SQL dialect, the CASE type (searched or simple) and where the expression will be used. For a simple CASE, enter the subject column that each WHEN value is compared against.
Type one branch per line as condition => result (for example salary >= 100000 => Senior). Set the ELSE default, the output alias, the table name and the base columns for the generated statement.
Copy the CASE expression or the full query. Check the notes panel for NULL handling and type warnings, and use the alternative-syntax panel if your dialect has a shorter IF/IIF/DECODE form.

Frequently asked questions

What is the difference between a simple CASE and a searched CASE in SQL?

A simple CASE names one expression up front (CASE status WHEN 'shipped' THEN ... ) and compares it with = against each WHEN value, so it can only test equality. A searched CASE has no subject; every WHEN is a full boolean condition (WHEN salary >= 100000 THEN ...), so it can use ranges, IS NULL, LIKE, AND/OR and even subqueries. Both stop at the first matching WHEN and return the ELSE value (or NULL if there is no ELSE) when nothing matches. Searched CASE is the more general form and the one this generator defaults to; simple CASE is shorter when you only map a column's values to labels.

How do I write CASE WHEN with multiple conditions?

Combine conditions inside one WHEN with AND / OR and parentheses, for example WHEN country = 'US' AND (plan = 'pro' OR plan = 'team') THEN 'priority'. To test several columns in one CASE use the searched form, because a simple CASE can only compare one subject expression. Keep the most specific branch first: if WHEN salary >= 60000 came before WHEN salary >= 100000, the second branch would never be reached, since CASE returns the first true condition. In the generator each line is one WHEN, so put compound conditions on a single line.

Why does CASE WHEN column = NULL never work?

In SQL, NULL is unknown rather than a value, so column = NULL evaluates to UNKNOWN, not TRUE, and the branch is skipped. Write WHEN column IS NULL THEN ... instead. The same rule affects a simple CASE: CASE column WHEN NULL THEN ... also never matches, so NULL rows fall through to ELSE. If you just need a default for NULL, COALESCE(column, 'default') is shorter and portable; use CASE when you need several branches. You can test the generated expression against your own tables in Chat2DB, available as a desktop download at https://chat2db.ai/download or on the web at https://app.chat2db.ai.