What are CASE Expressions in SQL

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

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

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

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

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

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.