MySQL: IF Function

This tutorial explains how to use the MySQL IF function with syntax and examples.

Description

The MySQL IF function returns one value if a condition evaluates to TRUE, or another value if it evaluates to FALSE.

Syntax

The syntax for the IF function in MySQL is:

IF( condition, [value_if_true], [value_if_false] )

Parameters or Arguments

condition

The value that you want to test.

value_if_true

Optional. It is the value that is returned if condition evaluates to TRUE.

value_if_false

Optional. It is the value that is return if condition evaluates to FALSE.

Note

• The MySQL IF function can return either a string or a numeric value, depending on the context of how it is used.

Applies To

The IF function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.3

Example

Let's look at some MySQL IF function examples and explore how to use the IF function in MySQL.

MySQL IF Function - Returns String Value

This first IF function example shows how you would return a string value.

For example:

mysql> SELECT IF(100200, 'yes', 'no');
Output: 'yes'

In this IF function example, the condition is 100200. If this condition is TRUE, the IF function will return 'yes'. Otherwise, the IF function will return 'no'.

The first IF function example uses a numeric condition. However, you can also use the IF function with a string condition.

For example:

mysql> SELECT IF(STRCMP('AODBA.com','mysite.com')=0, 'yes', 'no');
Output: 'no'

In this IF function example, the condition uses the STRCMP function to compare 2 strings: STRCMP('AODBA.com','mysite.com').

If the string 'AODBA.com' is the same as the string 'mysite.com', the IF function will return 'yes'. Otherwise, the IF function will return 'no'.

MySQL IF Function - Returns Numeric Value

This next IF function example shows how you would return a numeric value.

For example:

mysql> SELECT IF(100200, 5000, 6000);
Output: 5000

In this IF function example, the condition is 100200. If this condition is TRUE, the IF function will return the numeric value 5000. Otherwise, if the condition is FALSE, the IF function will return 6000.

MySQL IF Function - Condition includes Field

Let's look at an example that uses the IF function to test the value of a field in a table.

For example:

mysql> SELECT supplier_id, supplier_name, quantity, IF(quantity>10, 'More', 'Less')
FROM suppliers;

In this IF function example, the IF function tests the value of the quantity field in the suppliers table. The IF function will evaluate the condition, quantity>10, for each row in our result set.

So (for each row) if quantity>10, the IF function will return 'More'. Otherwise, the IF function will return 'Less'.