MySQL IF() Function

Syntax of MySQL IF() Function

The syntax of the MySQL IF() function is:

Here, the condition is the parameter on the basis of which we will evaluate and filter the result. The value_returned_if_true is a variable used to specify the value to return if the condition evaluates to true, and value_returned_if_false specifies the value to return if the condition evaluates to false. These two value variables are optional.

Parameters of MySQL IF() Function

• Condition:

  This condition variable represents a conditional statement whose result is evaluated.

• value_returned_if_true:

  This variable is an optional variable used to specify the value to return when the condition evaluates to true. It is displayed only if the evaluated condition is true.

• value_returned_if_false:

  This variable is also an optional variable used to specify the value to return when the condition evaluates to false. It only appears if the evaluated condition returns false.

Hiring Partners:

GoogleAmazonMicrosoftFlipkartAdobe1200+ more

Return Value of MySQL IF() Function

The return value of this MySQL IF() function can be a string or a numeric, depending on the values you pass in the value_returned_if_true and value_returned_if_false variables.

How does the MySQL IF() Function work?

The MySQL IF() function works by evaluating the condition specified in the expression passed as the first parameter. If the value of the expression evaluates as "TRUE", then the function returns the second parameter. Otherwise, the function returns the value of the third parameter.

Examples

First, let's look at some examples of simple MySQL IF() functions to better understand their functionality.

1. MySQL IF() function that returns a string value based on the result of a condition evaluated:

   Query:

   Output:

   The output of the above query would be:

   if_return_value
   True, 90 is lesser than 100

   Explanation:

   The above query evaluates the condition (90 < 100) and returns the value of the first variable because the result of this conditional statement is true as 90 is lesser than 100. The return type for this query is a string because we have passed the return value in string format.

2. The MySQL IF() function returns a numeric value based on the result of a condition evaluated:

   Query:

   Output:

   The output of the above query would be:

   if_return_value
   90

   Explanation:

   The return value of the above query will be a number.

3. Executing a MySQL IF() function based on the result of an assignment condition:

   Query:

   Output:

   The output of the above query would be:

   if_return_value
   Equal

   Explanation:

   The above query checks if two values are equal. If equal, the value is returned as "Equal", otherwise as "Different".

4. Executing MySQL's IF() function based on the result of comparing two strings:

   Query:

   Output:

   The output of the above query would be:

   if_return_value
   FALSE

   Explanation:

   The above query uses the result of the STRCMP method and checks the condition by comparing the first two strings provided. The result of this query is "FALSE" because both strings are different.

5. Executing MySQL's IF() function with a condition on a table column:

   You can also use MySQL's IF() function to derive table results by specifying conditions on columns. Suppose you have a table named students with roll_number, name, and marks as fields that store some data. Let's see what the table data looks like:

   Output:

   roll_number	name	marks
   1	Khushi Malhotra	67
   2	Anjali Mehta	98
   3	Sonal Gupta	42
   4	Aashish Garg	99
   5	Naveen Sehghal	78

   Now, the query for IF() function in MySQL evaluating marks column is:

   Query:

   Output:

   roll_number	name	marks	if_return_value
   1	Khushi Malhotra	67	Fail
   2	Anjali Mehta	98	Pass
   3	Sonal Gupta	42	Fail
   4	Aashish Garg	99	Pass
   5	Naveen Sehghal	78	Pass

   Explanation:

   The above query applies the condition on the marks column and will check if the marks of a student are greater than or equal to 70, if yes, then it will return the value as Pass or else as Fail.

6. Executing MySQL's IF() function using SUM() to get the aggregate results from the table:

   You can also use the SUM function with IF() in MySQL to get the number of subject's results. Let's say you have a table named mentors having a name and subject as the field having some data stored in them. Let's see what the table data looks like:

   Output:

   name	subject
   Kunal Rana	Math
   Shruti Sinha	Math
   Gopesh Gupta	History
   Vinay Singh	Math
   Jyoti Pandya	Commerce

   Now, the query for IF() function in MySQL evaluating the subject column is:

   Query:

   Output:

   Math	Other than Math
   3	2

   Explanation:

   The above query will search for subjects named Math. It will return 1 if yes, and 0 if otherwise. The SUM function aggregates the results, summing subjects named Math and non-math subjects as Other than Math.