CASE

CASE adds if-then-else logic inside SQL. It derives new values from old ones - pay bands from salaries, labels from codes - without changing the stored data.

The two forms of CASE

• Simple CASE: compares one expression with values: CASE WORKDEPT WHEN 'A00' THEN ...
• Searched CASE: tests full conditions: CASE WHEN SALARY > 40000 THEN ...
• Searched CASE is more flexible - conditions can differ per WHEN.
• Both forms end with END, and can be aliased like a column.

WHEN, THEN and ELSE

• WHENs are tested in order; the first true WHEN wins.
• THEN gives the result value for that WHEN.
• ELSE is the fallback when no WHEN matches - omit it and you get NULL.
• All THEN/ELSE results should be compatible types.

Where CASE can appear

• In the SELECT list to compute derived columns.
• In ORDER BY to sort by a custom ranking.
• In WHERE to build conditional filters.
• CASE cannot create new rows or columns in the table - it only computes values in the result.

Example