SQL CASE statement allows you to perform IF-THEN-ELSE functionality within an SQL statement. CASE SQL expression can help make your data more readable and useful to the user or to the application. It allows selecting one sequence of statements to execute out of many possible sequences. There are two general flavors of the expression. In the first kind, each WHEN statement does its own independent checking. In the second kind, all of the WHEN conditions are used to do “equal” checks against a common reference expression. With both flavors, the first WHEN that matches is the one chosen.
It chooses which sequence of statements to execute based on an expression that returns one of those values. The expression is stated at the beginning, and the possible results are checked in the condition parameters.
CASE [expression] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... ... WHEN condition_n THEN result_n ELSE result END case_name
It chooses the sequence of statements associated with the first condition that evaluates to TRUE is executed. The expressions are used within each condition without mentioning it at the start of the CASE expression.
CASE WHEN expression_1 THEN result_1 WHEN expression_2 THEN result_1 ... ... WHEN expression_n THEN result_n ELSE result END CASE;
There are several advantages of using CASE statements in SQL:
IF
statements or multiple separate queries.Here are some tips and tricks for using CASE statements in SQL:
ELSE
clause in your CASE statement to handle the cases where none of the conditions are met.WHEN
conditions that can be evaluated quickly, and avoid using complex expressions that can slow down the query performance.WHEN
clause are consistent, as mixing data types can lead to unexpected results.debug
command or other debugging tools to trace the flow of execution and identify the source of the problem.SELECT LASTNAME ,SEX AS SX ,CASE SEX WHEN ’F’ THEN ’FEMALE’ WHEN ’M’ THEN ’MALE’ ELSE NULL END AS SEXX FROM EMPLOYEE WHERE LASTNAME LIKE ’J%’ ORDER BY 1; ANSWER LASTNAME SX SEXX JEFFERSON M MALE JOHNSON F FEMALE JONES M MALE
The next statement is logically the same as the above, but it uses the alternative form of the CASE notation in order to achieve the same result. In this example, the equal predicate is explicitly stated rather than implied.
SELECT ,SEX AS SX ,CASE LASTNAME WHEN SEX = ’F’ THEN ’FEMALE’ WHEN SEX = ’M’ THEN ’MALE’ ELSE NULL JOHNSON F FEMALE END AS SEXX JONES M MALE FROM EMPLOYEE WHERE LASTNAME LIKE ’J%’ ORDER BY 1; ANSWER LASTNAME SX SEXX JEFFERSON M MALE JOHNSON F FEMALE JONES M MALE
SELLECT first_name, last_name, dept_name, CASE WHEN dept_name IN ('MATH', 'ENGLISH') THEN 'Science' WHEN dept_name IN ('GEOGRAPHY', 'HISTORY') THEN 'Arts' ELSE 'Unknown' END DIVISION FROM customers ORDER BY first_name, last_name; ANSWER FIRST_NAME LAST_NAME DEPT_NAME DIVISION ANDREW JEFFERSON MATH Science BEN JOHNSON HISTORY Arts DEAN JONES PHYSICS Unknown
SELECT LASTNAME ANSWER ,SEX FROM EMPLOYEE WHERE LASTNAME LIKE ’J%’ AND CASE SEX WHEN ’F’ THEN ’’ WHEN ’M’ THEN ’’ ELSE NULL END IS NOT NULL ORDER BY 1;
SELECT LASTNAME ANSWER ,LENGTH(RTRIM(LASTNAME)) AS LEN ,SUBSTR(LASTNAME,1, CASE WHEN LENGTH(RTRIM(LASTNAME)) 6 THEN 6 ELSE LENGTH(RTRIM(LASTNAME)) END ) AS LASTNM FROM EMPLOYEE WHERE LASTNAME LIKE ’J%’ ORDER BY 1; ANSWER LASTNAME LEN LASTNM JEFFERSON 9 JEFFER JOHNSON 7 JOHNSO JONES 5 JONES
SELECT first_name, last_name, count_emp, CASE WHEN MOD(count_emp, 2) = 0 THEN 'Even' WHEN MOD(count_emp, 2) = 1 THEN 'Odd ' ELSE 'Unknown' END OddOrEven FROM EMPLOYEE ORDER BY first_name, last_name; ANSWER FIRST_NAME LAST_NAME COUNT_EMP ODDOREVEN ANDREW JEFFERSON 23 Odd BEN JOHNSON 8 Even DEAN JONES 3 Odd
SELECT first_name, last_name, dept_name, CASE WHEN dept_name IN ('MATH', 'ENGLISH') THEN (CASE WHEN first_name = 'ANDREW' THEN 'Los Angles' ELSE 'Atlanta' END) WHEN dept_name IN ('PHYSICS', 'HISTORY') THEN (CASE WHEN first_name = 'JOHN' THEN 'New York' ELSE 'Chicago' END) ELSE 'Unknown' END location FROM customers ORDER BY first_name, last_name; ANSWER FIRST_NAME LAST_NAME DEPT_NAME LOCATION ANDREW JEFFERSON MATH Los Angles BEN JOHNSON PHYSICS Chicago DEAN JONES SCIENCE Unknown
The CASE expression can also be used in an UPDATE statement to do any one of several alternative updates to a particular field in a single pass of the data:
UPDATE STAFF SET COMM = CASE DEPT WHEN 15 THEN COMM * 1.1 WHEN 20 THEN COMM * 1.2 WHEN 38 THEN CASE WHEN YEARS < 5 THEN COMM * 1.3 WHEN YEARS >= 5 THEN COMM * 1.4 ELSE NULL END ELSE COMM END WHERE COMM IS NOT AND DEPT < 50;
This example shows how to group the results of a query by a CASE expression without having to re-type the expression. Using the sample employee table, find the maximum, minimum, and average salary. Instead of finding these values for each department, assume that you want to combine some departments into the same group.
SELECT CASE_DEPT,MAX(SALARY),MIN(SALARY),AVG(SALARY) FROM (SELECT SALARY, CASE WHEN WORKDEPT = 'A00' OR WORKDEPT = 'E21' THEN 'A00_E21' WHEN WORKDEPT = 'D11' OR WORKDEPT = 'E11' THEN 'D11_E11' ELSE WORKDEPT END AS CASE_DEPT FROM DSN8A10.EMP) X GROUP BY CASE_DEPT;
A well-maintained product backlog is crucial for successful product development. It serves as a single…
Incremental value to the customer refers to the gradual delivery of small, functional parts of…
A Product Market refers to the group of potential customers who might be interested in…
The Professional Agile Leadership - Evidence-Based Management (PAL-EBM) certification offered by Scrum.org is designed for…
The Professional Agile Leadership (PAL I) certification, offered by Scrum.org, is designed to equip leaders…
Choosing the right Scrum Master Certification depends on your current experience and career goals. If…