Introduction to IF Statement in MySQL
The “IF” statement in MySQL is a conditional statement that is used to test a condition(s) or generate a condition-based output. An IF statement is followed by only ELSEIF which is further followed by ELSE statement. The IF statement works sequentially, like, if a condition tests positive on IF itself then subsequent ELSEIF won’t execute, and if condition on ELSEIF turns out to be TRUE then subsequent ELSE will not execute. The IF statement is a type of control-flow statements. In the context of the query language, the control-flow statements assume significance as they allow checking condition for an important metric.
Syntax:
The syntax of IF-ELSEIF-ELSE statement in MySQL is as follows.
IF <search_condition_1> THEN
Block of statement(s) that will execute when search_condition_1 is TRUE.
[ELSEIF <search_condition_2> THEN
Block of statement(s) that will execute when the search_condition_2 is TRUE.]
[ELSE
Block of statement(s) that will execute when no condition is met.]
ENDIF
Flow Diagram of IF-ELSEIF-ELSE in MySQL
The flow diagram for IF statement in MySQL is as shown below.
How IF Statement Works in MySQL?
When a condition is passed in the IF statement then it evaluates if the condition is TRUE. If found TRUE then a block of statements are executed. If the condition evaluates to FALSE then it passes to ELSEIF, where evaluation for another condition happens. If satisfied, the statement of blocks under ELSEIF gets executed otherwise the statement of blocks under ELSE executes.
It must be noted that the block of statements cannot be empty as not permitted by MySQL.
4.5 (4,700 ratings)
View Course
Example #1
In this example, we will find the maximum of two values. For this, we will create a simple function MAXI in MySQL. The function will make use of the IF statement to generate the correct output. The code for implementing the function has been shown below.
Code:
DELIMITER //
CREATE FUNCTION MAXI(x INT, y INT)
RETURNS VARCHAR (20)
BEGIN
DECLARE s VARCHAR (20);
IF x > y THEN SET s = 'is greater than';
ELSEIF x = y THEN SET s = 'is equal to';
ELSE SET s = 'is less than';
END IF;
SET s = CONCAT(x, ' ', s, ' ', y);
RETURN s;
END //
DELIMITER;
We evaluated the function over some random values to check each of the conditions and obtained the results as shown below.
Instance 1
SELECT MAXI (13, 5);
Output:
Instance 2
SELECT MAXI (4, 6);
Output:
Instance 3
SELECT MAXI (7, 7);
Output:
As we can find that the code gave us the right results. Let’s see how the code works. There are two important dimensions associated with the code, first the syntax, and second is the logic. We explore the logic which is important though simple. We pass two integer values into the function. Note, the function returns string and so we used VARCHAR. Inside the function, we compare the variables, and based on the condition we assign appropriate statement to the character variable ‘s’. The conditions are checked using the IF statement. Finally, when the IF statement gets over, we concatenate variables and the statement assigned to the variable s.
We validated the result with three sample inputs, which have been shown above, and the result so obtained have been shown by the screenshots subsequent to the SELECT codes. In order to execute the function, we have to execute it with the SELECT statement.
Example #2
The example which we are going to see now demonstrates an out-of-the-box approach. We have a products table that contains various details like price, vendor, product types, product categories for various types of vehicles viz. Classic Cars, Motorcycles, Planes, Ships, Trucks and Busses, and Vintage Cars. We intend to categorize the products based on price. The categorization has to be as: Products with a price of greater than 100 would be categorized as ‘high range’, those having price greater than 50 but less than or equal to 100 would be categorized as ‘mid range’, and lastly, those with the price of less than 50 will be categorized as ‘low range’.
We don’t have Product Type as a column in the table. And so, first, we need to add it to the table. This is as demonstrated below.
ALTER TABLE products
ADD prod_type VARCHAR(20)
Let’s load the products table to check if the column has got added properly or not. The following screenshot shows this.
Output:
As we can see above, the column prod_type has successfully got added to the table.
Now, we intend to have values in this column based on the values of the buyprice. The following code takes into consideration the categorization that we discussed earlier and employs the IF statement to categorize the product based on the buy price. Have a closer look at the code.
Code:
DELIMITER //
CREATE FUNCTION product_category(p DECIMAL)
RETURNS VARCHAR (20)
BEGIN
DECLARE product_typeVARCHAR (20);
IF p > 100 then set product_type = 'high range';
ELSEIF p > 50 then set product_type = 'mid range';
ELSE set product_type = 'low range';
END IF;
RETURN product_type;
END//
DELIMITER
Going through the code, we find that we created a function that takes a decimal variable as a parameter and essentially returns a character result. The IF code enables us to assigns suitable string value to the string variable based on the value of the parameter.
The following UPDATE statement makes use of product_category() function that we defined above.
UPDATE products
SET prod_type = product_category (buyPrice);
The above code will assign appropriate value to each of the entries of the prod_type column based on the value of buyPrice. Here, we didn’t use the WHERE clause, instead, we made use of the IF statement through the use of the function. We thus employed an out-of-the-box approach to accomplish the task.
The following screenshot shows how the above code has successfully updated the table.
Output:
Conclusion
There’s no limitation as to how conditional-flow statements like IF can be employed in MySQL. The implementation of IF statement in MySQL is not as easy as it is in any programming language such as C, C++, Java, VBA, etc. This is owing to the query processing nature of MySQL.
Recommended Articles
This is a guide to IF Statement in MySQL. Here we discuss the Introduction to IF Statement in MySQL and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
