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.

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.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.