Example of OR is Not Supported with CASE Statement

Before diving into CASE first let’s create a table of Employee.

Query:

CREATE TABLE EmpTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Age INT,
    Gender NVARCHAR(10)
);

Now, We will insert the entries into this table

INSERT INTO EmpTable (ID, Name, Age, Gender)
VALUES 
(1, 'John', 25, 'Male'),
(2, 'Emily', 30, 'Female'),
(3, 'Michael', 45, 'Male'),
(4, 'Sophia', 22, 'Female'),
(5, 'Emma', 35, 'Female'),
(6, 'William', 28, 'Male'),
(7, 'Olivia', 50, 'Female'),
(8, 'James', 20, 'Male');

Select * from EmpTable

Output:

1. Simple CASE expression

Select Name, 
CASE Gender
    WHEN 'Male' THEN 'MALE'
    WHEN 'Female' THEN 'FEMALE'
END AS ProfileName
from EmpTable

Output:

Explanation: In the above query, we have just converted the Normal Case to the upper case. But here we can see that we are comparing Gender with the Values of males and Females and thus we can not use such a case with another comparison operator.

2. Searched CASE Expression

Select Name, 
 CASE 
    WHEN Age <= 25 THEN 'Young'
    WHEN Age > 25 AND Age <= 40THEN 'Middle-aged'
    WHEN Age > 40THEN 'Senior'
    ELSE 'Unknown'
END
from EmpTable

Output:

Explanation: Here we have given the Boolean expression in the when clause and thus we can check the age lower than 25. This cannot be done with Simple CASE Expression.

In SQL Server, the CASE statement cannot directly support the use of logical operators like OR with its structure. Instead of CASE it operates based on the evaluation of multiple conditions using the WHEN keyword followed by specific conditions. In this article, we will learn about the OR is not supported with CASE Statement in SQL Server with a detailed explanation of CASE Statement and so on.