SQL CASE Statement with Multiple Conditions

SQL Case Statement -The case statement is the heart of SQL. Because It can control the execution of different sets of statements. It handles the WHEN/THEN statement.  By using it, we can filter & optimize queries through selection rows that satisfied our requirements. We can also use it in an insert, select, update or delete statement along with Where, Order By, Group By clause so on.

As we know that we perform different actions in different conditions in our real world. So, the case statement is also similar to our real-world actions. lets’ we understand with the following real-world example –

If a student has marks –

• greater than 80% then he is the topper.
• from 60% to 80%  then he gets the first division.
• from 45% to 60% then he gets the second division.
• between 29% to 45% then he gets the third division
• less than 30%  then he fails
• else he is blocked

In the above example, we have seen that different actions are performed in different conditions. The case statement also has a similar capability to perform different actions based upon different conditions.

Read Also 

SQL Interview Questions

You will get more information about case statements like their syntax, comparison operator, example, usage & how to execute. So, continue reading this tutorial without skipping any one of the given steps.

SQL CASE Statement – Overview

Case statement controls the different sets of a statement based upon different conditions. It contains WHEN, THEN & ELSE statements to execute the different results with different comparison operators like =, >, >=, <, <=  so on.

Case executes through the conditions and returns a result when the condition is true. Once a condition is true, It will stop reading the next statement and return the result. If all the conditions are false, It will return the result of the ELSE block.

Note – If the ELSE block does not exist and all the conditions are false, it will return a NULL value.

CASE- Syntax

You have to use one of the following syntaxes to execute the SQL statement with CASE.

Simple CASE Syntax –

SQL case statement with multiple values. So, you should use simple case syntax if you want to get the result based upon different values.

CASE case_value
 WHEN value_1 THEN statement_1 
 WHEN value_2 THEN statement_2 
 WHEN value_3 THEN statement_3 
 WHEN value_4 THEN statement_4 
 WHEN value_5 THEN statement_5 
 WHEN value_n THEN statement_n 
 ELSE statement
END AS [ALIAS_NAME]

• Simple Case syntax checks only the equality of case_value with value_1 to value_n.
• The case_value matches the first value then next below values in proper order-
  • If case_value is equal to Value_1, then next WHEN…THEN statements will be skipped, and CASE execution will be stopped immediately.
  • If case_value is not equal to Value_1, then case_value will  match with value_2 for equality. This process of comparing case_value with value will continue until case_value  matches equality value with value_2, value_3, value_4……value_n
  • If case_value does not any values, then the statement of else block will be executed.
• ELSE is optional.
• If ELSE does not exist and case_value also does not match any of the values, Case will return a NULL value.

Searched Case Statement

SQL case statement with multiple conditions is known as the Search case statement. So, You should use its syntax if you want to get the result based upon different conditions -.

CASE
    WHEN condition_1 THEN statement_1
    WHEN condition_2 THEN statement_2
    WHEN condition_3 THEN statement_3
    WHEN condition_4 THEN statement_4
    WHEN condition_5 THEN statement_5
    WHEN condition_n THEN statement_n
    ELSE statement
END AS [ALIAS_NAME];

• Searched Case checks equality as well as a Boolean expression using a comparison operator.
• The Searched case matches the first condition then the next below condition in proper order-
  • If condition_1 is true, then the next conditions will be skipped, and CASE execution will be stopped immediately.
  • If condition_1 is false, then the case checks the next condition. This process of checking conditions will continue until the condition is not true.
  • If no conditions are true, then the statement of else block will be executed.
• ELSE is optional.
• If ELSE does not exist and case_value also no conditions are true, Case will return a NULL value.

CASE- Comparison Operator

If you have to use a searched case statement then you will need to use a comparison operator. So, you should know these operators to use it.

SQL CASE Statement Example

Now, we will understand the SQL case statement in a select statement through the following examples.

Table Name – students

Suppose that we have store 10 records of students examination in the following table.

Simple Case Example –

Suppose that we have to show the result of five students with their unique roll number. So we can get the result using a simple case statement through the following conditions.

When a Student has Roll Number –

• 1001 then he is ‘State Topper’.
• 1005 then he is ‘District Topper’.
• 1022 then he is ‘School Topper’.
• 2020 then he is ‘Village Topper’.
• Otherwise, he is ‘pass’.

Query –

SELECT name, roll_number, mark,
CASE roll_number
  WHEN '1001' THEN 'State Topper'
  WHEN '1005' THEN 'District Topper'
  WHEN '1022' THEN 'Scool Topper'
  WHEN '2020' THEN 'Village Topper'
  ELSE 'Pass'
END AS result
FROM students;

Output –

Searched Case Example –

If a student has marks –

• greater than 80% then he is the topper.
• from 60% to 80%  then he gets the first division.
• from 45% to 60% then he gets the second division.
• between 29% to 45% then he gets the third division
• less than 30%  then he fails
• else he is blocked

Query –

