Know Your MYSQL: IF and ELSE

We can IF and ELSE control structure in two ways in MYSQL.

If you are creating a procedure and you want to use IF and ELSE control structure in your stored procedure.

IF syntax :

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

For example :

MYSQL > BEGIN

> IF (userId <> ”) && (userId <> 0) then

> SELECT * FROM user_table where user_id  = userId;

> END IF;

> END

In this case this procedure will run the select statement only if userId is not equal to ” and 0

Next example :

MYSQL > 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 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.

There is one IF function in mySQL, It will very useful when you want if and else function in the query like:

mySQL  > 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 user.

Now, if we write :

mySQL > 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’.