IF statement

What's the weather like today? IF it rains, I should take an umbrella. OR IF today is sunny, then maybe I should go without it? ELSEIF it's very sunny AND hot today, should I use sunscreen?

When writing a program, it might be necessary to create various ways of operating depending on the conditions. There are special IF ? THEN ! ELSE ! constructions for this, which we will now get acquainted with.

Conditions

To start, let's create a simple table:

CREATE TABLE event_list (action TEXT);

To separate the ways of program operations, it is necessary to come up with a condition. The search condition has the following requirements:

• Use comparison operators: < > = != (less, greater, equal, not equal)

• Return only logical answer: true or false

• Use operators for additional conditions: AND, OR

• Use parentheses ( ) for grouping conditions

• The finished construction must be located inside a statement.

Using the example below, let's consider the correct principle of designing IF...ELSE into a procedure.

The IF operator should check a search condition. Since 40 > 20, it returns true. THEN we have to perform an action = 'take sunscreen'. But IF 'sunny' = 'rain', it returns false. THEN we do not have to perform action = 'take umbrella'. Therefore, the correct construction should look like this:

CREATE PROCEDURE first_procedure()
    IF 40 > 20 THEN
        INSERT INTO event_list (action) VALUES ('take sunscreen');
    END IF;

CREATE PROCEDURE second_procedure()
    IF 'sunny' = 'rain' THEN
        INSERT INTO event_list (action) VALUES ('take umbrella');
    END IF;

Then we orderly call PROCEDURE:

CALL first_procedure();
CALL second_procedure();

Finally, we'll see the following result in the action table:

SELECT action FROM event_list;

40 > 20 always returns true, but word 'sunny' never equals 'rain', so this condition 'sunny' = 'rain' always returns false. Only the first_procedure is fully functional.

Check by queue

Now that we've figured it out a little, let's write a procedure that will branch out our program and check several actions at once.

CREATE PROCEDURE third_procedure()
    IF false THEN
        INSERT INTO event_list (action) VALUES ('put on sweater');
    ELSEIF true AND false THEN
        INSERT INTO event_list (action) VALUES ('take book');
    ELSEIF false OR true THEN
        INSERT INTO event_list (action) VALUES ('take hat');
    ELSEIF true THEN
        INSERT INTO event_list (action) VALUES ('take glasses');
    ELSE
        INSERT INTO event_list (action) VALUES ('buy ice cream');
    END IF;

Then let's call the procedure:

CALL third_procedure();

We'll see the following:

SELECT action FROM event_list;

The resulting table will look like this:

Hmm, looks like something went wrong. In addition to the hat, the program should've recommended we take a book and glasses, buy ice cream, and not wear a sweater. As we can see, only the third condition take hat worked. The rest, although they should've happened because the condition was true, were ignored.

When we build a chain of IF THEN , ELSEIF THEN , ... , ELSE , END IF;, it is important to consider that these conditions are always checked in order. It would seem that the second condition should work, but for the AND operator it's important that the first condition and the second condition both return true. For AND, if the first condition returns false, the check will be stopped. For the third condition with the OR operator, it is enough for any one of the conditions to return true. So, it also stops checking other conditions if one of them returns true.

If one of the conditions checks true in this chain, only its own code after THEN will work. Others in the chain will not be checked!

Branching

When we declare IF, it may contain a chain of any number of ELSEIF blocks. And at the end it may contain an additional ELSE statement. Each IF must end with END IF;. All statements between IF and END IF; construct only a single statement block body. When we need to put several blocks together, we need to place them into the BEGIN END; construction.

Let's try to rewrite the previous example using additional IF blocks.

DROP PROCEDURE IF EXISTS third_procedure;

CREATE PROCEDURE third_procedure()
BEGIN
    IF (false AND true) AND (true OR false) THEN
        INSERT INTO event_list (action) VALUES ('put on sweater');
    END IF;
    IF true AND true THEN
        INSERT INTO event_list (action) VALUES ('take red hat');
    END IF;
    IF true OR false THEN
        INSERT INTO event_list (action) VALUES ('take book');
    ELSE
        INSERT INTO event_list (action) VALUES ('buy ice cream');
    END IF;
END;

CALL third_procedure();

Now, let's check the result in the event table:

SELECT action FROM event_list;

We get the following result:

Pay attention to the first check search condition: (false AND true) AND (true OR false).

1. (false AND true) return false

2. (true OR false) return true

3. The last expression (1. false) AND (2. true) returns false

However, there's no mention of ice cream, again. The ELSE operator is an additional operator that will be activated only if none of the previous conditions in the chain have been activated by true.

Multi-construction

So, we're finally outside. It's really warm there, so let's write the following function for going to the store.

CREATE FUNCTION shopping(
    product_name VARCHAR(30),
    description VARCHAR(30),
    total_sum DOUBLE)
    RETURNS TEXT
BEGIN
    DECLARE result_text TEXT;
    DECLARE amount INT;
    IF product_name != 'ice cream' THEN
        SET result_text = 'Sorry, but this is the ice cream shop.';
    ElSEIF total_sum >= 40.00 THEN
        IF description = 'vanilla' 
        OR description = 'banana' 
        OR description = 'caramel' 
        OR description = 'chocolate' 
        THEN
            SET amount = FLOOR(total_sum / 40.00);
            SET result_text = CONCAT('You bought ', amount, ' ', description,' ice creams! Have a good day!');
        ELSE
            SET result_text = 'Sorry, we do not have that kind of ice cream, try another one.';
        END IF;
    ELSE
        SET result_text = 'Sorry, but this amount of money is not enough. You need some more.';
    END IF;
    RETURN result_text;
END;

This function seems quite large and complex. But no one said that shopping is easy. We have created a function that takes the following variables as input: product_name; description; total_sum.

Now, let's see how it works. Suppose you'd try to buy a book.

SELECT shopping('book', 'tom sawyer', 1500) AS 'answer';

This is the answer that you'll get:

How did we get it? Well, the first IF statement checked variable product_name. book != ice cream returned true, because 'book' doesn't equal 'ice cream'. Therefore, the following operation after THEN was executed. And the remaining statements in this block: ElSEIF total_sum >= 40.00 and ELSE were ignored.

Now let's buy something more appropriate for the shop we're in.

SELECT shopping('ice cream', 'banana', 30) AS 'answer';

We get the following answer:

This time, the first IF statement didn't work, because we definitely wanted ice cream. Now the second ElSEIF statement which checks total_sum 30 >= 40 returned false. That's why the last default ElSE statement worked.

Okay, one more try:

SELECT shopping('ice cream', 'banana', 200) AS 'answer';

And here's the result:

Finally, there's enough ice cream to share with your friends. Of course, if our ice cream flavor wasn't in the OR enumeration, the shopkeeper would have to report: 'Sorry, we do not have that kind of ice cream, try another one.' Because this is the default statement after last ELSE, that triggers if the previous conditions weren't activated.

Conclusion

In this topic, we've figured out a little about how IF statements work:

• When we use OR conditions, if one of them returns true, then all statements are true. Conditions are not checked further if one of them returned true.

• When we use AND conditions, the statement returns true if all conditions return true. Conditions are not checked further if one of them returned false.

• In the chain of IF THEN , ELSEIF THEN ,... ,ELSE, END IF; , the statements are not checked further if one of them returned true.

• ELSE is the additional statement that is executed IF all previous conditions are false.