EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial IF Statement in MySQL

IF Statement in MySQL

By Priya PedamkarPriya Pedamkar

IF Statement in MySQL

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.]
ENDIF
Note: The above syntax shows that the statements ELSEIF and ELSE will execute only if the condition in the IF statement is false, as indicated by the square brackets enclosing these statements.

Flow Diagram of IF-ELSEIF-ELSE in MySQL

Here is the flow diagram for the IF statement in MySQL:

Diagram of IF-ELSEIF-ELSE 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:

IF Statement in MySQL Example 1

Instance 2

SELECT MAXI (4, 6);

Output:

Instance 2 Example 1

Instance 3

SELECT MAXI (7, 7);

Output:

Instance 3 Example 1

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:

Product Type Example 2

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//
DELIMITER

Going 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:

IF Statement in MySQL Example 2

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.

  1. Introduction to MySQL Operators
  2. Top Differences – Jira vs Github
  3. Top 23 MySQL String functions
  4. MySQL vs SQLite | Top 14 Comparisons
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more