An Introduction To SQL Case Statement

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.

This article will explore the following topics:

  • The SQL CASE statement
  • CASE Statement with ORDER BY
  • CASE Statement with GROUP BY
  • CASE Statement with UPDATE
  • CASE Statement with Limitations

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

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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

Introduction To Data Analytics (FREE)

Mastery to Data Analytics Basics is a Click Away!Start Learning
Introduction To Data Analytics (FREE)

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.
Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

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 SQL certification course.

This SQL certification course 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. 

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.