Incorporating IF Statements Within SQL WHERE Clauses

1. Overview

IF or IF-ELSE statements let us run code blocks based on certain conditions. We can use these statements to compare values, filter records, and manipulate data based on specific requirements. Sometimes, we need to use an IF statement as an expression in our SQL clauses like SELECT or WHERE to modify our output based on specific conditions.

Unfortunately, we can’t directly use IF statements within a WHERE clause in SQL, regardless of the database system (MS SQL, MySQL, or PostgreSQL). However, we can achieve similar results using CASE statements, Boolean operators, the IFF() function, and others.

In this tutorial, we’ll demonstrate how we can implement the SQL IF statement within the WHERE clause.

2. WHERE Clause

Let’s make sure we understand the WHERE clause before we start implementing IF statements within WHERE. We can use the WHERE clause to set conditions for selecting or affecting rows with an SQL statement. Also, the WHERE clause is a key part of SQL queries that lets us filter data based on specific criteria.

Additionally, we can use the WHERE clause with SELECT, UPDATE, DELETE, and other statements to decide which records to work with.

2.1. Syntax

Now, let’s view the syntax:

SELECT column1, column2,
FROM table_name
WHERE condition;

In this query, we use a logical expression condition that evaluates to TRUE, FALSE, or UNKNOWN.

Now, let’s explore several methods that can incorporate IF statements within SQL WHERE clauses.

3. Using CASE

We can use the CASE statement to perform conditional logic within a WHERE clause. For example, we can use it to create IF-THEN-ELSE style queries that can be used to create, modify, or execute calculations based on certain criteria.

In addition, we can use this approach across all three SQL dialects, including MySQL, SQL Server, and PostgreSQL.

3.1. Syntax

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

SELECT column1, column2,
FROM table
WHERE CASE
WHEN first_condition THEN value1
WHEN second_condition THEN value2
...
ELSE valueN
END;

Here, we begin a CASE statement with the WHEN clause and end with a THEN, which specifies the outcome if the condition is met. If the first condition isn’t fulfilled, it verifies subsequent conditions until the final one (nth condition).

However, if none of the conditions are met, the optional ELSE clause specifies what result to return. Additionally, if we omit the ELSE and none of the conditions are satisfied, the query result returns NULL.

3.2. Example Query

Now, let’s use a CASE statement within a WHERE clause to filter the result of the Department table of our University Database schema:

SELECT id, name
FROM department
WHERE CASE
WHEN code = 'CS' THEN 1
WHEN code = 'EC' THEN 1
ELSE 0
END = 1;
+----+--------------------------------+
| id | name                           |
|----+--------------------------------|
| 1  | Computer Science               |
| 2  | Electronics and Communications |
+----+--------------------------------+

Here, If the code table value is CS or EC, then the CASE statement evaluates to 1, which means the row is included in the results. Besides, if the code is anything else, the CASE evaluates to 0, which excludes that row from the results.

Additionally, the End=1 will conclude the CASE statement by including only those rows in the result that return 1.

Furthermore, we can also use the combination of WHERE and IN clauses to retrieve the id and name column from the Department table where the code is either CS or EC:

SELECT id, name
FROM department
WHERE code IN ('CS', 'EC');

In this query, we use the IN clause to return TRUE if the column value matches any of the values in the list.

4. Using Boolean Operators

We can use the WHERE clause with the OR/AND operator in SQL to do the same things as IF-ELSE statements do within the WHERE clause. Moreover, this method is universal, and we can implement it in MS SQL, PostgreSQL, and MYSQL dialects.

4.1. Syntax

Let’s view the general syntax of WHERE with OR/AND:

SELECT column1, column2
FROM table
WHERE (1st_condition AND result1)
OR (2nd_condition AND result2)
OR (NOT (1st_condition OR 2nd_condition) AND result3);

We use this structure to specify multiple conditions and their corresponding results, which is an effective way of creating branching logic within an SQL query.

4.2. Example Query

Let’s consider an example where we return all columns of the Exam table of the University database based on their id and grade column value:

SELECT *
FROM exam
WHERE (grade = 'A+' AND student_id = 1001)
OR (grade = 'A' AND student_id = 2009);
+------+----------+------------+-------+-----------+------------+
| id   | semester | exam_date  | grade | course_id | student_id |
|------+----------+------------+-------+-----------+------------|
| 1    | SPRING   | 2022-07-11 | A+    | CS111     | 1001       |
| 3    | SPRING   | 2022-07-11 | A+    | CS122     | 1001       |
| 7    | SPRING   | 2022-07-11 | A+    | CS411     | 1001       |
| 8    | SPRING   | 2022-07-11 | A+    | CS511     | 1001       |
| 3054 | FALL     | 2022-12-20 | A     | ME221     | 2009       |
+------+----------+------------+-------+-----------+------------+

