Richard C.
—In all programming languages, you can write an if statement similar to the following.
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.
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 if-else statement is equivalent to WHEN…ELSE in SQL. You also need to begin with CASE
. Here’s an example:
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.
If you are using SQL Server version 2012 or later, there is a more concise way of writing the above statement.
SELECT Name, IIF (Age < 21, 'child', 'adult') AS Age FROM Person;
It also works in MySQL.
SELECT Name, IF (Age < 21, 'child', 'adult') AS Age FROM Person;
This is similar to the ternary operator in C-style languages.
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.
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.
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.
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.
SELECT CASE WHEN (SELECT COUNT(1) FROM Person) < 100 THEN 'Table is small' ELSE 'Table is big' END AS result;
Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.
SEE EPISODESConsidered “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.
Here’s a quick look at how Sentry handles your personal information (PII).
×We collect PII about people browsing our website, users of the Sentry service, prospective customers, and people who otherwise interact with us.
What if my PII is included in data sent to Sentry by a Sentry customer (e.g., someone using Sentry to monitor their app)? In this case you have to contact the Sentry customer (e.g., the maker of the app). We do not control the data that is sent to us through the Sentry service for the purposes of application monitoring.
Am I included?We may disclose your PII to the following type of recipients:
You may have the following rights related to your PII:
If you have any questions or concerns about your privacy at Sentry, please email us at compliance@sentry.io.
If you are a California resident, see our Supplemental notice.