SQL CASE Expression

Sample image for case statement

In SQL, the CASE statement is a powerful conditional expression that allows you to perform if-then-else logic within your queries. It evaluates conditions sequentially and returns a result when the first condition is met. If no conditions are true, it returns the value specified in the ELSE clause or NULL if the ELSE clause is omitted.

Syntax of the CASE Statement:

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

Example 1: Categorizing Order Quantities

Suppose you have an OrderDetails table and you want to categorize orders based on their quantities:

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'High'
    WHEN Quantity = 30 THEN 'Medium'
    ELSE 'Low'
END AS QuantityCategory
FROM OrderDetails;

In this example, orders with a quantity greater than 30 are labeled as ‘High’, those equal to 30 as ‘Medium’, and all others as ‘Low’. citeturn0search0

Get Teju Senior Data engineer|Azuure|Databricks | AWS’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Example 2: Assigning Grades Based on Scores

Consider a Students table with a Score column. You can assign letter grades based on score ranges using the CASE statement:

SELECT StudentName, 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 Students;

Here, students are graded ‘A’ for scores 90 and above, ‘B’ for scores between 80 and 89, and so on. citeturn0search9

Example 3: Using CASE in an UPDATE Statement

The CASE statement can also be utilized within an UPDATE statement to modify data conditionally. For instance, updating employee bonuses based on their performance ratings:

UPDATE Employees
SET Bonus = 
CASE
    WHEN PerformanceRating = 'Excellent' THEN 1000
    WHEN PerformanceRating = 'Good' THEN 500
    ELSE 100
END;

In this scenario, employees with an ‘Excellent’ rating receive a bonus of 1000, those with a ‘Good’ rating receive 500, and all others receive 100. citeturn0search7

The CASE statement enhances SQL's versatility by allowing conditional logic directly within queries, making data retrieval and manipulation more dynamic and context-sensitive.