sql case when else

SQL CASE WHEN ... ELSE lets you write if-else style logic directly inside a query to return different values depending on conditions.

Basic idea

Think of CASE as: “check these conditions in order; return the first matching result; if nothing matches, use the default (ELSE).”

General syntax

Inline expression form:

sql

SELECT
  CASE
    WHEN <condition1> THEN <result1>
    WHEN <condition2> THEN <result2>
    -- more WHENs ...
    ELSE <default_result>
  END AS new_column
FROM your_table;

Key points:

• CASE starts the expression, END closes it.
• Each WHEN contains a condition.
• THEN defines the value to return when that WHEN is true.
• ELSE is optional and defines a fallback for all non‑matched rows.
• The first WHEN that evaluates to true wins; later WHENs are ignored for that row.

Two styles of CASE

1. Searched CASE (most common)

You write full boolean conditions after WHEN:

sql

SELECT
  customer_name,
  age,
  CASE
    WHEN age > 22 THEN 'Age is greater than 22'
    WHEN age = 21 THEN 'Age is 21'
    WHEN age = 22 THEN 'Age is 22'
    ELSE 'Age is 22 or below'
  END AS age_description
FROM customers;

• You can use >, <, =, !=, LIKE, NOT, AND, OR, etc. inside the conditions.

2. Simple CASE

You compare a single expression to several values:

sql

SELECT
  order_id,
  status,
  CASE status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

• Good when you just map fixed values (codes → labels).

Practical examples

1. Categorizing numeric values

sql

SELECT
  product_id,
  price,
  CASE
    WHEN price < 10 THEN 'Cheap'
    WHEN price BETWEEN 10 AND 50 THEN 'Medium'
    ELSE 'Expensive'
  END AS price_category
FROM products;

• This is classic “bucketing” logic: cheap/medium/expensive based on ranges.

2. Custom sorting with CASE in ORDER BY

You can use CASE in ORDER BY to control sort order:

sql

SELECT
  name,
  priority
FROM tasks
ORDER BY
  CASE priority
    WHEN 'High'   THEN 1
    WHEN 'Medium' THEN 2
    WHEN 'Low'    THEN 3
    ELSE 4
  END;

• Text priorities are mapped to numeric ranks so High → Medium → Low order is enforced.

3. Default values with ELSE

If you omit ELSE, unmatched rows return NULL.

With ELSE:

sql

SELECT
  student_id,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM exam_results;

• ELSE 'F' is the “catch‑all” for everything below 60.

Without ELSE:

sql

CASE
  WHEN score >= 60 THEN 'Pass'
  -- no ELSE
END

• Scores below 60 yield NULL in that column.

Where you can use CASE

You can use CASE almost anywhere an expression is allowed:

• In SELECT (most common, to create computed/categorical columns).
• In ORDER BY (custom sort order).
• In GROUP BY or aggregations (e.g., counting buckets).
• In UPDATE or INSERT to set values conditionally.

Example in UPDATE:

sql

UPDATE employees
SET salary =
  CASE
    WHEN performance_rating = 'A' THEN salary * 1.10
    WHEN performance_rating = 'B' THEN salary * 1.05
    ELSE salary
  END;

• Different raises based on performance rating, with ELSE keeping salary unchanged.

Mini HTML table: quick reference

[1][3][9] [7][9][1] [3][1][7] [9][3] [3][9]

Piece	What it does	Required?
CASE	Starts the conditional expression.	Yes
WHEN	Defines a condition to test.	At least one
THEN	Result returned when its WHEN is true.	Yes, after each WHEN
ELSE	Default result for all non‑matched rows.	No (optional)
END	Closes the CASE expression.	Yes

Common tips and gotchas

• All THEN and ELSE results should be of compatible types (e.g., all strings, or all numbers).

• Conditions are checked from top to bottom; order matters because the first match wins.

• Use ELSE when you want to avoid NULL and always have a defined value.

• You can nest CASE inside another CASE, but keep it readable or break logic into smaller parts.

If you share the SQL dialect you’re using (MySQL, SQL Server, PostgreSQL, Oracle, etc.) and a sample table, I can tailor a couple of CASE WHEN ... ELSE snippets exactly to your use case.