SQL CASE

The CASE is a statement that operates if-then-else type of logical queries. This statement returns the value when the specified condition evaluates to True. When no condition evaluates to True, it returns the value of the ELSE part.

When there is no ELSE part and no condition evaluates to True, it returns a NULL value.

In Structured Query Language, CASE statement is used in SELECT, INSERT, and DELETE statements with the following three clauses:

WHERE Clause
ORDER BY Clause
GROUP BY Clause

This statement in SQL is always followed by at least one pair of WHEN and THEN statements and always finished with the END keyword.

The CASE statement is of two types in relational databases:

Simple CASE statement
Searched CASE statement

Syntax of CASE statement in SQL

CASE <expression>

WHEN condition_1 THEN statement_1
WHEN condition_2 THEN statement_2 …….
WHEN condition_N THEN statement_N

ELSE result

END;

Here, the CASE statement evaluates each condition one by one.

If the expression matches the condition of the first WHEN clause, it skips all the further WHEN and THEN conditions and returns the statement_1 in the result.

If the expression does not match the first WHEN condition, it compares with the seconds WHEN condition. This process of matching will continue until the expression is matched with any WHEN condition.

If no condition is matched with the expression, the control automatically goes to the ELSE part and returns its result. In the CASE syntax, the ELSE part is optional.

In Syntax, CASE and END are the most important keywords which show the beginning and closing of the CASE statement.

Examples of CASE statement in SQL

Let's take the Student_Details table, which contains roll_no, name, marks, subject, and city of students.

Roll_No	Stu_Name	Stu_Subject	Stu_Marks	Stu_City
2001	Akshay	Science	92	Noida
2002	Ram	Math	49	Jaipur
2004	Shyam	English	52	Gurgaon
2005	Yatin	Hindi	45	Lucknow
2006	Manoj	Computer	70	Ghaziabad
2007	Sheetal	Math	82	Noida
2008	Parul	Science	62	Gurgaon
2009	Yogesh	English	42	Lucknow
2010	Ram	Computer	88	Delhi
2011	Shyam	Hindi	35	Kanpur

Example 1: The following SQL statement uses single WHEN and THEN condition to the CASE statement:

SELECT Roll_No, Stu_Name, Stu_Subject, Stu_marks,
CASE
WHEN Stu_Marks >= 50 THEN 'Student_Passed'
ELSE 'Student_Failed'
END AS Student_Result
FROM Student_Details;

Explanation of above query:

Here, the CASE statement checks that if the Stu_Marks is greater than and equals 50, it returns Student_Passed otherwise moves to the ELSE part and returns Student_Failed in the Student_Result column.

Output:

Roll_No	Stu_Name	Stu_Subject	Stu_Marks	Student_Result
2001	Akshay	Science	92	Student_Passed
2002	Ram	Math	49	Student_Failed
2004	Shyam	English	52	Student_Passed
2005	Yatin	Hindi	45	Student_Failed
2006	Manoj	Computer	70	Student_Passed
2007	Sheetal	Math	82	Student_Passed
2008	Parul	Science	62	Student_Passed
2009	Yogesh	English	42	Student_Failed
2010	Ram	Computer	88	Student_Passed
2011	Shyam	Hindi	35	Student_Failed

Example 2: The following SQL statement adds more than one WHEN and THEN condition to the CASE statement:

SELECT Roll_No, Stu_Name, Stu_Subject, Stu_marks,
CASE
WHEN Stu_Marks >= 90 THEN 'Outstanding'
WHEN Stu_Marks >= 80 AND Stu_Marks < 90 THEN 'Excellent'
WHEN Stu_Marks >= 70 AND Stu_Marks < 80 THEN 'Good'
WHEN Stu_Marks >= 60 AND Stu_Marks < 70 THEN 'Average'
WHEN Stu_Marks >= 50 AND Stu_Marks < 60 THEN 'Bad'
WHEN Stu_Marks < 50 THEN 'Failed'
END AS Stu_Remarks
FROM Student_Details;

Explanation of above query:

