MySQL IF()

In this section, we are going to learn how IF() function works in MySQL. The IF function is one of the parts of the MySQL control flow function, which returns a value based on the given conditions. In other words, the IF function is used for validating a function in MySQL. The IF function returns a value YES when the given condition evaluates to true and returns a NO value when the condition evaluates to false. It returns values either in a string or numeric form depending upon the context in which this function is used. Sometimes, this function is known as IF-ELSE and IF THAN ELSE function.

The IF function takes three expressions, where the first expression will be evaluated. If the first expression evaluates to true, not null, and not zero, it returns the second expression. If the result is false, it returns the third expression.

Syntax

snippet

IF ( expression 1, expression 2, expression 3)

Parameter

Parameter	Requirement	Descriptions
Expression 1	Required	It is a value, which is used for validation.
Expression 2	Optional	It returns a value when the condition evaluates to true.
Expression 3	Optional	It returns a value when the condition evaluates to false.

Default Return Type

The return type of IF function can be calculated as follows:

If expression 2 or expression 3 are both strings or produce a string, the result is always a string.
If expression 2 or expression 3 gives a floating-point value, the result is always a floating-point value.
If expression 2 or expression 3 is an integer, the result is always an integer.

MySQL version support

The IF function can support the following MySQL versions:

MySQL 8.0
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

Note

Note: The IF function is different from the IF statement. So do not confuse in IF function and IF statement.

Let us understand the MySQL IF function with the following examples. We can use the IF function with the SELECT statement directly.

Example 1

snippet

SELECT IF(200>350,'YES','NO');

In the above function, the (200>350) is a condition, which is evaluated. If the condition is true, it returns a value, YES, and if the condition is false, it returns NO.

Output:

snippet

NO

Example 2

snippet

SELECT IF(251 = 251,' Correct','Wrong');

In the above function, the (251 = 251) is a condition, which is evaluated. If the condition is true, it returns value Correct, and if the condition is false, it returns Wrong output.

Output:

snippet

Correct

Example 3

snippet

SELECT IF(STRCMP('Rinky Ponting','Yuvraj Singh')=0, 'Correct', 'Wrong');

The above example compares the two strings. If both the string is the same, it returns Correct. Otherwise, the IF function returns Wrong output.

Output:

snippet

Wrong

Example 4

Here, we are going to create a table 'student' and perform the IF function.

Now, run the following MySQL query. This statement returns the last name of the student table, in which, if the age is greater than 20, it returns Mature. Otherwise, the IF function returns Immature.

snippet

SELECT lastname,
IF(age>20,"Mature","Immature")  
As Result
FROM student;

Output:

When the above MySQL statement runs successfully, it will give the following output.