Updated May 11, 2023
Introduction to IF Statement in MySQL
The MySQL “IF” statement is a conditional statement used to test one or more conditions or generate a condition-based output. An IF statement must be followed by an ELSEIF statement, which can be further followed by an ELSE statement. The IF statement works sequentially; if a condition tests positive on IF itself, subsequent ELSEIF won’t execute, and if a condition on ELSEIF turns out to be TRUE, then subsequent ELSE will not run. The IF statement is a type of control-flow statement. In the context of the query language, the control-flow statements assume significance as they allow checking conditions for an important metric.
Syntax:
The syntax of the 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.]
ENDIFFlow Diagram of IF-ELSEIF-ELSE in MySQL
Here is the flow diagram for the IF statement in MySQL:
How Does IF Statement Works in MySQL?
The IF statement evaluates a condition to determine if it is true. If the condition is true, a block of statements is executed. If the condition is false, the code moves on to the ELSEIF statement where another condition is evaluated. If this condition is satisfied, the block of statements under the ELSEIF statement is executed. otherwise, the statement of blocks under ELSE executes.
Please note that MySQL does not allow an empty block of statements.
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 use the IF statement to generate the correct output. Below is the code to implement the function.
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 condition and obtained the results 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 aspects associated with code: syntax and logic. We explore logic, which is essential though simple. We pass two integer values into the function. Note the function returns a string, so we used VARCHAR. Inside the function, we compare the variables, and based on the condition, we assign appropriate statements to the character variable ‘s’. After checking the conditions, the IF statement concatenates the variables and the statement assigned to the variable “s”.
We validated the result with three sample inputs, shown above, and the screenshots have established the results obtained after the SELECT codes. We have to run the function with the SELECT statement to execute it.
Example #2
The example which we are going to see now demonstrates an out-of-the-box approach. We have a product table containing various details like price, vendor, product types, and product categories for multiple vehicles, viz. Classic Cars, Motorcycles, Planes, Ships, Trucks and Busses, and Vintage Cars. We intend to categorize the products based on price. To categorize the products, you should use the following criteria: classify products with a price greater than 100 as “high range,” categorize products with a price greater than 50 but less than or equal to 100 as “mid range,” and classify products with a price less than 50 as “low range.”
We don’t have the 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 been added properly. The following screenshot shows this.
Output:
The above code shows that the column “prod_type” has been successfully added to the table.
Now, we intend to have values in this column based on the values of the buyprice. The following code considers the categorization 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//
DELIMITERGoing through the code, we created a function that takes a decimal variable as a parameter and returns a character result. The IF code enables us to assign a suitable string value to the string variable based on the parameter’s value.
The following UPDATE statement uses the product_category() function we defined above.
UPDATE products
SET prod_type = product_category (buyPrice);The above code will assign an 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 on how conditional-flow statements like IF can be employed in MySQL. Implementing IF statements in MySQL is not as easy as in any programming language such as C, C++, Java, VBA, etc. This is owing to the query-processing nature of MySQL.
Recommended Articles
We hope that this EDUCBA information on “IF Statement in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