SELECT name, roll_number, mark,
CASE
    WHEN mark > 80% THEN 'Topper'
    WHEN mark >= 60% AND mark <= 80% THEN 'First Division'
    WHEN mark >= 45% AND mark <= 60% THEN 'Second Division'
    WHEN mark > 29% AND mark <45% THEN 'Third Division'
    WHEN mark < 30% THEN 'Fail'
    ELSE 'Blocked'
END AS result
FROM students;

Output –

SQL CASE  Statement with other Statements

You can also use the SQL CASE statement with the following different statements.

CASE with INSERT Statement

You can use the SQL case statement with the INSERT statement. Here, I have given a simple example to insert a single value in students table.

SET mark=300;
INSERT INTO students(name, roll_number, mark) VALUES('Aayush Aryan',2106,
CASE mark
    WHEN 400 THEN 80%
    WHEN 300 THEN 60%
    WHEN 200 THEN 40%
    WHEN 100 THEN 20%
    ELSE mark
END 
);

CASE with UPDATE Statement

You can also use the SQL case statement with an UPDATE statement. Here the following query will update two records that have 55% & 25%.

UPDATE students set mark= CASE mark
    WHEN 55% THEN 60%
    WHEN 25% THEN 30%
    ELSE mark
END;

CASE  with DELETE Statement

You can also use the SQL case statement with the DELETE statement. Here, the following query will delete those records that have id 5 & 10.

DELETE students WHERE id= CASE mark
    WHEN 34% THEN 5
    WHEN 25% THEN 10
END;

SQL CASE Statement in different Clauses

Now, you will learn to use the SQL case statement in the different clauses. These clauses will be used when you implement it with the SELECT statement.

CASE  in WHERE Clause

With this query, you will quickly learn how to use the SQL case statement in where clause.

SELECT name, roll_number, mark WHERE roll_numer= CASE mark
   WHEN 99% THEN 1001
   WHEN 90% THEN 1022
END
FROM students;

CASE in ORDER BY

With this query, you will quickly learn to use SQL statement in order by clause

Select name,Gender,roll_number, mark
from students ORDER BY 
CASE gender
WHEN 'female' THEN id 
End DESC,
Case 
WHEN gender='male' THEN id ASC  
END;

CASE  in GROUP BY

With this query, you will easily implement SQL case statement in the group by clause

Select count(id), name, mark from students Group By
CASE
WHEN mark >80% THEN 'male'
WHEN mark <=30% AND mark >= 60% THEN 'female'
END;

How to Execute SQL Case Statement

You will have to configure the following steps if you want to execute SQL case statement–

• First of all, Make sure that SQL Server is installed on your computer.
• Create a Database my_db
• Create a table, students
• Insert the required values in the created table.
• write SQL statements to execute.

Simple Case –

mysql>CREATE DATABASE my_db;
mysql>USE my_db;
mysql>CREATE TABLE students
      ->(
      ->id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      ->name varchar(50),
      ->city varchar(100),
      ->roll_number int(100),
      ->mark int(100)
      ->);
mysql>INSERT INTO students(name, gender, roll_number, mark)
     ->VALUES('Rapson jani', 'male', '1001', '99%'),
     ->('Jhon Pratap', 'male', '1005', '95%'),
     ->('Sunil Kumar', 'male', '1006', '55%'),
     ->('Mamta Kumari', 'female', '1015','67%'),
     ->('Sonali Mishra', 'female', '1018', '34%'),
     ->('Aryan Razz', 'male', '1022', '90%'),
     ->('Noor Khan', 'male', '2020', '85%'),
     ->('Sunny Rathore', 'male', '2030', '55%'),
     ->('Twinkle Yadav', 'female', '2201', '30%'),
     ->('Monika Singh', 'female', '2105', '25%');
mysql>SELECT name, roll_number, mark,
    -> CASE roll_number 
    ->WHEN '1001' THEN 'State Topper'
    ->WHEN '1005' THEN 'District Topper'
    ->WHEN '1022' THEN 'Scool Topper'
    ->WHEN '2020' THEN 'Village Topper' 
    ->ELSE 'Pass'
    -> END AS result 
    ->FROM students;

Searched Case –

You had already created a database & table in the previous query. Now need not create a new one. just use them and execute searched case query –

mysql>use my_db
mysql>SELECT name, roll_number, mark, 
     ->CASE 
     ->WHEN mark > 80% THEN 'Topper' 
     ->WHEN mark >= 60% AND mark <= 80% THEN 'First Division' 
     ->WHEN mark >= 45% AND mark <= 60% THEN 'Second Division' 
     ->WHEN mark > 29% AND mark < 45% THEN 'Third Division' 
     ->WHEN mark < 30% THEN 'Fail' 
     ->ELSE 'Blocked' 
     ->END AS result 
     ->FROM students;

Tutorial Suggestion –

You have learned to execute SQL CASE Statement with the best example. Now you can easily implement it with different & complex query conditions.

If you have any questions related to SQL, Kindly ask me through the comment box. Even you can suggest sharing another web development tutorial.

Thanks for giving time to this tutorial. Keep visiting my blog to learn more & become an expert in the coding field.