The CASE statement in SQL returns a value for the condition specified. It tests a list of conditions and returns one of the multiple possible results. We mostly use a case expression in SQL stored procedures or as a formula for a particular column which optimizes the SQL statements. In this article, we would explore the SQL CASE statement and its various use cases.
The SQL CASE Statement
The CASE statement in SQL is a way of handling the IF/THEN logic. The CASE statement is always followed by a minimum of one pair of WHEN and THEN statements.
Syntax:
SELECT CASE Expression
When expression1 Then Result1
When expression2 Then Result2
...
ELSE Result
END
Consider the following database:
In the following SQL statement, we are using a comparison operator to evaluate an expression.
In the following image, we get the salary details as per the condition specified in the CASE statement.
CASE Statement With ORDER BY Clause
The CASE statement can be used in the ORDER BY clause as well. In SQL, the ORDER BY clause is used to sort the result in ascending or descending order.
In the following SQL statement, you can see the use of the ORDER BY and CASE statements together.
In the output, it sorts the salary in descending order where the office id is equal to 1 and in ascending order where the office id is equal to 2.
CASE Statement With GROUP BY Clause
Suppose we want to group the employees based on their salaries and calculate the maximum and minimum salary for a specified range of employee data.
In the following SQL statement, you can see the use of the GROUP BY clause with the required CASE statement.
The statement will produce the following result.
CASE Statement With UPDATE statement
Consider the following dataset.
In the following example, we are updating the state code using the CASE statement.
As you can see, we updated the state column according to the given condition.
CASE Statement Limitations
- You cannot control the flow of execution of stored procedures and functions.
- You cannot check the NULL value using the CASE statement.
Conclusion
The CASE statement on the SQL adds versatility to the SQL queries. We implemented the CASE statement with several other keywords.
If you wish to learn more about SQL, then check out our Post Graduate Program in Full Stack Web Development.
This Post Graduate Program in Full Stack Web Development helps you get the basics right with the practical training you need to work with SQL databases and use them in the applications you use or create. On completing the course, you will have a work-ready understanding of how to structure your database correctly, create error-free and efficient SQL statements and clauses, and design and manage your SQL databases for scalable growth.
If you have any questions, please feel free to ask them in our comments section, and our experts will answer them for you right away.