EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL IF Function
 

MySQL IF Function

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

Updated June 6, 2023

MySQL IF Function

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

table of person_records:

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:

MySQL IF Function 2

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:

now retrieve name and eligibility of that person

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:

MySQL IF Function 4

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:

MySQL IF Function 5

Let us now retrieve the records.

Code:

SELECT * FROM  educba_writers;

Output:

retrieve the records

Code:

SELECT id, firstName, rate, IF(joining_date_time IS NULL,"N/A",joining_date_time) as joining_date_time  FROM  educba_writers;

Output:

MySQL IF Function 7

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:

with Aggregate

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.

  1. MySQL Workbench
  2. Substring in MySQL
  3. MySQL Dump
  4. MySQL REVOKE

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW