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 ;
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:
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 :
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.
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!!