How to Execute an IF…THEN Logic in an SQL SELECT Statement

1. Overview

Whether working with a simple or complex database, we can use different logical conditions in SQL to enhance our data manipulation capabilities. For example, we can use IF-THEN logic in an SQL SELECT statement to perform various tasks on data based on specific conditions.

However, there is no direct way to use IF-THEN logic in SQL because there is no IF keyword in SQL. We can use either a CASE statement or an IIF() function to implement IF-THEN logic in SQL.

In this tutorial, we’ll explore how to implement IF-THEN logic in SQL across various dialects such as SQL Server, MySQL, and PostgreSQL.

2. Using CASE

The CASE statement acts as a logical IF-THEN-ELSE conditional statement. We can use it to perform conditional branching within the SELECT statement across various SQL databases, including SQL Server, MySQL, and PostgreSQL.

Within SQL SELECT, we can use the WHEN-ELSE statement instead of the traditional IF-ELSE. It evaluates a condition and returns a specific value depending on the outcome.

2.1. Syntax

Let’s look at the syntax for a CASE statement:

SELECT column1, column2,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result_default
END AS alias_name
FROM table_name;

Here, we use a SELECT and FROM query to select multiple columns from a table and use a CASE statement to evaluate conditions.

2.2. Example Query

Let’s assume an example in which we want to assign remarks to students’ results based on their grades. To do this, let’s consider the Exam table of our University database. We’ll assign remarks to the student_id and course_id columns based on their grades and put them into a new grade_remarks column.

Let’s assign descriptive remarks to students’ grades in an EXAM table:

SELECT student_id, course_id,
CASE
WHEN grade = 'A+' THEN 'SUPER'
WHEN grade = 'A' THEN 'EXCELLENT'
WHEN grade = 'B+' THEN 'GOOD'
WHEN grade = 'B' THEN 'SATISFACTORY'
WHEN grade = 'C' THEN 'NEEDS IMPROVEMENT'
ELSE 'POOR'
END AS grade_remarks
FROM EXAM;

Let’s view the output of the above query:

Firstly, we select the student_id and course_id columns from the Exam table. Then, we use CASE with WHEN and THEN to input remarks based on the grade column. For example, if the grade column has the value A+, then the remarks in the grade_remarks column will be Super.

3. Using IIF()

We can also use the IIF() function to perform IF-THEN logic in SQL and MySQL with slight syntax modification. However, we can’t use this function in PostgreSQL as it isn’t universally supported like CASE.

Furthermore, the IIF() function within an SQL SELECT statement returns one of two values based on the evaluation of the expression.

3.1. Syntax

Let’s look at the syntax for the IIF() function in SQL:

SELECT column1,
IIF(condition, true_result, false_result) AS alias_name
FROM table_name;

We can’t use the IIF() function directly in MySQL, but we can use the IF() function to achieve similar functionality. Let’s check out its syntax:

SELECT column1,
IF(condition, true_result, false_result) AS alias_name
FROM table_name;

Additionally, PostgreSQL doesn’t support an IIF() or IF() function.

3.2. Example Query

Let’s repeat the example from the previous section, but this time, we’ll use the IIF() function instead of the CASE expression.

Let’s evaluate the grade column in SQL using IIF():

SELECT student_id, course_id,
IIF(grade = 'A+', 'SUPER',
IIF(grade = 'A', 'EXCELLENT',
IIF(grade = 'B+', 'GOOD',
IIF(grade = 'B', 'SATISFACTORY',
IIF(grade = 'C', 'NEEDS IMPROVEMENT', 'POOR'))))
) AS grade_remarks
FROM EXAM;

In the above query, we use nested IIF() functions multiple times to handle different grade values. Furthermore, let’s use the IF() function instead of IIF() for MySQL:

SELECT student_id, course_id,
IF(grade = 'A+', 'SUPER',
IF(grade = 'A', 'EXCELLENT',
IF(grade = 'B+', 'GOOD',
IF(grade = 'B', 'SATISFACTORY',
IF(grade = 'C', 'NEEDS IMPROVEMENT', 'POOR'))))
) AS grade_remarks
FROM EXAM;

The above query uses multiple IF() functions to evaluate the grade column and return a corresponding remark.

4. Using CHOOSE()

We can also use the CHOOSE() function within SQL SELECT to make decisions. It works like a shortcut for conditional statements that allows us to pick from a list of options based on a position or index. Additionally, we can use CHOOSE() with CASE to create a simple IF-THEN logic inside a SELECT query.

4.1. Syntax

Let’s see how to use CHOOSE():

SELECT column1,
CHOOSE(CASE grade WHEN condition1 THEN 1 ELSE 2 END, 'true_result', 'false_result') AS alias_name
FROM table_name;

In the above query, we use the CHOOSE() function to choose a value from a list based on a position, while the position is determined by a CASE statement that evaluates the value in the grade column.

4.2. Example Query

Let’s try the example from the previous section again, but this time we’ll use the CHOOSE() function instead of the IIF() or CASE expression. However, CASE expressions are indirectly needed inside the CHOOSE() function to perform the operations completely.

Let’s perform IF-THEN logic in SQL:

SELECT student_id, course_id,
CHOOSE(
CASE grade
WHEN 'A+' THEN 1
WHEN 'A' THEN 2
WHEN 'B+' THEN 3
WHEN 'B' THEN 4
WHEN 'C' THEN 5
ELSE 6
END,
'SUPER', 'EXCELLENT', 'GOOD', 'SATISFACTORY', 'NEEDS IMPROVEMENT', 'POOR'
) AS grade_remarks
FROM EXAM;

In this query, we create a computed grade_remarks column to map the grade to their descriptive remark, such as A+ becomes SUPER, and B+ becomes GOOD.

Additionally, to achieve the same result in MySQL, we can use the ELT() function:

SELECT student_id, course_id,
ELT(
CASE grade
WHEN 'A+' THEN 1
WHEN 'A' THEN 2
WHEN 'B+' THEN 3
WHEN 'B' THEN 4
WHEN 'C' THEN 5
ELSE 6
END,
'SUPER', 'EXCELLENT', 'GOOD', 'SATISFACTORY', 'NEEDS IMPROVEMENT', 'POOR'
) AS grade_remarks
FROM EXAM;

Moreover, we can’t perform IF-THEN logic in PostgreSQL with the CHOOSE or ELT() function.

5. Conclusion

In this article, we explored different options for executing IF-THEN logic in a SQL SELECT statement. These options include using CASE, IIF(), and CHOOSE().

Furthermore, if we want a query that’s used in almost every dialect, we must choose the CASE statement. We can also use the IIF(), IF(), or CHOOSE() function if we need a quick implementation of IF-THEN logic within SELECT.