If ELSE in MySQL

In this tutorial, we aim at exploring how to use the IF ELSE statement in MySQL.

One of the key roles of a data analyst is to gather insights from the data and generate meaningful results. It can be done with the help of several data filtering and visualization techniques.

One of them is the IF ELSE statement. An IF ELSE statement, as the name suggests, helps us filter data of a particular table in the MySQL database.

These filtering conditions are set in the IF block of the statement. If a particular condition is not met by the data entered in our table, the ELSE block is executed.

For example, in an employee table with employee details, we can use the IF ELSE clause in MySQL if we wish to filter the employees based on their salaries. Let us understand how this method works.

Before we begin, let us create a dummy dataset by creating a student_details table with a few rows.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

To view the entries in the data, we use the following code.

SELECT * FROM student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

IF ELSE Statement in MySQL

The basic syntax of the IF ELSE technique is as follows.

select column_name, 
    (
    CASE 
        WHEN <condition> THEN <operation>
        ELSE 1
    END)
 from table_x;

As seen in the aforementioned query, we use the case statement along with the ELSE clause. This is how the IF ELSE statement is carried out in MySQL.

Let us filter data from our student_details table by ensuring that only the students’ last names are printed instead of the first name if the stu_id is greater than 3. Otherwise, we print the first name if the stu_id is less than or equal to 3.

We can achieve this using the following query.

select stu_id, 
    (
    CASE 
        WHEN stu_id <= 3 THEN stu_firstName
        ELSE stu_lastName
    END) AS filtered_data
 from student_details;

Output:

stu_id	filtered_data
1		Preet
2		Rich
3		Veron
4		Jos
5		Shah
6		Parker
7		Miller

An alternative to the CASE ELSE technique is a stored procedure. A stored procedure can be created with the IF ELSE block, but this method is highly inefficient and case works best with use cases similar to the one discussed above.

Therefore, with the help of the case statement, we can efficiently implement the functionality expected from an IF ELSE statement from any other programming language to filter data in MySQL.