How to Use OR Condition in CASE WHEN Statement? – Interview Question of the Week #102

Question: How to Use OR condition in CASE WHEN Statement?

Answer: I personally do not like this kind of questions where user is tested on their understanding of the tricky words rather than SQL Server knowledge. However, we find all the kinds of people in the real world and it is practically impossible to educate everyone. In this blog post we will see a question asked for one of my friends in the interview about How to use OR condition in a CASE WHEN statement.

It is practically not possible to use OR statement in CASE statement as the structure of the CASE statement is very different. Here are two different ways how you can use different conditions in the CASE statement.

Method 1: Simple CASE Expressions

A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalence. If these expressions are equivalent, the expression in the THEN clause will be returned.

DECLARE @TestVal INT
SET @TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END

Method 2: Searched CASE expressions

A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expression is shown below:

DECLARE @TestVal INT
SET @TestVal = 5
SELECT
CASE
WHEN @TestVal >=3 THEN 'Top 3'
ELSE 'Other'
END

Here are two related blog posts on this subject:

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Implementig IF … THEN in SQL SERVER with CASE Statements

Reference: Pinal Dave (https://blog.sqlauthority.com)