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:

dataset_employees

In the following SQL statement, we are using a comparison operator to evaluate an expression.

sql-case-simple

In the following image, we get the salary details as per the condition specified in the CASE statement. 

output-simple-case

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program
Want a Top Software Development Job? Start Here!

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.

SQL-CASE-ORDERby

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.

output-order-by

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.

sql-case-GROUPby

The statement will produce the following result.

output-group-by

CASE Statement With UPDATE statement

Consider the following dataset.

offices-database

In the following example, we are updating the state code using the CASE statement.

UPDATE1

As you can see, we updated the state column according to the given condition.

output-update

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program
Want a Top Software Development Job? Start Here!

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. 

Our Software Development Courses Duration And Fees

Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Coding Bootcamp

Cohort Starts: 17 Jun, 2024

6 Months$ 8,000
Automation Test Engineer

Cohort Starts: 29 May, 2024

11 Months$ 1,499
Full Stack Java Developer

Cohort Starts: 4 Jun, 2024

6 Months$ 1,449
Full Stack Developer - MERN Stack

Cohort Starts: 18 Jun, 2024

6 Months$ 1,449