Introduction

This tutorial will show you the concept of IF Function in MySQL with various examples. This detailed article will cover the following topics:
  1. Introduction
  2. IF Function in MySQL
  3. MySQL IF Function Examples
  4. Conclusion
First, let's create a database with a few tables containing some dummy data. Here, I am providing a database with the tables containing the records, where I am showing you the various examples.
  1. CREATE DATABASE MySQL_IFFunction;
  3. USE MySQL_IFFunction;
  5. CREATE TABLE StudentDetails (
  6. StudentNumber INT NOT NULL,
  7. StudentName VARCHAR(50) NOT NULL,
  8. contactLastName VARCHAR(50) NOT NULL,
  9. contactFirstName VARCHAR(50) NOT NULL,
  10. contactnumber VARCHAR(50),
  11. addressLine1 VARCHAR(250) NOT NULL,
  12. addressLine2 VARCHAR(250) DEFAULT NULL,
  13. city VARCHAR(50) NOT NULL,
  14. state VARCHAR(50) DEFAULT NULL,
  15. postalCode VARCHAR(15) DEFAULT NULL,
  16. country VARCHAR(50) NOT NULL,
  17. PRIMARY KEY (StudentNumber)
  18. );
  20. CREATE TABLE Bookorder (
  21. BookNumber INT NOT NULL,
  22. orderDate datetime NOT NULL,
  23. shippedDate DATE DEFAULT NULL,
  24. Status VARCHAR(50) NOT NULL,
  25. OrderAcknowledgeDate datetime,
  26. PRIMARY KEY (BookNumber)
  27. );
  29. CREATE TABLE Books (
  30. BookID INT PRIMARY KEY auto_increment,
  31. BookName varchar(100) NOT NULL,
  32. BookType varchar(100) NOT NULL,
  33. BookVendor varchar(100) NOT NULL,
  34. BookDescription varchar(250),
  35. BookInStock int NOT NULL
  36. );
By using the following query, let's check the following tables.
1) To get data from the "StudentDatails" table, use the following query:
  1. SELECT * FROM mysql_iffunction.studentdetails;
StudentDatails Table
2) To get data from the "Bookorder" table, use the following query:
  1. SELECT * FROM mysql_iffunction.bookorder;
Bookorder Table
3) To get data from the "Book" table, use the following query:
  1. SELECT * FROM mysql_iffunction.books;
Book Table

IF Function In MySQL

The MySQL IF Function is defined as a control flow function that returns a value based on a given expression or condition. If the given expression is true, then it will return the "condition_true_expression" value, otherwise, it will return the "condition_false_expression" value.
Syntax
IF (expression, condition_true_expression, condition_false_expression);
Note:
In MySQL, the MySQL IF Function is different from the IF Statement.

MySQL IF Function Examples

Using the examples below, let's take a look at how the MySQL IF function works.

1) Simple Examples

a) In this example, the "expression" is true. Therefore, the MySQL IF Function will return the "condition_true_expression" as a result.
Example
  1. SELECT IF(10*2=20,'TRUE','FALSE') AS IF_Result;
example 1
b) Here, the "expression/condition" is false. Therefore, MySQL IF Function will return the "condition_false_expression" as a result.
Example
  1. SELECT IF(100*20=20000,'TRUE','FALSE') AS IF_Result;
example 2

2) MySQL IF Function With String Functions

Here, I am going to show you some examples of MySQL IF Function using String Functions.
Example 1
  1. SELECT IF(LENGTH("hello! vatsa") > 10, "StringAccept", "StringReject") AS IF_Function_Result;
String Functions Example 1
Example 2
  1. SELECT IF(ASCII('A')=65, "ASCIIAccept", "ASCIIReject") AS IF_Function_Result;
String Functions Example 2

3) MySQL IF Function With Aggregate Functions

Here, I am going to show you some examples of MySQL IF Function using Aggregate Functions.
Example 1
  1. SELECT SUM(IF(status = 'Done', 1, 0)) AS OrderCompleted,
  2. SUM(IF(status = 'In progress', 1, 0)) AS OrderInProgress
  3. FROM bookorder;
Aggregate Functions Example 1
Example 2
  1. SELECT COUNT(IF(status = 'Done', 1, NULL)) AS Done,
  2. COUNT(IF(status = 'In progress', 1, NULL)) AS InProgress
  3. FROM bookorder;
Aggregate Functions Example 2

4) Some Complex Examples

Here, I am going to show you some examples of the MySQL IF Function using the given database.
Example 1
  1. SELECT StudentNumber, StudentName,
  2. concat(contactlastName, ' ', contactFirstName) AS StudentNickName,
  3. IF(contactnumber IS NULL, 'Not Available', contactnumber) AS ContactNumber, country
  4. FROM studentdetails;
database Example 1
Example 2
  1. SELECT BookID, BookName, BookType,
  2. IF(BookInStock>=5000, 'Sufficient Stock', 'Need To Increase Stock') AS BookInStock
  3. FROM mysql_iffunction.books;
database Example 2
Reference
https://www.mysqltutorial.org/

Conclusion

In this article, we have discussed the concept of MySQL IF Function in detail with various examples.
To read more about MySQL Functions, visit MySQL Functions.
If you want to know about various important queries of MySQL, visit Important Queries In MySQL.
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
Thanks for reading.