Conditional Expressions (CASE)

The `CASE` statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the `ELSE` clause.

If there is no `ELSE` part and no conditions are true, it returns NULL.

The `CASE` statement can be used in any clause that accepts a valid expression, like `SELECT`, `WHERE`, and `ORDER BY`.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  WHEN conditionN THEN resultN
  ELSE result
END;

Example:

Categorize employees into salary brackets.

SELECT name, salary,
  CASE
    WHEN salary >= 75000 THEN 'High'
    WHEN salary >= 65000 THEN 'Medium'
    ELSE 'Low'
  END AS salary_bracket
FROM employees;

You can use `CASE` inside an aggregate function to perform conditional counts or sums. This is a common technique for "pivoting" data, where you turn unique row values into columns.

-- Count employees in specific departments using a pivot-like structure
SELECT
  SUM(CASE WHEN department = 'Engineering' THEN 1 ELSE 0 END) as Engineering,
  SUM(CASE WHEN department = 'Marketing' THEN 1 ELSE 0 END) as Marketing,
  SUM(CASE WHEN department = 'Sales' THEN 1 ELSE 0 END) as Sales
FROM employees;