Post
Ask Question

A Complete SQL Case Statement Tutorial

SQL CASE statement

 
SQL CASE statement allows developers to apply multiple conditions to perform different sets of actions in SQL based on the satisfied conditions. The SQL Case statement should be used when there are more than few conditions needs to be check. If there are 3 or less conditions, you can use the If..Else statement.
 
The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.
 
The CASE expression has two formats,
  • The simple CASE expression compares an expression to a set of simple expressions to determine the result.
  • The searched CASE expression evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.
 
CASE statement can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING. 
 
If there is no ELSE part and no conditions are true, it returns NULL.
 
Syntax 
  1. CASE  
  2.     WHEN condition1 THEN result1  
  3.     WHEN condition2 THEN result2  
  4.     WHEN conditionN THEN resultN  
  5.     ELSE result  
  6. END; 

SELECT statement with a simple CASE expression

 
A SELECT statement, a simple CASE expression allows for only an equality check -- no other comparisons are made.
 
The following example uses the CASE expression to change the display of EmployeeDetails to make them more understandable
 
Syntax
  1.  USE sample;      
  2. GO      
  3. SELECT   EmployeeName,  EmployeeAddress =      
  4.       CASE  EmployeeName      
  5.          WHEN 'R' THEN 'Rahul'      
  6.          WHEN 'M' THEN 'Mountain'      
  7.          WHEN 'T' THEN 'Touring'      
  8.          WHEN 'S' THEN 'Other sale items'      
  9.          ELSE 'Not for Match'      
  10.       END,      
  11.    EmployeeName      
  12. FROM EmployeeDetails     
  13. ORDER BY EmployeeID;      
  14. GO     
Example
 
SQL CASE Statement
 

SELECT statement with a searched CASE expression

 
The SELECT statement and the searched CASE expression allow for values to be replaced in the result set based on comparison values.
 
The following example displays the EmployeeDetails as a text comment based on the EmployeeID for an EmployeeName 
 
Syntax 
  1. SELECT EmployeeID, EmployeeName      
  2. FROM EmployeeDetails      
  3. ORDER BY CASE  EmployeeID WHEN 1 THEN EmployeeName END DESC      
  4.         ,CASE WHEN EmployeeID = 0 THEN EmployeeName  END;      
  5. GO  
Example 
 
SQL CASE Statement
 

SELECT CASE in an ORDER BY clause

 
The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. In the first example, the value in the EmployeeID, EmployeeName, EmployeeAddress, and column of the EmployeeDetails table is evaluated. Employees that have the EmployeeName set to 1 are returned in order by the EmployeeId in descending order. Employees that have the EmployeeId set to 4 are returned in order by the EmployeeID in ascending order.
 
In the second example, the result set is ordered by the column EmployeeName,EmployeeAddress when the column EmployeeAddress is equal to EmployeeName and by EmployeeDetails for all other rows
 
Syntax
  1. SELECT EmployeeID, EmployeeName, EmployeeAddress From EmployeeDetails  
  2. Where EmployeeName IS NOT NULL ORDER BY CASE EmployeeName WHEN 'Rahul' THEN EmployeeAddress  
  3.  ELSE EmployeeName End  
Example
 
SQL CASE Statement
 

CASE in an UPDATE statement

 
The following example uses the CASE expression in an UPDATE statement to determine the value that is set for the column EmployeeName for EmployeeDetails with EmployeeName set to EmployeeDetails.
 
Syntax 
  1. UPDATE EmployeeDetails           
  2. SET EmployeeName  = CASE EmployeeAddress          
  3. WHEN 'RA' THEN 'Rahul'          
  4. WHEN 'Sa' THEN 'YashBeniwal'          
  5. ELSE NULL          
  6. END     
Example
 
SQL CASE Statement
 

SELECT CASE in a SET statement

 
The SELECT CASE uses the CASE expression in a SET statement in the table-valued function EmployeeDetails. In the Sample database, all data related to people is stored in the EmployeeDetails table.
 
Syntax
  1. UPDATE EmployeeDetails           
  2. SET EmployeeName  = CASE EmployeeAddress          
  3. WHEN 'RA' THEN 'Ravi'          
  4. WHEN 'Sa' THEN 'Singh'          
  5. ELSE NULL          
  6. END  

CASE in a HAVING clause

 
The following example uses the CASE expression in a HAVING clause to restrict the rows returned by the SELECT statement.
 
The statement returns the EmployeeName in the EmployeeDetails table
 
Syntax
  1.  USE sample ;      
  2. GO      
  3. SELECT EmployeeName,EmployeeAddress, EmployeeCity     
  4. FROM EmployeeDetails AS E    
  5. JOIN  EmployeeDetail AS ph1 ON EmployeeID=EmpId     
  6. GROUP BY EmployeeName HAVING(EmployeeName='M' WHEN ph1.EmployeeName)    
  7. THEN Ph1.EmployeeName      
  8. ORDER BY EmployeeID DESC;  

Summary

 
In this tutorial, we learned the basics of the SQL SELECT CASE statement.


Similar Articles