MySQL IF() Condition

In this tutorial, we will study the MySQL IF() condition. Decision making is a process that you come across in real life. Decision making has many applications and use cases in the computing world. Suppose you have a table of student marks and you need to print a pass/fail grade for them. If a student has more than 40 marks, they pass, else they fail. This is where we use the MySQL IF() function. 

The IF() function is used to return a value if a condition is true and another value if the same condition is false. Let us take a look at the syntax and a few examples of this very important function.

Syntax of MySQL IF()

IF(condition, value1, value2)Code language: SQL (Structured Query Language) (sql)

Where,

• ‘condition’ is the value/condition to be tested,
• ‘value1’ is the value that should be returned if ‘condition’ is true and,
• ‘value2’ is the value that should be returned if ‘condition’ is false.

Examples of MySQL IF()

Let us kick things off with a couple of basic examples. We will use the SELECT statement and aliases. Suppose you have the condition ‘5>2’.

If this condition is true, we should display “Yes!”, otherwise we should display “No.” Below is the query for this.

SELECT IF(5>2, "Yes!", "No.") AS 'Is 5 greater than 2?';Code language: SQL (Structured Query Language) (sql)

And the output is –

You can also have numbers instead of strings as the ‘value1’ and ‘value2’ parameter. Here is an example where we check if 250 is greater than or equal to 250. If that condition is true, we display 250 otherwise we display 251.

SELECT IF(250<=250, 250, 251);Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL IF() with Multiple Conditions

Suppose you have the following problem –

Check if 5 is greater than 2 and 2 is greater than 1. If both conditions are true, display “Yes!”, else display “No.”

Now we have two conditions here. To join two conditions, we can use the keywords AND and OR. In this case, since both conditions need to be met, we will use the AND operator as follows:

SELECT IF(5>2 AND 2>1, "Yes!", "No.") AS 'Is 5>2 and 2>1?';Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL IF() With Functions as Conditions

Let us take our examples a step ahead now. How about having MySQL functions as conditions? We can have mathematical, string and date functions in our ‘condition’ parameter.

Let us take a look at the below example where we check if the tangent of an angle (rounded off to 4 decimal places) is equal to the sine of that angle divided by the cosine of that angle (result rounded to 4 decimal places). If the condition is true, display “Yes!” else display “No!”. We will use the ROUND(), TAN(), SIN() and COS() functions. We will assume the value of the angle as 30. The query is –

SELECT IF(ROUND(TAN(30), 4) = ROUND(SIN(30)/COS(30), 4), "Yes!", "No!") AS 'TAN = SIN/COS';Code language: SQL (Structured Query Language) (sql)

And the output is –

Similarly, let us see an example with string functions. Let us use STRCMP() to compare two strings – “Jessica” and “Radha”. If the strings are equal, display “Yes”, else display “No”. The query is –

SELECT IF(STRCMP("Jessica", "Radha") = 0, "Yes", "No");Code language: SQL (Structured Query Language) (sql)

The output is –

Similarly, let us see an example using date functions. If the current date is ‘2021-04-23’, display ‘Yes’ else display ‘No”. We will use the CURDATE() function. The query is –

SELECT IF(CURDATE() = '2021-04-23', 'Yes', 'No') AS Result;Code language: SQL (Structured Query Language) (sql)

And the output is –

Working With Tables

Consider the below ‘Students’ and ‘Marks’ tables.

Let us display the student’s ID, first and last name and his attendance record. If the student is present for more than 80 days, his attendance record is ‘Good’, otherwise it is ‘Poor’. Below is a query for this –

SELECT ID, FirstName, LastName, 
IF(DaysPresent > 80, "Good", "Poor") AS 'Attendance Record' 
FROM Students;Code language: SQL (Structured Query Language) (sql)

And the output is –

Now, let us display the student’s ID, first and last name and his Maths grade. Maths grade is a Pass or Fail grade. A student passes if he scores more than 40 otherwise he fails. Here is a query for this.

SELECT s.ID, s.FirstName, s.LastName, IF(m.Maths>40, "Pass", "Fail") AS 'Maths Grade' 
FROM Students s 
INNER JOIN Marks m 
ON s.ID=m.StudentID;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

Decision making is a very important operation in programming in general. With database management systems, decision making has a plethora of use cases. Therefore, I highly recommend you to practice examples of the IF() function.