SQL Language

What Case Expressions in SQL Are

The Case Statement has functionality similar to the IF-THEN-ELSE Statement. CASE Statements evaluate a list of conditions and returns one of the multiple possible result expressions. Sometimes there is a need to fetch or modify the records based on some conditions. In this case we can use CASE Expressions. Case 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 IN, WHERE, ORDER BY and HAVING.

Syntax of CASE Expression

Syntax 1 (Simple CASE Expression)

This CASE Expression is known as a simple CASE Expression. The simple CASE Expression compares an expression to a set of simple expressions to determine the result. It compares the expression with each expression in each WHEN clause. If the expression within the WHEN clause matches, it returns the expression of the THEN clause.

The following are some important points of Simple CASE Expressions:

  • Allows only an equality check.
  • Evaluates input_expression and then in the order specified, evaluates input_expression = when_expression for each WHEN clause.
  • Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
  • If no input_expression = when_expression evaluates to TRUE, the SQL Server Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. 
  1. CASE expression  
  2.   
  3.    WHEN value_1 THEN result_1  
  4.    WHEN value_2 THEN result_2  
  5.    ...  
  6.    WHEN value_n THEN result_n  
  7.   
  8.    ELSE result  
  9.   
  10. END  
Syntax 2 (Searched CASE Expression)

This CASE Expression is known as a Searched CASE Expression. The searched CASE Expression evaluates a set of Boolean expressions to determine the result. In this CASE Expression we can use boolean, logical and comparison operators.

Some important points about Searched CASE Expression: 
  • Evaluates in the order specified, Boolean_expression for each WHEN clause.
  • Returns result_expression of the first Boolean_expression that evaluates to TRUE.
  • If no Boolean_expression evaluates to TRUE, the Database Engine returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. 
  1. CASE  
  2.   
  3.    WHEN condition_1 THEN result_1  
  4.    WHEN condition_2 THEN result_2  
  5.    ...  
  6.    WHEN condition_n THEN result_n  
  7.   
  8.    ELSE result  
  9.   
  10. END  
Now we will do some exercises on CASE Expressions.

First we will create a table and insert some value in that table.
  1. CREATE TABLE tblEmployee (  
  2.     EMP_IID INT NOT NULL,  
  3.     EMP_NAME VARCHAR(MAX) NOT NULL,  
  4.     EMP_AGE INT NOT NULL,  
  5.     EMP_SALARY INT NOT NULL,  
  6.     EMP_CITY VARCHAR(MAX) NOT NULL,  
  7.     EMP_GENDER CHAR(1) NOT NULL  
  8. )  
  9.   
  10.   
  11. INSERT INTO tblEmployee   
  12. SELECT 1,'PANKAJ',20, 25000, 'ALWAR', '1' UNION ALL  
  13. SELECT 2,'RAHUL',19, 22000, 'JAIPUR', '1' UNION ALL  
  14. SELECT 3,'PRIYA',21, 28000, 'ALWAR', '0' UNION ALL  
  15. SELECT 4,'SANDEEP',20, 23000, 'JAIPUR', '1' UNION ALL  
  16. SELECT 5,'SONAL',22, 32000, 'ALWAR', '0' UNION ALL  
  17. SELECT 6,'SANJEEV',21, 50000, 'ALWAR', '1' UNION ALL  
  18. SELECT 7,'KOMAL',23, 47000, 'JAIPUR', '0'   
Now tblEmployee will look as the following: 
  1. SELECT * FROM tblEmployee e;  
tblEmployee look

Now we will see some examples.

Example 1

