MySQL IF() function

IF() function is one of the most interesting functions in MySQL as it helps a lot while implementing a complex logic in a simple query.

IF FUNCTION takes three parameter

IF(CONDITION STATEMENT, STATEMENT FOR TRUE CASE, STATEMENT FOR FALSE CASE)

We use IF function mostly to generate calculated column which is used to show the result in the more meaningful way.

For Example:

You want to generate a report of the total order placed in a month and you want to submit this report to your Manager, who don’t have knowledge of technical term. So, in that case, you will need IF Function.

Here CONDITION STATEMENT is a statement which will return TRUE or FALSE on the basis of which, will run the STATEMENT FOR TRUE CASE or STATEMENT FOR FALSE CASE and return a value.

For Example:

CONDITION STATEMENT  if return TRUE

then STATEMENT FOR TRUE CASE will execute

select IF(true,"TRUE CASE", "FALSE CASE");

MySQL - IF FUNCTION

 

 

CONDITION STATEMENT  if return FALSE

then STATEMENT FOR FALSE CASE will execute

select IF(false,"TRUE CASE", "FALSE CASE");

MySQL - IF FUNCTION false case

 

 

We can also implement nested IF() in MySQL i.e. IF() within IF()

Nested if in case of a TRUE case:

IF(CONDITION STATEMENT, IF(CONDITION STATEMENT, STATEMENT FOR TRUE CASE, STATEMENT FOR FALSE CASE), STATEMENT FOR FALSE CASE)

For Example:

select IF(true,IF(true,"NESTED TRUE CASE", "NESTED  FALSE CASE"), "FALSE CASE");

Nested if in case of a FALSE case:

IF(CONDITION STATEMENT, STATEMENT FOR TRUE CASE, IF(CONDITION STATEMENT, STATEMENT FOR TRUE CASE, STATEMENT FOR FALSE CASE))

For Example:

select IF(true,"TRUE CASE", IF(true,"NESTED TRUE CASE", "NESTED  FALSE CASE"));

 

IF() with SELECT Statement

Inside IF function we are also able to use select statement but it should return single value instead multiple rows or columns

select *, if(column_three > 2 ,(select count(1) from table_one where column_three > 2), "Column_one is smaller than 2") from table_one

MySQL - IF Function with select

We can implement SELECT statement with nested IF() if you need.

IF() can be used in simple MySQL Query or in PL/SQL

 

 

MySQL: If and Else

In this article, we will talk about IF and Else control structure which will work inside the procedure and how an if function, which will even work in the simple query too,  explained below.

We can use If and else control structure in two ways in MySQL.

1. IF and ELSE control structure

If and Else control structure works only inside of a procedure. So if you are creating a procedure and you want to use if and else control structure in your stored procedure.

Syntax:  To use if and else

IF syntax :

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list] END IF

Example: To use If only

MySQL -> BEGIN
-> IF (userId <>”) && (userId <> 0) then
-> SELECT * FROM user_table where user_id  = userId;
-> END ;
-> END
In this case, this procedure will run the select statement only if userId is not equal to  empty string(“”) and 0

 Example: To use If, Else If and else, altogether

1
2
3
4
5
6
7
8
9
 BEGIN
IF (userType = "emp") then
select * from emp_table where user_id = userId;
ELSE IF (userType = "Admin")
select * from admin_table where user_id = userId;
ELSE
select * from user_table where user_id = userId;
END IF;
END

In this case, we are first checking the type of userType by comparing it with different values like emp and admin then and only we are providing the data from the table on the basis of userId.

2. IF Function in MySQL

Here we have one more important concept regarding If is the use of If Function. If Function can be used in a simple SQL query or inside a procedure. 

It is one of the most useful functions in MySQL, It will very useful when you want if and else like condition in the query like :

Let’s assume a table having a column name status which is having values in the form of ‘Y’ and ‘N’.

So if we run the query mentioned-below:

1
select status from emp_table;

it will return Y and N regarding all the user present in the table. but it will be not clear to some other people if they don’t know what status field store and how it is related to the user.

Now, if we write :

1
 select if(status = 'Y', 'active'  , 'not active') from emp_table;

Now it will return ‘active’  if status value is ‘Y’ and ‘not active’ if status value is ‘N’.

You can even use nested if function inside the query.

1
select if(status = 'Y', 'active'  , if(status = 'N','not active',"may be not active")) from emp_table;

Not only in selecting, you can use If Function in where clause also. For other details related to If Function click here

I hope you will now understand, how to use if and else control structure in MySQL. Please write a comment. It will help us to improve this article. Thanks!!