case when sql

The SQL CASE statement is a conditional expression that allows you to implement if/then logic within SQL queries. It evaluates conditions and returns a specific value when the first condition is met. If none of the conditions are true, it returns the value in the optional ELSE clause, or NULL if no ELSE clause is provided. The general syntax looks like this:

sql

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE else_result
END

• The CASE statement is often used in SELECT queries to create new columns with values based on conditions.
• It can also be used in clauses like ORDER BY, WHERE, UPDATE, DELETE, and SET.
• The statement must always end with END.
• Multiple WHEN conditions can be used to check various conditions.
• ELSE is optional and catches any cases not covered by WHEN conditions.

Example usage:

sql

SELECT player_name, year,
  CASE
    WHEN year = 'SR' THEN 'yes'
    ELSE 'no'
  END AS is_a_senior
FROM players;

This example marks rows where the year is 'SR' as "yes" in the new column is_a_senior, otherwise "no" is shown. Another example with multiple conditions:

sql

SELECT CustomerName, Age,
  CASE
    WHEN Age > 22 THEN 'Older than 22'
    WHEN Age = 21 THEN 'Exactly 21'
    ELSE 'Younger than 21'
  END AS AgeGroup
FROM Customer;

The CASE statement is a powerful way to perform conditional logic inside SQL queries for data transformation and categorization.