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.

MySQL IF

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.

MySQL IF
Related Tutorial
Follow Us
https://www.facebook.com/Rookie-Nerd-638990322793530 https://twitter.com/RookieNerdTutor https://plus.google.com/b/117136517396468545840 #
Contents +