This example shows a general use of a CASE expression. 

    Syntax 1

    1. DECLARE @MYCOUNT INT;  
    2. SET @MYCOUNT = 3  
    3. SELECT  
    4.     CASE (@MYCOUNT)  
    5.         WHEN 1 THEN 'ONE'  
    6.         WHEN 2 THEN 'TWO'  
    7.         WHEN 3 THEN 'THREE'  
    8.         ELSE 'WRONG CHOICE'  
    9.     END AS [MESSAGE]  
    Output

    CASE expression

    Syntax 2
    1. DECLARE @MYCOUNT INT;  
    2. SET @MYCOUNT = 9  
    3. SELECT  
    4.     CASE  
    5.         WHEN @MYCOUNT < 5 THEN 'VALUE IS LESS THAN 5'  
    6.         WHEN @MYCOUNT >= 5 AND  
    7.             @MYCOUNT < 10 THEN 'VALUE IS LESS THAN 10 BUT GREATER THAN 5'  
    8.   
    9.         ELSE 'VALUE GREATER THAN 10'  
    10.     END AS [MESSAGE]  

    Output

    MESSAGE 
Example 2 (SELECT Statements)

    Syntax 1

    1. SELECT  
    2.     e.EMP_IID,  
    3.     e.EMP_NAME,  
    4.     e.EMP_AGE,  
    5.     e.EMP_SALARY,  
    6.     e.EMP_CITY,  
    7.     EMP_GENDER=  
    8.     (CASE e.EMP_GENDER  
    9.     WHEN '0' THEN 'FEMALE'  
    10.     WHEN '1' THEN 'MALE'   
    11.     ELSE NULL  
    12.     END  
    13.     )  
    14. FROM tblEmployee e  

    Syntax 2
    1. SELECT  
    2.     e.EMP_IID,  
    3.     e.EMP_NAME,  
    4.     e.EMP_AGE,  
    5.     e.EMP_SALARY,  
    6.     e.EMP_CITY,  
    7.     EMP_GENDER=  
    8.     ( CASE  
    9.     WHEN  e.EMP_GENDER ='0' THEN 'FEMALE'  
    10.     WHEN e.EMP_GENDER ='1' THEN 'MALE'   
    11.     ELSE NULL  
    12.     END  
    13.     )  
    14. FROM tblEmployee e  

    Output

    SELECT Statements 
Example 3 (UPDATE Statement)

    Syntax 1

    1. UPDATE tblEmployee  
    2. SET EMP_SALARY = (CASE EMP_CITY  
    3.     WHEN 'ALWAR' THEN 35000  
    4.     WHEN 'JAIPUR' THEN 40000  
    5.     ELSE 38000  
    6. END)  

    Syntax 2
    1. UPDATE tblEmployee  
    2. SET EMP_SALARY = (CASE   
    3.     WHEN EMP_CITY='ALWAR' THEN 35000  
    4.     WHEN EMP_CITY='JAIPUR' THEN 40000  
    5.     ELSE 38000  
    6. END)  

    Output

    UPDATE Statement 

Example 4 (DELETE Statement)

    1. DELETE FROM tblEmployee  
    2. WHERE EMP_CITY = (CASE  
    3.         WHEN EMP_SALARY <= 25000 THEN 'ALWAR'  
    4.         WHEN EMP_SALARY > 25000 AND  
    5.             EMP_SALARY < 30000 THEN 'JAIPUR'  
    6.         ELSE NULL  
    7.     END)  

    Output

    tblEmployee

Example 5 (DELETE Statement)

    1. DELETE FROM tblEmployee  
    2. WHERE EMP_CITY = (CASE EMP_SALARY  
    3.         WHEN  22000 THEN 'ALWAR'  
    4.         WHEN  47000  THEN 'JAIPUR'  
    5.         ELSE NULL  
    6.     END)  

    Output

    DELETE Statement 

