The MySQL IF() function explained

Learn how to create conditional MySQL query with IF function

Posted on September 16, 2021


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_evaluate is the value or expression you want to evaluate
  • if_true is the value returned when the evaluation returns true
  • if_false is the value returned when the evaluation returns false

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.

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.