Here, we implement conditional logic directly within an SQL WHERE clause without using explicit IF statements with the help of boolean operators.

5. Using IF() and IIF()

We can also use SQL functions like IF() and IIF() to implement IF or IF-ELSE logic within the WHERE clause. Both functions are similar and can evaluate a condition and return a specific value based on whether the condition is TRUE or FALSE.

Furthermore, we can use the IF() function in MySQL or IIF() function in MS SQL server. However, we can’t use this method in PostgreSQL.

5.1. Syntax

Let’s take a look at the syntax of the IIF() function within the WHERE clause:

SELECT column1, column2
FROM table_name
WHERE IF(condition, true_value, false_value) = 'desired_value';

Moreover, we can provide three arguments to both functions, including the condition to execute, the return value if the condition is TRUE, and the value to return if the condition is FALSE. Furthermore, we can replace the IF() function with IIF() when using it in the MS SQL server.

5.2. Example Query

Let’s use the same example from the previous section, where we retrieve the id and name column value of the Department table based on the specified condition.

Now, let’s implement IF in WHERE using the IIF() function in MS SQL Server:

SELECT id, name
FROM department
WHERE IIF(code = 'CS' OR code = 'EC', 1, 0) = 1;
+----+--------------------------------+
| id | name                           |
|----+--------------------------------|
| 1  | Computer Science               |
| 2  | Electronics and Communications |
+----+--------------------------------+

In this query, we filter the results using the WHERE clause to only include rows where the IIF() function returns 1, which means either CS or EC code.

6. Using CHOOSE() and ELT()

We can also implement conditional logic similar to IF statements within the WHERE clause by using the CHOOSE() or ELT() function. Moreover, we use these functions to choose a value based on an index within a list of options.

Additionally, we can use the ELT() function in MySQL and CHOOSE() in MS SQL. However, we can’t perform this method in PostgreSQL.

6.1. Syntax

Let’s see how to use CHOOSE() to implement IF-type logic in a WHERE clause:

WHERE CHOOSE(
CASE
WHEN 1st_condition THEN 1
WHEN 2nd_condition THEN 2
WHEN 3rd_condition THEN 3
ELSE 4
END,
outcome1, outcome2, outcome3, default_outcome
) = desired_result

Here, we need to use a CASE statement to generate the index, which is a number determining which value to return. Additionally, we use this index as an argument to CHOOSE() to return a value from a list of options.

6.2. Example Query

Let’s use the CHOOSE() function in MS SQL Server to filter Exam table columns based on their grade and student_id value:

SELECT *
FROM exam
WHERE CHOOSE(
CASE
WHEN grade = 'A+' AND student_id = 1001 THEN 1
WHEN grade = 'A' AND student_id = 2009 THEN 1
ELSE 0
END,
1, 0, 0, 0
) = 1;
+------+----------+------------+-------+-----------+------------+
| id   | semester | exam_date  | grade | course_id | student_id |
|------+----------+------------+-------+-----------+------------|
| 1    | SPRING   | 2022-07-11 | A+    | CS111     | 1001       |
| 3    | SPRING   | 2022-07-11 | A+    | CS122     | 1001       |
| 7    | SPRING   | 2022-07-11 | A+    | CS411     | 1001       |
| 8    | SPRING   | 2022-07-11 | A+    | CS511     | 1001       |
| 3054 | FALL     | 2022-12-20 | A     | ME221     | 2009       |
+------+----------+------------+-------+-----------+------------+

In this query, we use the CHOOSE(CASE-END, 1, 0, 0, 0) function to select the second argument 1 when the CASE statement returns 1. For example, if the grade is A+ and student_id is 1001, or if the grade is A and student_id is 2009, it returns 1 (included), otherwise, it returns 0 (excluded).

7. Conclusion

In this article, we discussed various methods for implementing IF or IF-ELSE logic in an SQL WHERE clause. These methods include using CASE, Boolean Operators, IF() or IIF(), and CHOOSE() or ELT().

Moreover, we can use universal CASE statements to handle multiple different conditions with different outcomes. Also, if we want a concise query, then we can use IF() or IIF() functions. We can also use Boolean operators, CHOOSE(), or ELT() functions for incorporating IF statements in WHERE.