If you have programmed before, then these will be familiar. The two control structures available are: CASE and IF.
If... Else
These functions return a value depending on whether a condition is true or not.
Think of it like this:
if ((x == x) == true) {
return "X"
} else {
// false
return "Y";
}
The generic syntax for this function is:
IF (expr,val1,val2)
The expression that we are testing is the first parameter. If expr is true, then the return value is val, otherwise the return value is val2.
Example:
SELECT IF(5>2,'a',4)
If 5 is greater than 2, the return value is 'a'. If 5 is less than 2 the return value is 4. Since 5 is greater than 2, the return value is 'a'.
Another example:
SELECT IF(3>4,'greater','less')
Result:
less
You can nest if structures by including an if structure as one of the return values. If you have used Excel before then this notation will be familar to you. You can make more complicated statements using AND and or operators.
This might be useful when you want to return data from one table, if a certain condition is true or from a different table if the condition is false.
Switch Case
The switch case structure is a concise method of testing one variable against multiple values.
The generic syntax is:
SELECT CASE value WHEN compareValue THEN result WHEN compare2 THEN result ELSE result END;
Example:
SELECT CASE 1 WHEN 5 THEN '5' WHEN 4 THEN '4' WHEN 3 THEN '3' ELSE 1 END;
Output:
1
This is a useless example but you might want to test a value that the user has entered and return a certain value depending on what the value is, or insert it into a different table depending on the value.
