Updated June 6, 2023
Introduction to MySQL IF Function
MySQL IF Function is a control function in MySQL that helps us to manage and manipulate decision-making related work on the conditional basis. There might be a situation when you want to execute a certain function or retrieve a certain value from the table based on some condition fulfillment. If that condition fails, you might want to execute some other code or retrieve another value. Here, the MySQL IF function comes to the rescue and can be used to make the execution of MySQL statements on a conditional basis.
Suppose function is one of MySQL’s control flow functions that help us perform a particular operation on the condition’s successful result and some other behavior operation on the failure of the conditional expression. Hence, the function of MySQL is also referred to as the IF THEN ELSE or IF-ELSE function in MySQL.
Syntax and Working of MySQL IF Function
Given below is the syntax of MySQL IF Function:
IF(expression,true_expression,false_expression)An expression can be any condition, statement, or function that returns a boolean value. If this condition evaluates to true, then the specified code or functions will execute in the second parameter; true_expression is executed. If the condition doesn’t evaluate to true, then the function or code you want to execute that is specified in the third parameter, false_expression, is executed. This is the syntax of the if function in MySQL.
Note that the if statement and if the function are two different things in MySQL. Whenever a null or zero value is evaluated from the expression, then the false_expression is executed. The type of the value returned by the if function depends on how we use it and what kind of task we are performing in the if function based on the expression result.
Usage of MySQL IF Function
We can use this if function in query statements to handle NULL values in the columns of the table records and use the if function along with aggregate functions to achieve the desired result sets with the help of expressions of query statements that will ultimately return to a boolean value.
Example of MySQL IF Function
We will now consider one example where we will store the data of a certain number of persons that will contain all the details of persons, like their name and age, and then determine the eligibility to vote for each person.
Let us first create a table of person_records:
Code:
CREATE TABLE 'person_records' (
'id' int(11) PRIMARY KEY,
'firstName' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'age' int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;Output:
Let us insert some records in the table.
Code:
INSERT INTO 'person_records' ('id', 'firstName', 'age') VALUES
(1, 'Payal', '23'),
(2, 'Vyankatesh', '17'),
(3, 'Omprakash', '15'),
(4, 'Parineeta', '36');Output:
Let us now retrieve the name and eligibility of that person and use the if function to do so, as shown in the below query statement.
Code:
SELECT firstName, IF(age<=17,'You are not eligible to vote!','You are eligible to vote!') as eligibility FROM 'person_records';The output of the above functions, when copied and pasted on MySQL terminal command prompt, is as follows.
Output:
Displaying Not Applicable(N/A) instead of null values of columns.
By using the IF function, we can manage NULL values in columns and adjust the displayed value in case NULL is stored in them.
Let us consider a simple example: creating an educba_writers table and inserting null values in a certain column.
Code:
CREATE TABLE 'educba_writers' (
'id' int(11) PRIMARY KEY,
'firstName' varchar(10) COLLATE latin1_danish_ci NOT NULL,
'rate' decimal(5,2) DEFAULT NULL,
'joining_date_time' datetime
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;The output of the above functions, when copied and pasted on MySQL terminal command prompt, is as follows.
Output:
Code:
INSERT INTO 'educba_writers' ('id', 'firstName', 'rate', 'joining_date_time') VALUES
(1, 'Payal', '750.00', '2020-05-28 16:02:34'),
(2, 'Vyankatesh', '700.00', NULL),
(3, 'Omprakash', '600.00', '2020-05-28 20:32:50'),
(4, 'Parineeta', '980.00', NULL);Output:
Let us now retrieve the records.
Code:
SELECT * FROM educba_writers;Output:
Code:
SELECT id, firstName, rate, IF(joining_date_time IS NULL,"N/A",joining_date_time) as joining_date_time FROM educba_writers;Output:
Using the IF function with Aggregate Functions
We can get the required results from the if function and other aggregate functions.
For example, suppose that we consider the writers whose rate of an article with more than 700 as the highly paid writers. Now, if we want to calculate the total count of the highest paid writers, then we can combine the usage of aggregate function SUM and IF function in a single select query statement, as shown below.
Code:
SELECT SUM(IF(rate>700, '1', '0')) as "count of high paid writers" FROM educba_writers;Output:
According to the information given, it seems that Payal and Parineeta are the two writers who earn more than 700 for their articles. Payal’s rate is 750, while Parineeta’s is 980, both of which exceed 700. The table contents reflect this information.
Conclusion
Suppose function is the control function available in MySQL that provides us with the capability to execute and perform specific tasks or define the behavior of working of MySQL statements based on certain conditions. When the condition is true, certain code will be executed. On the other hand, if the condition is not true, another set of code will be carried out. This function is applicable in query statements and can handle NULL values in columns. Let’s collaborate on defining expressions with aggregate functions. Note that the expression should evaluate to a boolean value. You can embed the IF function within SELECT query statements to retrieve results based on specific conditions. The IF function allows you to define expressions using various types of values, including column values, literals, numbers, strings, and booleans.
Recommended Articles
We hope that this EDUCBA information on “MySQL IF Function” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
