MySQL If-Then Statements

The ‘IF’ declaration is often used in preserved programs in MySQL that enforce the simple conditional construct. The IF-THEN statement allows a series of SQL statements to be performed based on a condition specified. It yields True, False, or NULL as one of the three values. Throughout this guide, we will understand how to process a piece of SQL command regarding a given condition by using the MySQL IF-then statement.

Syntax:

>> IF condition THEN statements END IF;

In the following syntax:

IF: It’s the keyword clause for the condition to be started.
Condition: After the ‘IF’ clause, It’s the constraint to be fulfilled.
Statements: Can be any piece of code, e.g., select, retrieve, update, delete. If a condition is evaluated as TRUE, then the statements after the ‘THEN’ clause will be executed.
ENDIF: It’s the end of the ‘IF’ clause. After it, the power is moved to the next clause.

Let’s start understanding If-then by launching the MySQL command shell. By typing the password, we are ready to go.

Example 01: MySQL IF() Function:

To gain knowledge about the If statement, we have first to test the IF() function. In the below example, we have defined the IF() function in the SELECT query and give it a condition to check whether 2 is greater than 9 or not. If the condition is valid, it will return the first value after the condition; otherwise, second. As our condition is not valid, that’s why it returns ‘false’.

Let’s assume a table ‘order’ as shown in the appended image.

>> SELECT * FROM data.order;

Let’s have a look at the IF() function performed on this table. We have been selecting the three columns. If the column ‘Status’ has the value ‘Paid’ then the IF() method will return ‘Excellent’ otherwise ‘Bad’. The IF() function’s returned value will be saved into a newly run-time-created column ‘Remarks’. Now we can see the output as appended below.

Example 02: MySQL IF-THEN Statement

We have tried an IF() function on the MySQL command line. Let’s try a new example of the IF-Then statement in a MySQL GUI while using procedures. Open the MySQL Workbench 8.0 and connect to the Database first.

We have been working on the database ‘data’; then, you have to state the ‘use data’ command to use it in Workbench. Press the flash icon to execute it. You have to know that the If-then statements work with store procedures, as shown below. We have declared a keyword delimiter to start the store procedure. The procedure ‘myResult’ has been taking two arguments. After the BEGIN statement, we have an IF statement that is checking the condition. If the condition satisfies, then the ‘THEN’ command and its following statement will be executed. If the condition gets false, then the statements after ‘END IF’ will be implemented.

As the procedure ‘myResult’ has been taking two arguments, we have to pass two values into it.

After passing values to the store procedure, we have to Call the procedure to see the results of the If-then statement.

And the result is given below. It has calculated the discount_rate via the If-then statement.

If you want to use the same stored procedure again, you have to drop this procedure first using the DROP command below and then execute it again.

Example 03: MySQL IF-THEN-ELSE Statement

Let’s go to some broad level. We will be looking at the IF-Then-Else statement this time by using the stored procedure in our example. Have a look at the below table, ‘student’ with some fields in it.

>> SELECT * FROM data.student;

First of all, you have to use the database ‘data’ to use the table ‘student’ in our store procedure. For that, type the below command in your MySQL command-line terminal.

>> use data;

Now declare a delimiter, and then start writing the store procedure. The ‘CREATE’ command will be used to declare or create a procedure as always. The procedure ‘details’ has been taking two arguments. After that, the store procedure starts with the ‘BEGIN’ keyword. The word ‘DECLARE’ has been used to define a variable ‘Sub’ for subjects. The ‘SELECT’ query has been used to select the values of column ‘Subject’ from the table ‘student’ and save it into the newly declared variable ‘Sub’. The user-provided value ‘S_Subject’ will be compared to the column ‘Subject’ value. In the ‘IF’ statement, If the user-provided value ‘S_Subject’ matches with the column ‘Subject’ value, then the relative ‘THEN’ statement will be executed along with its statements within. This procedure will be processed from the first ‘IF’ statement to the second, then the third ‘ELSEIF’ statement. If the last ‘ELSEIF’ part doesn’t match with the value provided by a user, then control will be given to the ‘END IF’ statement.

Let’s end the delimiter with the below command.

We have to call the store procedure by executing it with a ‘CALL’ query and provided arguments in the parenthesis. As we have given ‘Maths’ as its value, then a new column will be generated to output the ‘THEN’ and ‘SET’ statement.

Let’s check the newly created column ‘S_Cource’ by using the below command. You can see we have a relevant result according to the course ‘Maths’.

Call the procedure again by a change at the first argument, i.e., subject. We have provided the subject ‘Computer’ this time.

While selecting the value of the ‘S_Cource’ column, you can see we have a value relating to the subject ‘Computer’, which is ‘IT’.

Once more, we have been calling the procedure as below by a modification at the first argument. We have delivered the subject ‘History’ this time.

By calling the column ‘S_Cource’ again, you can see how we have a result ‘Masters in History’ regarding the recently provided subject, e.g., History.

Conclusion:

We have done will all the examples required for If-then statements elaboration, e.g., IF() function, If-then simple statement, If-then-else statement.