Answers by Sentry

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:

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:

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.

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.

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.

SELECT Name,
       CASE
            WHEN Name = 'Amir' OR
                 Name = 'Aya'
            THEN Age
            ELSE -1
        END AS Age
FROM Person;

Loved by over 4 million developers and more than 90,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
Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

Try Sentry For FreeRequest a Demo
    TwitterGitHubDribbbleLinkedinDiscord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.