The SQL CASE Expression
The SQL CASE expression is a powerful tool for conditional logic within a query and return a value when first condition is met (if-then-else) like true and false statement. When first condition is true than return the value. If there are no true then return the value result false and if there are no true and false condition then return NULL value. In this tutorial.
Here, we will learn the sql CASE expression with examples.
Syntax of SQL CASE expression
The CASE expression Syntax
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
WHEN conditionN THEN valueN
ELSE result
END;
Examples SQL CASE Expression
Example 1: Basic SQL CASE Expression
Let’s say, you have a table called Students with a Grade column representing the numeric grade of each student. If you want to create a new column called GradeCategory that categorizes grades into Pass or Fail based on a passing score of 60.
SQL> SELECT StudentName, Grade,
CASE
WHEN Grade >= 60 THEN ‘Pass’
ELSE ‘Fail’
END AS GradeCategory
FROM Students;
The CASE expression is used to check the condition Grade >= 60.
If the condition is true, it returns ‘Pass’; otherwise, it returns ‘Fail’ for each row in the result.
Example 2: SQL CASE Expression with Multiple Conditions
Suppose you want to categorize grades into Excellent, Good, Average, Below Average, and Fail based on certain score ranges. You can use a CASE expression with multiple conditions:
SQL> SELECT StudentName, Grade,
CASE
WHEN Grade >= 90 THEN ‘Excellent’
WHEN Grade >= 80 THEN ‘Good’
WHEN Grade >= 70 THEN ‘Average’
WHEN Grade >= 60 THEN ‘Below Average’
ELSE ‘Fail’
END AS GradeCategory
FROM Students;
The CASE expression checks multiple conditions and returns the corresponding category based on the student’s grade.
Example 3: SQL CASE Expression with Column Updates
You can also use the CASE expression to conditionally update values in an UPDATE statement. Suppose you want to update the Status column of a table called ‘Orders’ to ‘Shipped’ for orders with a ShipDate that is not null and ‘Not Shipped’ for orders with a null ShipDate.
SQL> UPDATE Orders
SET Status =
CASE
WHEN ShipDate IS NOT NULL THEN ‘Shipped’
ELSE ‘Not Shipped’
END;
The SQL CASE expression is used in the UPDATE statement to conditionally set the ‘Status’ column based on the ‘ShipDate’ value.
The CASE expression is a versatile tool in SQL that allows you to perform conditional operations within queries, making it useful for data categorization, custom calculations, and conditional updates.