添加链接
link管理
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接
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, 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' 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' BEGIN SELECT 'Table is big'