MySQL IF Function

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. 

CREATE DATABASE MySQL_IFFunction;

USE MySQL_IFFunction;

CREATE TABLE StudentDetails (
   StudentNumber INT NOT NULL,
   StudentName VARCHAR(50) NOT NULL,
   contactLastName VARCHAR(50) NOT NULL,
   contactFirstName VARCHAR(50) NOT NULL,
   contactnumber VARCHAR(50),
   addressLine1 VARCHAR(250) NOT NULL,
   addressLine2 VARCHAR(250) DEFAULT NULL,
   city VARCHAR(50) NOT NULL,
   state VARCHAR(50) DEFAULT NULL,
   postalCode VARCHAR(15) DEFAULT NULL,
   country VARCHAR(50) NOT NULL,
   PRIMARY KEY (StudentNumber)
);

CREATE TABLE Bookorder (
   BookNumber INT NOT NULL,
   orderDate datetime NOT NULL,
   shippedDate DATE DEFAULT NULL,
   Status VARCHAR(50) NOT NULL,
   OrderAcknowledgeDate datetime,
   PRIMARY KEY (BookNumber)
);

CREATE TABLE Books (
   BookID INT PRIMARY KEY auto_increment,
   BookName varchar(100) NOT NULL,
   BookType varchar(100) NOT NULL,
   BookVendor varchar(100) NOT NULL,
   BookDescription varchar(250),
   BookInStock int NOT NULL
);

By using the following query, let's check the following tables.

 

1) To get data from the "StudentDatails" table, use the following query:

SELECT * FROM mysql_iffunction.studentdetails;

 

2) To get data from the "Bookorder" table, use the following query:

SELECT * FROM mysql_iffunction.bookorder;

 

3) To get data from the "Book" table, use the following query:

SELECT * FROM mysql_iffunction.books;

 

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

SELECT IF(10*2=20,'TRUE','FALSE') AS IF_Result;

 

b) Here, the "expression/condition" is false. Therefore, MySQL IF Function will return the "condition_false_expression" as a result. 

 

Example

SELECT IF(100*20=20000,'TRUE','FALSE') AS IF_Result;

 

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

SELECT IF(LENGTH("hello! vatsa") > 10, "StringAccept", "StringReject") AS IF_Function_Result;

 

Example 2

SELECT IF(ASCII('A')=65, "ASCIIAccept", "ASCIIReject") AS IF_Function_Result;

 

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

SELECT SUM(IF(status = 'Done', 1, 0)) AS OrderCompleted,
       SUM(IF(status = 'In progress', 1, 0)) AS OrderInProgress
FROM bookorder;

 

Example 2

SELECT COUNT(IF(status = 'Done', 1, NULL)) AS Done,
       COUNT(IF(status = 'In progress', 1, NULL)) AS InProgress
FROM bookorder;

 

4) Some Complex Examples

 

Here, I am going to show you some examples of the MySQL IF Function using the given database.

 

Example 1

SELECT StudentNumber, StudentName,
concat(contactlastName, ' ', contactFirstName) AS StudentNickName,
IF(contactnumber IS NULL, 'Not Available', contactnumber) AS ContactNumber, country
FROM studentdetails;

 

Example 2

SELECT BookID, BookName, BookType,
IF(BookInStock>=5000, 'Sufficient Stock', 'Need To Increase Stock') AS BookInStock
FROM mysql_iffunction.books;

 

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.