Example 6 (ORDER BY)

    Syntax 1

    1. SELECT  
    2.     *  
    3. FROM tblEmployee e  
    4. ORDER BY CASE e.EMP_GENDER  
    5.     WHEN '0' THEN e.EMP_NAME  
    6. END DESC,  
    7. CASE e.EMP_GENDER  
    8.     WHEN '1' THEN e.EMP_IID  
    9. END DESC  

    Syntax 2
    1. SELECT  
    2.     *  
    3. FROM tblEmployee e  
    4. ORDER BY CASE  
    5.     WHEN e.EMP_GENDER = '0' THEN e.EMP_NAME  
    6. END DESC,  
    7. CASE  
    8.     WHEN e.EMP_GENDER = '1' THEN e.EMP_IID  
    9. END DESC  

    Output

    ORDER BY 

Example 7 (HAVING)

    Syntax 1

    1. SELECT  
    2.     e.EMP_NAME,  
    3.     e.EMP_AGE,  
    4.     e.EMP_SALARY,  
    5.     MAX(e.EMP_SALARY) AS MAXSALARY  
    6. FROM tblEmployee e  
    7.   
    8. GROUP BY    e.EMP_NAME,  
    9.             e.EMP_AGE,  
    10.             e.EMP_SALARY  
    11. HAVING (MAX(CASE e.EMP_GENDER  
    12.     WHEN '0' THEN e.EMP_SALARY  
    13.     ELSE NULL  
    14. END)) > 30000  
    15. OR (MAX(CASE e.EMP_GENDER  
    16.     WHEN '1' THEN e.EMP_SALARY  
    17.     ELSE NULL  
    18. END)) < 30000  

    Syntax 2
    1. SELECT  
    2.     e.EMP_NAME,  
    3.     e.EMP_AGE,  
    4.     e.EMP_SALARY,  
    5.     MAX(e.EMP_SALARY) AS MAXSALARY  
    6. FROM tblEmployee e  
    7.   
    8. GROUP BY    e.EMP_NAME,  
    9.             e.EMP_AGE,  
    10.             e.EMP_SALARY  
    11. HAVING (MAX(CASE   
    12.     WHEN e.EMP_GENDER= '0' THEN e.EMP_SALARY  
    13.     ELSE NULL  
    14. END)) > 30000  
    15. OR (MAX(CASE   
    16.     WHEN e.EMP_GENDER= '1' THEN e.EMP_SALARY  
    17.     ELSE NULL  
    18. END)) < 30000  

    Output

    HAVING 

Example 8 (STORED PROCEDURE)

This example illustrates how to use CASE Statements in a Stored Procedure. 

    1. CREATE PROC MY_PROC (@EMP_SALARY INT)  
    2. AS  
    3. BEGIN  
    4.   
    5.     DELETE FROM tblEmployee  
    6.     WHERE EMP_CITY = (CASE  
    7.             WHEN @EMP_SALARY <= 25000 THEN 'ALWAR'  
    8.             WHEN @EMP_SALARY > 25000 AND  
    9.                 @EMP_SALARY < 30000 THEN 'JAIPUR'  
    10.             ELSE NULL  
    11.         END)  
    12.   
    13. END   
Example 9 (VIEW)

This example illustrates how to use CASE Statements with views. 
    1. CREATE VIEW MY_VIEW  
    2. AS  
    3. SELECT  
    4.     e.EMP_IID,  
    5.     e.EMP_NAME,  
    6.     e.EMP_AGE,  
    7.     e.EMP_SALARY,  
    8.     e.EMP_CITY,  
    9.     EMP_GENDER=  
    10.     ( CASE  
    11.     WHEN  e.EMP_GENDER ='0' THEN 'FEMALE'  
    12.     WHEN e.EMP_GENDER ='1' THEN 'MALE'   
    13.     ELSE NULL  
    14.     END  
    15.     )  
    16. FROM tblEmployee e  

    Now select values from the view:
    1. SELECT * FROM MY_VIEW mv  

    Output

    VIEW  
I hope this article helps you in understanding Case Expressions in SQL.

Up Next
    Ebook Download
    View all
    Basic SQL Queries
    Read by 10.6k people
    Download Now!
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs