Sentry Answers>SQL>

How do I perform an IF...THEN in a SQL SELECT?

How do I perform an IF...THEN in a SQL SELECT?

Richard C.

—

The Problem

In all programming languages, you can write an if statement similar to the following.

Click to Copy
if person.age < 21:
    print('child')
else:
    print('adult')

How can we do the same in SQL using a SELECT 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

The if-else statement is equivalent to WHEN…ELSE in SQL. You also need to begin with CASE. Here’s an example:

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

This code will work in SQL Server, PostgreSQL, and MySQL.

SQL Server and MySQL Shorthand

If you are using SQL Server version 2012 or later, there is a more concise way of writing the above statement.

Click to Copy
SELECT
    Name,
    IIF (Age < 21, 'child', 'adult') AS Age
FROM Person;

It also works in MySQL.

Click to Copy
SELECT
    Name,
    IF (Age < 21, 'child', 'adult') AS Age
FROM Person;

This is similar to the ternary operator in C-style languages.

Click to Copy
String age = (age < 21) ? "child" : "adult";

We do not recommend this solution as it cannot be used on all database servers and it takes longer to comprehend when reading, compared to the CASE statement.

Procedural IF…ELSE Statements in SQL Server

While CASE statements are the appropriate solution for returning rows of data, there is an actual IF ELSE statement in SQL Server too. You can use it for stored procedures or working with logic outside of rows. Here is an example.

Click to Copy
IF (SELECT COUNT(1) FROM Person) < 100
    SELECT 'Table is small'
ELSE
    SELECT 'Table is big'

For more complex queries, you can wrap multiple statements in BEGIN…END blocks.

Click to Copy
IF (SELECT COUNT(1) FROM Person) < 100
BEGIN
    SELECT 'Table is small'
END
ELSE
BEGIN
    SELECT 'Table is big'
END

The code above won’t work in other database servers. For PostgreSQL and MySQL, you still have to use CASE.

Click to Copy
SELECT
    CASE
        WHEN (SELECT COUNT(1) FROM Person) < 100 THEN 'Table is small'
        ELSE 'Table is big'
    END AS result;
  • 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.