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.
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'