Post
Ask Question

What are CASE Expressions in SQL

Introduction

SQL Case statement provides functionality similar to the IF-THEN-ELSE statement. A CASE statement evaluates 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 a CASE expression in SQL. Case can be used in any statement or clause allowing valid expression. For example, you can use a CASE statement with SELECT, UPDATE, DELETE, and SET in clauses such as IN, WHERE, ORDER BY, and HAVING.

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 essential points of a simple CASE expression.

  • Allows only an equality check.
  • Evaluates input_expression and then, in the order specified, evaluate 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. 

The following is an example of a simple SQL CASE expression.

CASE expression  
  
   WHEN value_1 THEN result_1  
   WHEN value_2 THEN result_2  
   ...  
   WHEN value_n THEN result_n  
  
   ELSE result  
  
END

Searched CASE Expression

The searched CASE Expression evaluates Boolean expressions to determine the result. We can use boolean, logical, and comparison operators in this CASE Expression.

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. 

Here is an example of searched CASE expression.

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 values 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 at the following.

SELECT * FROM tblEmployee e;

tblEmployee look

Now we will see some examples.

Example 1. General use case of a CASE expression

CASE expressions are powerful in SQL, allowing us to create conditional statements within a SELECT, UPDATE, INSERT, or DELETE statement. Here are some general use cases for a CASE expression:

  • Classifying data: We can use a CASE expression to classify data into different categories based on certain conditions. For example, use a CASE expression to classify products into price ranges (e.g., low, medium, high) based on their price.
  • Calculating values: We can use a CASE expression to calculate a value based on different conditions. For example, use a CASE expression to calculate a discount based on the quantity of items purchased.
  • Providing default values: We can use a CASE expression to give a default value if no other conditions are met. For example, use a CASE expression to assign a default priority level to tasks if no priority level is specified.
  • Conditional updates: We can use a CASE expression in an UPDATE statement to update different columns based on certain conditions. For example, use a CASE expression to update the price of a product based on the quantity of items in stock.
  • Conditional inserts: We can use a CASE expression in an INSERT statement to insert different values based on certain conditions. For example,  use a CASE expression to insert a default value for a column if no value is specified.

The below 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]  

This SELECT statement will declare a variable called MYCOUNT and initialize it with a value of 3. It will then use a CASE statement in the SELECT clause to display a message based on the value of MYCOUNT.

The CASE statement has four WHEN clauses, each specifying a value and a corresponding message. If MYCOUNT has a value of 1, the CASE statement will return 'ONE'; if MYCOUNT has a value of 2, the CASE statement will return 'TWO'; if MYCOUNT has a value of 3, the CASE statement will return 'THREE'; otherwise, the CASE statement will return 'WRONG CHOICE.'

The SELECT statement will display the message returned by the CASE statement in a MESSAGE column. In this case, the SELECT statement will return a row with a MESSAGE column containing the' THREE' value.

Output

CASE expression

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

MESSAGE

Example 2. CASE in a SELECT Statement

CASE expression in a SELECT statement to create a derived column that contains a value based on certain criteria. Here are two different examples of using a CASE in a SELECT statement. Here's the basic syntax for using a CASE expression in a SELECT statement:

 

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

This SELECT statement will retrieve all rows from the tblEmployee table and display the values in the EMP_IID, EMP_NAME, EMP_AGE, EMP_SALARY, and EMP_CITY columns. It will also use a CASE statement to display a text value for the EMP_GENDER column based on the value of the EMP_GENDER column.

The CASE statement in the SELECT clause has two WHEN clauses, each specifying a gender value and a corresponding text value. If the EMP_GENDER column has a value of '0', the CASE statement will return 'FEMALE'; if the EMP_GENDER column has a value of '1', the CASE statement will return 'MALE'; otherwise, the CASE statement will return NULL.

