The SQL CASE statement is a conditional expression that allows you to implement if/then logic within SQL queries. It evaluates conditions specified in WHEN clauses and returns the corresponding result after THEN when a condition is true. The CASE statement must always end with END, and it can optionally include an ELSE clause for cases where no conditions are true.
Basic Syntax
sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result
END
- The CASE statement evaluates each condition in order.
- Once a condition is true, the corresponding result is returned and evaluation stops.
- If no conditions are true and ELSE is specified, ELSE result is returned; otherwise NULL is returned.
- It is frequently used to create new columns or modify query output based on conditions.
Example
sql
SELECT player_name,
year,
CASE WHEN year = 'SR' THEN 'yes'
ELSE 'no'
END AS is_a_senior
FROM college_football_players;
This example assigns "yes" to the is_a_senior
column if the year equals
'SR', otherwise "no".
Additional notes:
- Multiple WHEN conditions can be added.
- Conditions can include logical operators like AND and OR.
- CASE statements can be used in SELECT, ORDER BY, and other clauses.
- The ELSE is optional but recommended to handle unmatched cases.
This captures the core idea and usage of SQL CASE and WHEN statements. If there is interest, examples with multiple conditions or nested CASE statements can be provided.