The MySQL IF() function allows you to create a conditional query that returns different results based on the expression or value passed to the function.
The function syntax is as shown below:
IF(value_or_expression_to_evaluate, if_true, if_false)
The function accepts 3 parameters:
value_or_expression_to_evaluateis the value or expression you want to evaluateif_trueis the value returned when the evaluation returnstrueif_falseis the value returned when the evaluation returnsfalse
The returned value from the function depends on the evaluation result of the expression or value you passed as the first argument to the function.
Here’s an example of a SELECT statement using the IF() function:
SELECT IF(0, "TRUE", "FALSE"),
IF(1, "TRUE", "FALSE"),
IF("", "TRUE", "FALSE");
The returned result set will be as follows:
+------------------------+------------------------+-------------------------+
| IF(0, "TRUE", "FALSE") | IF(1, "TRUE", "FALSE") | IF("", "TRUE", "FALSE") |
+------------------------+------------------------+-------------------------+
| FALSE | TRUE | FALSE |
+------------------------+------------------------+-------------------------+
1 row in set (0.00 sec)
Any value that evaluates to false will return the third argument, while true values will return the second argument.
Let’s use the function to query MySQL table data. Suppose you have a students table with some NULL values as shown below:
+----+---------+---------+-------+--------+
| id | name | subject | score | gender |
+----+---------+---------+-------+--------+
| 1 | Mark | Math | 7 | male |
| 2 | Natalia | Math | 8 | female |
| 3 | Gary | Math | NULL | male |
| 4 | Joe | English | 8 | male |
| 5 | Sarah | Math | NULL | female |
| 6 | Peter | English | 6 | male |
| 7 | Nathan | English | 8 | male |
+----+---------+---------+-------+--------+
As you can see, the third and the fifth row on the table above has NULL value in the score field for students that didn’t take the test.
You can query the table and replace those NULL values with a string "not available" like this:
SELECT name, subject,
IF(score, score, "not available") AS score, gender
FROM students;
The returned output will be as follows:
+---------+---------+---------------+--------+
| name | subject | score | gender |
+---------+---------+---------------+--------+
| Mark | Math | 7 | male |
| Natalia | Math | 8 | female |
| Gary | Math | not available | male |
| Joe | English | 8 | male |
| Sarah | Math | not available | female |
| Peter | English | 6 | male |
| Nathan | English | 8 | male |
+---------+---------+---------------+--------+
Beside the IF() function, MySQL also has the IF statement that works a bit differently than the function.
You can learn more about the IF statement here.