Here, the CASE statement checks multiple WHEN and THEN conditions one by one. If the value of Stu_Marks column is greater than or equals to 90, it returns Outstanding otherwise moves to the further WHEN and THEN conditions.

If none of the conditions is matched with the Student_Details table, CASE returns the NULL value in the Stu_Remarks column because there is no ELSE part in the query.

Output:

Roll_No	Stu_Name	Stu_Subject	Stu_Marks	Stu_Remarks
2001	Akshay	Science	92	Outstanding
2002	Ram Math	49	Failed
2004	Shyam	English	52	Bad
2005	Yatin	Hindi	45	Failed
2006	Manoj	Computer	70	Good
2007	Sheetal	Math	82	Excellent
2008	Parul	Science	62	Average
2009	Yogesh	English	42	Failed
2010	Ram	Computer	88	Excellent
2011	Shyam	Hindi	35	Failed

Example 3:

Let's take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Salary.

Emp_Id	Emp_Name	Emp_Dept	Emp_Salary
1	Akshay	Finance	9000
2	Ram	Marketing	4000
3	Shyam	Sales	5000
4	Yatin	Coding	4000
5	Manoj	Marketing	5000
1	Akshay	Finance	8000
2	Ram	Coding	6000
3	Shyam	Coding	4000
4	Yatin	Marketing	8000
5	Manoj	Finance	3000

The following SQL query uses GROUP BY clause with CASE statement:

SELECT Emp_Id, Emp_Name, Emp_Dept, sum(Emp_Salary) as Total_Salary,
CASE
WHEN SUM(Emp_Salary) >= 10000 THEN 'Increment'
ELSE 'Constant'
END AS Emp_Remarks
FROM Employee_Details
GROUP BY Emp_id, Emp_Name;

Output:

Emp_Id	Emp_Name	Emp_Dept	Total_Salary	Emp_Remarks
1	Akshay	Finance	17000	Increment
2	Ram	Marketing	9000	Decrement
3	Shyam	Sales	10000	Increment
4	Yatin	Coding	12000	Increment
5	Manoj	Marketing	8000	Decrement

Example 4: In this example, we use the ORDER BY clause with a CASE statement in SQL:

Let's take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Age.

We can check the data of Employee_Details by using the following query in SQL:

Select * From Employee_Details;

Output:

Emp_Id	Emp_Name	Emp_Dept	Emp_Age
1	Akshay	Finance	23
2	Ram	Marketing	24
3	Balram	Sales	25
4	Yatin	Coding	22
5	Manoj	Marketing	23
6	Sheetal	Finance	24
7	Parul	Finance	22
8	Yogesh	Coding	25
9	Naveen	Marketing	22
10	Tarun	Finance	23

The following SQL query shows all the details of employees in the ascending order of employee names:

SELECT * FROM Employee_Details
ORDER BY Emp_Name;

Output:

Emp_Id	Emp_Name	Emp_Dept	Emp_Age
1	Akshay	Finance	23
3	Balram	Sales	25
5	Manoj	Marketing	23
9	Naveen	Marketing	22
7	Parul	Finance	22
2	Ram	Marketing	24
6	Sheetal	Finance	24
10	Tarun	Finance	23
4	Yatin	Coding	22
8	Yogesh	Coding	25

If you want to show those employees at the top who work in the Coding Department, then for this operation, you have to use single WHEN and THEN statement in the CASE statement as shown in the following query:

SELECT * FROM Employee_Details
ORDER BY CASE WHEN Emp_Dept = 'Coding' THEN 0
ELSE 1 END, Emp_Name;

Output:

Emp_Id	Emp_Name	Emp_Dept	Emp_Age
4	Yatin	Coding	22
8	Yogesh	Coding	25
1	Akshay	Finance	23
3	Balram	Sales	25
5	Manoj	Marketing	23
9	Naveen	Marketing	22
7	Parul	Finance	22
2	Ram	Marketing	24
6	Sheetal	Finance	24
10	Tarun	Finance	23

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz

SQL CASE

Syntax of CASE statement in SQL

Examples of CASE statement in SQL

Comment / Reply From

You May Also Like

Popular Post

Language

Newsletter