beginner 6 min read

Conditional Logic with CASE

Written with a little help from AI, reviewed and edited by a human.

Sometimes you want to show different values or messages depending on the data. For example, showing “Pass” if a score is above 60 and “Fail” if it’s below. CASE statements let you add conditional logic to your SQL queries — like an if/else statement.

Real life example

Imagine you’re grading tests. For each student’s score, you want to write a letter grade on their paper:

  • 90 or above → A
  • 80-89 → B
  • 70-79 → C
  • Below 70 → F

You look at each score and decide which letter to write based on the number. That’s exactly what a CASE statement does in SQL.

Basic CASE syntax

Let’s say you have a table of students:

idnamescore
1Alice92
2Bob85
3Charlie78
4Diana65
5Eve55

You want to add a letter grade to each row:

SELECT
  name,
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

Result:

namescoregrade
Alice92A
Bob85B
Charlie78C
Diana65F
Eve55F

Here’s how it works:

  1. For each row, SQL checks the conditions in order
  2. When it finds a WHEN that’s true, it returns that value and stops checking
  3. If none of the conditions are true, it returns the ELSE value
  4. END marks the end of the CASE statement
  5. AS grade gives the result a column name

CASE checks conditions in order

The order of your WHEN clauses matters. SQL stops at the first condition that’s true.

For example, Alice has a score of 92. SQL checks:

  • WHEN score >= 90 → True! Return ‘A’ and stop
  • It never checks the other conditions

If you wrote the conditions in the wrong order:

CASE
  WHEN score >= 70 THEN 'C'  -- ❌ This would catch everyone
  WHEN score >= 80 THEN 'B'
  WHEN score >= 90 THEN 'A'
  ELSE 'F'
END

Alice would get a ‘C’ because score >= 70 is true, and SQL stops there. Always put the most specific conditions first.

Simple CASE (equality checks)

If you’re just checking if a column equals different values, you can use the simpler form:

SELECT
  name,
  CASE status
    WHEN 'active' THEN 'User is active'
    WHEN 'inactive' THEN 'User is inactive'
    WHEN 'banned' THEN 'User is banned'
    ELSE 'Unknown status'
  END AS status_message
FROM users;

This is shorthand for:

CASE
  WHEN status = 'active' THEN 'User is active'
  WHEN status = 'inactive' THEN 'User is inactive'
  ...
END

Use this when you’re only checking equality (=). Use the full WHEN form when you need other comparisons like >, <, LIKE, etc.

Using CASE in WHERE

You can use CASE in a WHERE clause, though it’s less common:

SELECT * FROM students
WHERE
  CASE
    WHEN score >= 90 THEN 'pass'
    ELSE 'fail'
  END = 'pass';

This finds students who scored 90 or above. (Though you’d normally just use WHERE score >= 90 — this is just an example.)

Using CASE in ORDER BY

CASE is useful in ORDER BY when you want custom sorting logic.

For example, sort students by grade (A first, then B, then C, then F):

SELECT name, score
FROM students
ORDER BY
  CASE
    WHEN score >= 90 THEN 1
    WHEN score >= 80 THEN 2
    WHEN score >= 70 THEN 3
    ELSE 4
  END;

This assigns a sort number (1, 2, 3, 4) based on the score and sorts by that.

Using CASE with aggregate functions

You can combine CASE with aggregate functions to do conditional counting or summing.

For example, count how many students passed (score >= 70) and how many failed:

SELECT
  COUNT(CASE WHEN score >= 70 THEN 1 END) AS passed,
  COUNT(CASE WHEN score < 70 THEN 1 END) AS failed
FROM students;

Result:

passedfailed
32

Here’s how it works:

  • CASE WHEN score >= 70 THEN 1 END returns 1 if the condition is true, otherwise NULL
  • COUNT() counts non-NULL values, so it only counts rows where the condition was true

You can also use SUM with CASE:

SELECT
  SUM(CASE WHEN score >= 70 THEN 1 ELSE 0 END) AS passed_count
FROM students;

This does the same thing — returns 1 for passing scores, 0 for failing scores, then sums them up.

Nested CASE statements

You can put a CASE inside another CASE, though it gets messy fast:

SELECT
  name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN
      CASE
        WHEN score >= 85 THEN 'B+'
        ELSE 'B'
      END
    ELSE 'C or below'
  END AS grade
FROM students;

This works, but deeply nested CASE statements are hard to read. Try to keep them simple.

CASE with NULL

CASE can handle NULL values:

SELECT
  name,
  CASE
    WHEN email IS NULL THEN 'No email provided'
    ELSE email
  END AS email_display
FROM users;

This shows “No email provided” for users without an email.

When to use CASE

Use CASE when:

  • You need to transform or categorize data in your results (like letter grades)
  • You want custom sorting logic
  • You need conditional aggregation (count only rows that meet certain criteria)
  • You want to display user-friendly labels instead of raw data

When NOT to use CASE

Don’t use CASE if you can solve it more simply:

  • Don’t use CASE WHEN status = 'active' THEN 1 ELSE 0 END in a WHERE clause — just use WHERE status = 'active'
  • Don’t use CASE for complex business logic that should be handled in your application code

Keep your SQL focused on querying and simple transformations. Complex logic usually belongs in your application.

Summary

  • CASE adds if/else logic to SQL queries
  • Use WHEN conditions in order from most specific to least specific
  • Always end with END
  • Use ELSE to handle cases that don’t match any condition
  • CASE is great for categorizing, labeling, and conditional aggregation
Share: