All programming languages use IF statements to control the flow of the program or to allow the developer to use different sections of code based on a certain criteria. Click on the items in the list below to learn more about using the IF statement.
Mutually Exclusive Conditions
Let's create the most simple form of an IF statement and see how it works within MySQL.
This simple function shows how to use a basic IF statement. First pass in a parameter, declare a varchar variable, then use IF to determine if the parameter passed is equal to A. If it is equal to A the code between the IF and END IF will be called. If it isn't equal to A, the program ignores this code.
There will be different results depending on which parameter is passed. Passing A results in the additional code running and therefore the variable changes. When using B the code is not called and the variable does not change.
For an IF to run, the evaluation condition (the code between IF and THEN) must evaluate to true. This evaluation can be complicated or simple so long as it evaluates to true or false.
All of these examples are valid (so long as l_boolean is a boolean value).
In the first example program it was simply a case of if the parameter is A, run the code. There was no ability to run something else if it wasn't. Two IF statements can be added to handle this:
This would solve the problem but it is not particularly efficient. A better solution is to use ELSE. This allows one thing to be done if the condition is met but a second if it is not met.
By adding the else after the first statement, the program can deal with one or the other condition. But
what if there is more than one condition? In this case, use an ELSEIF.
This time there is an ELSEIF condition between the IF and ELSE. There is a another way to achieve the same functionality and this is the CASE statement .
Mutually Exclusive Conditions
All of the IF conditions should be mutually exclusive. If the value or values being evaluating in the IF and ELSEIF statements could meet more than one of the conditions then they are not mutually exclusive. When processing the IF statement, the program will stop when it meets one of the conditions. If this is the first, it will not even check to see if the rest are met or not. For example:
In this block the parameter being passed meetS both the first and second condition. But when it meets the first, MySQL stops processing the rest of the code until it meets the relevant end if.
There may be situations where a number of conditions need to be check with some related elements. In this case, write a number of ELSEIF statements to meet all of the requirements.
While this is perfectly acceptable to the compiler, a much more elegant solution is to use nested IF statements. Call additional IF statements from inside the original IF statements. So the above example would become:
First check the value of p_comp. Once that is determined, check the value of p_divi. In the example there are only a small number of conditions but if it were a more complex example, valuable processing time would be saved doing it this way. The minimum amount of conditions need to be checked to get to the answer.
Note: The last example didn't include an ELSE statement. An ELSE is not needed if you do not require one. It is perfectly valid to check one, two or many conditions without having to catch anything that doesn't meet one of those requirements.