CASE statement in SQL Queries with Examples

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.

Simple CASE statement

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

Searched CASE statement

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;

Parameters

expression (optional): This is the expression that the CASE expression looks for. If we’re comparing this to an IF statement, this is the check done inside the IF statement (e.g. for IF x > 10, the expression would be “x > 10”
condtion_1/condition_n (mandatory): These values are a result of the expression parameter mentioned. They are the possible values that expression can evaluate to. Alternatively, they can be an expression on their own, as there are two ways to write an SQL CASE expression (as explained below). They also relate to the IF statement in the IF-THEN-ELSE structure.
result_1/result_n (mandatory): These values are the value to display if the related condition is matched. They come after the THEN keyword and relate to the THEN part of the IF-THEN-ELSE structure.
result (optional): This is the value to display if none of the conditions in the CASE expresson are true. It is the ELSE part of the IF-THEN-ELSE structure and is not required for the CASE SQL statement to work.
case_name (optional): This value indicates what the column should be referred to as when displayed on the screen or from within a subquery. It’s also called the column alias.

Notes & Restrictions

If more than one WHEN condition is true, the first one processed that matches are used.
If no WHEN matches, the value in the ELSE clause applies. If no WHEN matches and there is no ELSE clause, the result is NULL.
There must be at least one non-null result in a CASE statement. Failing that, one of the NULL results must be inside of a CAST expression.
All result values must be of the same type.
Functions that have an external action (e.g. RAND) can not be used in the expression part of a CASE statement.
Multiple CASE statements can be used within CASE in SQL. Also, SQL CASE can be used in a WHERE clause.
The maximum number of conditions in a CASE statement is 255.

Advantages of CASE statements in SQL

There are several advantages of using CASE statements in SQL:

Improved readability: CASE statements make understanding the logic behind a query easier, especially when the conditions and actions are complex.
Dynamic output: CASE statements allow you to dynamically change the output of a query based on certain conditions, making it possible to handle multiple scenarios with a single query.
Better performance: In some cases, using a CASE statement can improve the performance of a query compared to multiple nested IF statements or multiple separate queries.
Reusable code: CASE statements can be reused in multiple parts of the same query or in different questions, making it easier to maintain the code and reducing the risk of errors.
Enhanced functionality: CASE statements allow you to perform various operations, including string manipulation, mathematical calculations, and conditional statements, making them versatile tools in SQL.
Improved data analysis: CASE statements allow you to classify and group data in ways that may not be possible with basic SQL statements, making it easier to analyze and understand your data.

Here are some tips and tricks for using CASE statements in SQL:

Nesting: You can nest multiple CASE statements within a single query to handle complex logic.
ELSE clause: Always include a ELSE clause in your CASE statement to handle the cases where none of the conditions are met.
Performance: Use WHEN conditions that can be evaluated quickly, and avoid using complex expressions that can slow down the query performance.
Consistent data types: Ensure that the data types of the expressions used in the WHEN clause are consistent, as mixing data types can lead to unexpected results.
Aliasing: Consider using aliases to make your query easier to understand and to ensure that the output columns have meaningful names.
Code reuse: Store commonly used CASE statements in views or stored procedures to make it easier to reuse them in multiple parts of your application.
Documentation: Document the logic behind your CASE statements, especially if they are complex, to make it easier for others to understand and maintain your code.
Debugging: If you encounter an error in your CASE statement, consider using the debug command or other debugging tools to trace the flow of execution and identify the source of the problem.

Examples of Case Expression

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

CASE Statement with IN Clause

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

CASE Statement with Predicate

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;

CASE Statement with Functions

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

CASE Within CASE Expression

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

UPDATE Statement with nested CASE Expressions

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;