The SELECT statement will retrieve all rows from the tblEmployee table and display the values in the EMP_IID, EMP_NAME, EMP_AGE, EMP_SALARY, and EMP_CITY columns as well as the value returned by the CASE statement for the EMP_GENDER column. If the EMP_GENDER column has a value of '0', the EMP_GENDER column will be displayed as 'FEMALE'; if the EMP_GENDER column has a value of '1', the EMP_GENDER column will be displayed as 'MALE'; otherwise, the EMP_GENDER column will be displayed as NULL.

Output

SELECT Statements

Example 3. CASE in an UPDATE Statement

Case in UPDATE statement allows us to perform different actions based on different conditions. It can be used to replace the complex if-else statement and in the set clause of an update statement.

Here are some examples of using a CASE expression with an 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)

This UPDATE statement will update the values in the EMP_SALARY column of the tblEmployee table based on the values in the EMP_CITY column.

The CASE statement in the SET clause has three WHEN clauses, each specifying a city and a corresponding salary. If the EMP_CITY column has a value of 'ALWAR,' the CASE statement will return 35000; if the EMP_CITY column has a value of 'JAIPUR,' the CASE statement will return 40000; otherwise, the CASE statement will return 38000.

The UPDATE statement will update the EMP_SALARY column for all rows in the tblEmployee table with the value returned by the CASE statement. If the EMP_CITY column has a value of 'ALWAR,' the EMP_SALARY column will be updated with a value of 35000; if the EMP_CITY column has a value of 'JAIPUR,' the EMP_SALARY column will be updated with a value of 40000; otherwise, the EMP_SALARY column will be updated with a value of 38000.

It is important to note that this UPDATE statement will update all rows in the tblEmployee table, not just the rows where the EMP_CITY column has a specific value. Therefore, you should be careful when using a CASE statement in an UPDATE statement to ensure that you update the intended rows.

Output

UPDATE Statement

Example 4. CASE in a DELETE Statement

Using the CASE statement directly in a DELETE statement is impossible. However, we can use a CASE statement in a subquery in the WHERE clause of the DELETE statement to specify the rows to be deleted based on different conditions.
Here is an example of using a CASE expression with a DELETE statement.

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

This DELETE statement will delete all rows from the tblEmployee table where the EMP_CITY column's value equals the value returned by the CASE statement in the WHERE clause.

The CASE statement in the WHERE clause has three WHEN clauses, each specifying a salary and a corresponding city. If the EMP_SALARY column has a value of 22000, the CASE statement will return 'ALWAR'; if the EMP_SALARY column has a value of 47000, the CASE statement will return 'JAIPUR'; otherwise, the CASE statement will return NULL.

The DELETE statement will delete all rows where the EMP_CITY column's value equals the value returned by the CASE statement. If the EMP_SALARY column has a value of 22000, the DELETE statement will delete all rows where the EMP_CITY column has a value of 'ALWAR'; if the EMP_SALARY column has a value of 47000, the DELETE statement will delete all rows where the EMP_CITY column has a value of 'JAIPUR'; otherwise, the DELETE statement will not delete any rows.
Output

DELETE Statement

Example 5. CASE in an ORDER BY

The CASE statement can be used in the ORDER BY clause to specify the sorting order for a result set.

Here is an example of using a CASE expression with an ORDER BY clause in SQL.

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

Here is another example of using a CASE expression with ORDER BY. 

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

This SELECT statement will retrieve all rows from the tblEmployee table and order the result set based on the values in the EMP_GENDER and EMP_NAME or EMP_IID columns.

The ORDER BY clause uses two CASE statements to specify the sorting order. The first CASE statement sorts the rows where the EMP_GENDER column has a value of '0' by the EMP_NAME column in descending order. The second CASE statement sorts the rows where the EMP_GENDER column has a value of '1' by the EMP_IID column in descending order.

The SELECT statement will retrieve all rows from the tblEmployee table and order the result set as follows:

Rows, where the EMP_GENDER column has a value of '0', will be sorted by the EMP_NAME column in descending order.

