JIYIK CN >

In this tutorial, we aim to explore how to use IF ELSEthe statement in MySQL.

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

One of them is IF ELSEthe statement. IF ELSEThe statement, as the name suggests, helps us filter the data of a specific table in the MySQL database.

These filtering conditions IFare set in the block of statements. If the data entered in our table does not meet certain conditions, ELSEthe block is executed.

For example, in an employee table containing employee details, if we wish to filter employees based on their salary, we can use IF ELSEthe WITH clause in MySQL. Let us understand how this approach works.

Before we begin, let's student_detailscreate a dummy dataset by creating a 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 see 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 ELSEStatements in MySQL

IF ELSEThe basic syntax of the technique is as follows.

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

As shown in the above query, we use casethe INSERT statement and ELSEthe SELECT clause. This is how the INSERT statement is executed in MySQL IF ELSE.

Let's student_detailsfilter the data from the table, making sure that we print only the student's last name and not the first name when stu_idis greater than . Otherwise, if is less than or equal to , we will print the first name.3stu_id3

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

CASE ELSEAn alternative to the technique is stored procedures. It is possible to IF ELSEcreate stored procedures using blocks, but this approach is very inefficient and caseis best suited for use cases similar to those discussed above.

Therefore, casewith the help of statement, we can effectively implement IF ELSEthe functionality expected from statement from any other programming language to filter data in MySQL.