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. 

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

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. 

CASE

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Now we will do some exercises on CASE Expressions.

First we will create a table and insert some value in that table.

CREATE TABLE tblEmployee (
    EMP_IID INT NOT NULL,
    EMP_NAME VARCHAR(MAX) NOT NULL,
    EMP_AGE INT NOT NULL,
    EMP_SALARY INT NOT NULL,
    EMP_CITY VARCHAR(MAX) NOT NULL,
    EMP_GENDER CHAR(1) NOT NULL
)


INSERT INTO tblEmployee
SELECT 1,'PANKAJ',20, 25000, 'ALWAR', '1' UNION ALL
SELECT 2,'RAHUL',19, 22000, 'JAIPUR', '1' UNION ALL
SELECT 3,'PRIYA',21, 28000, 'ALWAR', '0' UNION ALL
SELECT 4,'SANDEEP',20, 23000, 'JAIPUR', '1' UNION ALL
SELECT 5,'SONAL',22, 32000, 'ALWAR', '0' UNION ALL
SELECT 6,'SANJEEV',21, 50000, 'ALWAR', '1' UNION ALL
SELECT 7,'KOMAL',23, 47000, 'JAIPUR', '0'

Now tblEmployee will look as the following: 

SELECT * FROM tblEmployee e;

Now we will see some examples.

Example 1

This example shows a general use of a CASE expression. 

Syntax 1

DECLARE @MYCOUNT INT;
SET @MYCOUNT = 3
SELECT
    CASE (@MYCOUNT)
        WHEN 1 THEN 'ONE'
        WHEN 2 THEN 'TWO'
        WHEN 3 THEN 'THREE'
        ELSE 'WRONG CHOICE'
    END AS [MESSAGE]

Output



Syntax 2


DECLARE @MYCOUNT INT;
SET @MYCOUNT = 9
SELECT
    CASE
        WHEN @MYCOUNT < 5 THEN 'VALUE IS LESS THAN 5'
        WHEN @MYCOUNT >= 5 AND
            @MYCOUNT < 10 THEN 'VALUE IS LESS THAN 10 BUT GREATER THAN 5'

        ELSE 'VALUE GREATER THAN 10'
    END AS [MESSAGE]

Output

 

Example 2 (SELECT Statements)

Syntax 1

SELECT
    e.EMP_IID,
    e.EMP_NAME,
    e.EMP_AGE,
    e.EMP_SALARY,
    e.EMP_CITY,
    EMP_GENDER=
    (CASE e.EMP_GENDER
    WHEN '0' THEN 'FEMALE'
    WHEN '1' THEN 'MALE'
    ELSE NULL
    END
    )
FROM tblEmployee e

Syntax 2


SELECT
    e.EMP_IID,
    e.EMP_NAME,
    e.EMP_AGE,
    e.EMP_SALARY,
    e.EMP_CITY,
    EMP_GENDER=
    ( CASE
    WHEN  e.EMP_GENDER ='0' THEN 'FEMALE'
    WHEN e.EMP_GENDER ='1' THEN 'MALE'
    ELSE NULL
    END
    )
FROM tblEmployee e

Output

 

Example 3 (UPDATE Statement)

Syntax 1

UPDATE tblEmployee
SET EMP_SALARY = (CASE EMP_CITY
    WHEN 'ALWAR' THEN 35000
    WHEN 'JAIPUR' THEN 40000
    ELSE 38000
END)

Syntax 2


UPDATE tblEmployee
SET EMP_SALARY = (CASE
    WHEN EMP_CITY='ALWAR' THEN 35000
    WHEN EMP_CITY='JAIPUR' THEN 40000
    ELSE 38000
END)

Output

 

Example 4 (DELETE Statement)

DELETE FROM tblEmployee
WHERE EMP_CITY = (CASE
        WHEN EMP_SALARY <= 25000 THEN 'ALWAR'
        WHEN EMP_SALARY > 25000 AND
            EMP_SALARY < 30000 THEN 'JAIPUR'
        ELSE NULL
    END)

Output

Example 5 (DELETE Statement)

DELETE FROM tblEmployee
WHERE EMP_CITY = (CASE EMP_SALARY
        WHEN  22000 THEN 'ALWAR'
        WHEN  47000  THEN 'JAIPUR'
        ELSE NULL
    END)

Output

 

Example 6 (ORDER BY)

Syntax 1

SELECT
    *
FROM tblEmployee e
ORDER BY CASE e.EMP_GENDER
    WHEN '0' THEN e.EMP_NAME
END DESC,
CASE e.EMP_GENDER
    WHEN '1' THEN e.EMP_IID
END DESC

Syntax 2


SELECT
    *
FROM tblEmployee e
ORDER BY CASE
    WHEN e.EMP_GENDER = '0' THEN e.EMP_NAME
END DESC,
CASE
    WHEN e.EMP_GENDER = '1' THEN e.EMP_IID
END DESC

Output

 

Example 7 (HAVING)

Syntax 1

SELECT
    e.EMP_NAME,
    e.EMP_AGE,
    e.EMP_SALARY,
    MAX(e.EMP_SALARY) AS MAXSALARY
FROM tblEmployee e

GROUP BY    e.EMP_NAME,
            e.EMP_AGE,
            e.EMP_SALARY
HAVING (MAX(CASE e.EMP_GENDER
    WHEN '0' THEN e.EMP_SALARY
    ELSE NULL
END)) > 30000
OR (MAX(CASE e.EMP_GENDER
    WHEN '1' THEN e.EMP_SALARY
    ELSE NULL
END)) < 30000

Syntax 2


SELECT
    e.EMP_NAME,
    e.EMP_AGE,
    e.EMP_SALARY,
    MAX(e.EMP_SALARY) AS MAXSALARY
FROM tblEmployee e

GROUP BY    e.EMP_NAME,
            e.EMP_AGE,
            e.EMP_SALARY
HAVING (MAX(CASE
    WHEN e.EMP_GENDER= '0' THEN e.EMP_SALARY
    ELSE NULL
END)) > 30000
OR (MAX(CASE
    WHEN e.EMP_GENDER= '1' THEN e.EMP_SALARY
    ELSE NULL
END)) < 30000

Output

 

Example 8 (STORED PROCEDURE)

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

CREATE PROC MY_PROC (@EMP_SALARY INT)
AS
BEGIN

    DELETE FROM tblEmployee
    WHERE EMP_CITY = (CASE
            WHEN @EMP_SALARY <= 25000 THEN 'ALWAR'
            WHEN @EMP_SALARY > 25000 AND
                @EMP_SALARY < 30000 THEN 'JAIPUR'
            ELSE NULL
        END)

END

Example 9 (VIEW)

This example illustrates how to use CASE Statements with views. 

CREATE VIEW MY_VIEW
AS
SELECT
    e.EMP_IID,
    e.EMP_NAME,
    e.EMP_AGE,
    e.EMP_SALARY,
    e.EMP_CITY,
    EMP_GENDER=
    ( CASE
    WHEN  e.EMP_GENDER ='0' THEN 'FEMALE'
    WHEN e.EMP_GENDER ='1' THEN 'MALE'
    ELSE NULL
    END
    )
FROM tblEmployee e

Now select values from the view:


SELECT * FROM MY_VIEW mv

Output

  

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