What is the CASE statement in SQL Server with or condition?

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.

Introduction to CASE Statement in SQL Server

The CASE statement in SQL Server is a powerful conditional expression that allows for conditional logic within SQL queries. It provides a way to perform different actions based on different conditions within a single query.

1. Simple CASE Expression

In SQL Server we have two types of cases present first one is “Simple CASE Expression” and another one is “Searched CASE Expression“.

Syntax:

--Syntax of Simple CASE Expression
CASE variable
     WHEN value THEN result [ ...n ]
     [ ELSE else_result ]
END

Explanation: Over here with CASE we will give the variable name or generally the column name, with WHEN we will give the value that will be matched with the column and if it returns the true result, THEN the result will be given as output.

Simple Case Expression allows only an equality check. And if any when clause doesn’t return a true result then the Else part will be executed.

2. Searched CASE Expression

Search CASE Expression is different from Simple one because it won’t give any variable or column name with which the when expression is validated. Here we directly provide the Boolean expression which can be validated directly.

Syntax:

--Syntax of Searched CASE Expression
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Explanation: So, here we can see that we are giving the Boolean expression rather than the value. Also here we can provide different comparison operators and we are not bound to only equality operators.

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:

EmpTable Output

1. Simple CASE expression

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

Output:

Simple CASE Expression

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:

Searched CASE Expression

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.

OR is Not Supported With the CASE Statement?

As we have seen in the Simple Case statement thus we cannot give the Boolean expression and OR always come with the Boolean expression and thus it gives the error “Incorrect syntax near the keyword ‘or'”.

But if we are using the Searched Case Statement then it will allow us to use the OR with it.

Query:

Select Name, 
 CASE 
    WHEN Age <= 25 OR Gender = 'Male' THEN 'Young or Male'
    WHEN Age > 25 OR Gender = 'Female' THEN 'Old or Female'
    ELSE 'Unknown'
END
from EmpTable
go

Output:

Output

Explanation: This is just one example in which we are comparing age and Gender but we can have a different condition with OR in this case.

If we have to use the same column and we have to compare some values with the equality operator and if the result is the same then we can use something like this:

Query:

Select 
CASE
WHEN Column IN (value1, value2, [valuen]) THEN result_expression
ELSE else_expression
END
From Table

Conclusion

So, using OR with Simple CASE is not good but we can choose to move with Searched CASE and by doing this we can achieve the result. Only the difference between Simple and Searched is that you have to mention the whole expression in the when clause in Searched CASE while in Simple CASE you can only match the expression with the value of one column.