Rows, where the EMP_GENDER column has a value of '1', will be sorted by the EMP_IID column in descending order.

EMP_NAME will sort the resulting result set for rows where EMP_GENDER is '0' and by EMP_IID for rows where EMP_GENDER is '1'.

Output

ORDER BY

Example 6. CASE expression with HAVING

The CASE expression can be used in the HAVING clause to filter the result set of a SELECT statement based on the values in one or more columns. 

Here is an example of using a CASE expression with a HAVING clause. 

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

Here is another example of using a CASE expression with a HAVING clause. 

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

The SELECT statement will retrieve all rows from the tblEmployee table, group them by the values in the EMP_NAME, EMP_AGE, and EMP_SALARY columns, and display them in the EMP_NAME, EMP_AGE, and EMP_SALARY columns, as well as the maximum salary among all rows. It will then filter the result set to include only the rows where the EMP_GENDER column has a value of '0,' and the EMP_SALARY column is greater than 30000, or where the EMP_GENDER column has a value of '1,' and the EMP_SALARY column is less than 30000

Output

HAVING

Example 7. Using CASE expression in a Stored Procedure

We can use the CASE expression in a stored procedure to create conditional logic in the body of the stored procedure. CASE expression allows us to specify different actions based on different values or ranges of values in a column or variable.

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

It creates a stored procedure called MY_PROC, which takes a single input parameter, EMP_SALARY, of type INT. The stored procedure contains a single DELETE statement that will delete rows from the tblEmployee table based on the value of EMP_SALARY.

The DELETE statement will delete all rows where the EMP_CITY column's value equals the value returned by the CASE statement. If EMP_SALARY is less than or equal to 25000, the DELETE statement will delete all rows where the EMP_CITY column has a value of 'ALWAR'; if EMP_SALARY is greater than 25000 and less than 30000, the DELETE statement will delete all rows where the EMP_CITY column has a value of 'JAIPUR'; otherwise, the DELETE statement will not delete any rows.

To execute this stored procedure, you would use the following syntax:

EXEC MY_PROC @EMP_SALARY = <value>

Replace <value> with the desired value for the EMP_SALARY parameter.

Suppose EMP_SALARY = 25000; then the syntax to execute the store procedure will be 

EXEC MY_PROC @EMP_SALARY = 25000


Example 8. Using CASE in a View

We can use a CASE expression to display a different value based on a set of conditions.

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

It creates a view called MY_VIEW that displays selected columns from the tblEmployee table and uses a CASE statement to display a text value for the EMP_GENDER column based on the value of the EMP_GENDER column.

The view is created using a SELECT statement that retrieves all rows from the tblEmployee table and displays the values in the EMP_IID, EMP_NAME, EMP_AGE, EMP_SALARY, and EMP_CITY columns. It also uses a CASE statement in the SELECT clause to display a text value for the EMP_GENDER column based on the value of the EMP_GENDER column.

The CASE statement in the SELECT clause has two WHEN clauses, each specifying a gender value and a corresponding text value. If the EMP_GENDER column has a value of '0', the CASE statement will return 'FEMALE'; if the EMP_GENDER column has a value of '1', the CASE statement will return 'MALE'; otherwise, the CASE statement will return NULL.

Once the view is created, you can query it like a regular table using the SELECT statement. For example:

SELECT * FROM MY_VIEW mv 

This will retrieve all rows from the view and display the values in the EMP_IID, EMP_NAME, EMP_AGE, EMP_SALARY, EMP_CITY, and EMP_GENDER columns. The EMP_GENDER column will be displayed as a text value based on the value of the EMP_GENDER column in the tblEmployee table.

Output

VIEW

Summary

In this article, we learned about SQL CASE expression and how the CASE expression is used in various SQL statements. We also learned to use CASE with SQL clauses such as ORDER BY and HAVING. We also saw a couple of examples of using CASE in SQL-stored procedures and views.


Similar Articles