4 Examples of CASE expression in SQL

What is CASE and where is it used?
When a condition is met, the CASE statement evaluates different conditions and returns a scalar value. The phrase from the ELSE block will be returned if none of the conditions are evaluated to TRUE. The ELSE block, however, is optional. Above all, the CASE statement has two formats Simple CASE & Searched CASE.

The Searched CASE statement is highlighted in this post. To get the results, you'll need to use search and pattern matching.

4 CASE statement usages ane Examples

Now let's see different types of CASE statement and their example to understand this key SQL concept in detail.

1. Simple CASE statement

We may use a simple CASE expression to compare various expressions instead of creating stacked IF statements. A basic CASE expression compares the first expression to the WHEN clause expression in this way. 

If there is a match, the expression following THEN is evaluated; otherwise, the expression in the ELSE block is evaluated. It's worth noting that the ELSE block is optional.

A simple CASE statement has the below-given syntax :

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Now, let's work on a real-time example. Let's create a table named Customers with the following sample data as shown below : 

Now, let's write a CASE statement and demonstrate how and why it is used. let's write a CASE statement by filtering countries. Let's do it for 2 countries Germany, Mexico, and the rest. The Query would be like something below : 

SELECT CustomerID, CustomerName, City,
CASE 
    WHEN Country = 'Germany' THEN 'German it is sire!' 
    WHEN Country = 'Mexico' THEN 'The country of Pablo!' 
    ELSE 'None other matters' 
END AS CountryDescription 
FROM Customers;

The result of the above query would be something like this : 

2. searched CASE

CASE was searched, and SIMPLE was expanded. This analyses a collection of boolean expressions, such as pattern matching and range comparisons. If any of the boolean expressions is TRUE, then the expression after THEN is evaluated. If none of the boolean expressions after WHEN evaluate to TRUE, the ELSE block is evaluated.

Economy class is defined as a wage that is higher than 500 but less than 2000. Furthermore, a salary of more than $2,000 is classified as a premium. If the salary falls outside of a set range, an ELSE block with no travel is provided.

The query for the same will be : 

SELECT ID, Name, Gender, Salary,
CASE  WHEN Salary >= 500 AND Salary < 2000 THEN 'Economy Class'
      WHEN Salary >= 2000 THEN 'Premium Class'
      ELSE 'No Travel' 
END TravelMode
FROM  
 (VALUES (1,'Vish', 'M', 100)
        ,(2,'Atul', 'M', 200)
        ,(3,'Vishal','M', 500)
        ,(4,'Kasturi','F',2000)
        ,(5,'Belinda','F',5000))
 as Emp(Id, Name, Gender, Salary);

The resultant would look like below : 

ID          Name    Gender Salary      TravelMode
----------- ------- ------ ----------- -------------
1           Vish    M      100         No Travel
2           Atul    M      200         No Travel
3           Vishal  M      500         Economy Class
4           Kasturi F      2000        Premium Class
5           Belinda F      5000        Premium Class





3. Searched CASE with IN, OR and, LIKE operator
This example shows how to utilise CASE expressions with various clauses and operators. The IN, OR, and LIKE operators are used for pattern matching and comparison in the query below. In this situation, the IN and OR operators are practically identical. LIKE allows us to use pattern matching with ease.


The query would look like this : 

SELECT ID, Name, Gender, Salary,
CASE WHEN  Name IN ('Atul', 'Belinda') THEN 'Class 1'
     WHEN  Name LIKE 'K%' THEN 'Class 2'
     WHEN  (NAME = 'Vish' OR Name = 'Vishal') THEN 'Class 3'
     ELSE  'No Class'
END as ClassInfo
           FROM  
 (VALUES (1,'Vish', 'M', 100)
        ,(2,'Atul', 'M', 200)
        ,(3,'Vishal','M', 500)
        ,(4,'Kasturi','F',2000)
        ,(5,'Belinda','F',5000)
        ,(5,'Simona','M',5000))
 as Emp(Id, Name, Gender, Salary);



The answer we get from the above query would look like below : 







4. Searched CASE Expression with CHARINDEX & PATINDEX
Similarly, with the searched CASE, use the CHARINDEX and PATINDEX functions. The following example shows how to use the CHARINDEX function to determine if a character occurs in any given name. Pattern matching with PATINDEX to see if (Underscore) appears anywhere in the name column.


The query would look like this : 


SELECT ID, Name, Gender, Salary,
CASE WHEN  CHARINDEX('s', Name) > 0 THEN 'Contains char s'
     WHEN  PATINDEX('%_%',Name) > 0 THen 'Underscore Exists'
     ELSE  'Optional'
END as ClassInfo
           FROM  
 (VALUES (1,'Vish', 'M', 100)
        ,(2,'Atul_', 'M', 200)
        ,(3,'Vishal','M', 500)
        ,(4,'Kasturi','F',2000)
        ,(5,'Belinda_','F',5000)
        ,(5,'Simona','M',5000))
 as Emp(Id, Name, Gender, Salary);


The answer we get from the above query would look like below : 


ID          Name     Gender Salary      ClassInfo
----------- -------- ------ ----------- -----------------
1           Vish     M      100         Contains char s
2           Atul_    M      200         Underscore Exists
3           Vishal   M      500         Contains char s
4           Kasturi  F      2000        Contains char s
5           Belinda_ F      5000        Underscore Exists
5           Simona   M      5000        Contains char s




Conclusion

That's all about the CASE expression in SQL. In this tutorial, you have learned about the Searched CASE expression in this post. The two most popular forms are simple and searched cases. In comparison to Simple, we can accomplish more with Searched case thanks to extended pattern machining and the usage of functions. Furthermore, the majority of the functions interact with Searched CASE in order to do more.


My Other SQL Articles and Tutorials you may like
Difference between DDL and DML commands in SQL
How to remove duplicate rows in SQL
How to concatenate columns in SQL
How to find Nth Highest salary in SQL
How to use Cross Join in SQL
How to use ORDER BY in SQL with example
How to use row_number function in SQL
15 SQL Interview Questions with Answers
How to use LEFT, RIGHT, and SELF JOIN
What is a Virtual column in MySQL? Example
How to do pagination in SQL with Examples
How to transpose data in SQL with examples

Thanks
 for reading this article so far. If you like these common examples of CASE expression in SQL  then please share it with
 your friends and colleagues. If you have any questions or feedback then
 please drop a note.