Sentry Answers>SQL>

How do I use OR in a CASE statement in SQL Server?

How do I use OR in a CASE statement in SQL Server?

Richard C.

—

The Problem

You can use a CASE statement to select different values or columns from a table, for example:

Click to Copy
SELECT Name,
       CASE
           WHEN Age < 21 THEN 'child'
           ELSE 'adult'
       END AS Age
FROM Person;

But how can you use a CASE statement with a range of optional values? In other words, how do you use OR in a CASE statement?

Let’s use the following table of people and their ages as an example:

Click to Copy
CREATE TABLE Person
(
    Id   INT PRIMARY KEY,
    Name VARCHAR(255),
    Age  INT
);

INSERT INTO Person (Id, Name, Age)
VALUES (1, 'Amir', 25),
       (2, 'Sofia', 42),
       (3, 'Aya', 18),
       (4, 'Mateo', 30),
       (5, 'Leila', 8),
       (6, 'Yara', 35),
       (7, 'Ndidi', 12),
       (8, 'Santiago', 50);

The Solution

Assume we want to return only the ages of Amir or Aya, and set all other ages to -1. There are three ways to do this in SQL. They all work in SQL Server, PostgreSQL, and MySQL.

The neatest way is to put the range of values in a list and use IN. This should be your default option.

Click to Copy
SELECT Name,
       CASE
           WHEN Name IN ('Amir', 'Aya') THEN Age
           ELSE -1
       END AS Age
FROM Person;

You could also specify the name of the column in the CASE statement, which is useful only when you have a lot of different outputs that all depend on the same column input.

Click to Copy
SELECT Name,
       CASE Name
           WHEN 'Amir' THEN Age
           WHEN 'Aya'  THEN Age
           ELSE -1
       END AS Age
FROM Person;

Finally, you could use OR in the WHEN statement, which is useful only when you want to do complex logic or arithmetic in each part of the OR statement. This wouldn’t be possible using the IN syntax above.

Click to Copy
SELECT Name,
       CASE
            WHEN Name = 'Amir' OR
                 Name = 'Aya'
            THEN Age
            ELSE -1
        END AS Age
FROM Person;
  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.