The IF Statement in MySQL

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

The syntax of the IF statement in MySQL can we given as SELECT IF(condition, result_when_true, result_when_false) AS [col_name].

In particular, IF statement, the condition is the criteria defined by the programmer that needs to be evaluated.

It can have one or more than one column involved for consideration. For example, to check if a particular value in a column is greater than 200 or not, we can write a condition if name_of_column > 100.

The result_when_true value represents the output value that we would like to get displayed against the condition if it is evaluated to be true. On the other hand, the result_when_false value represents the output value displayed when the condition is calculated to be false.

Let us try to learn more about this statement with an example.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- 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");

The above query creates a table with rows with students’ first names and last names. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The aforementioned code would give the following 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

Now, let us aim at printing Yes along with the students’ first names if the stu_id is greater than 3. Otherwise, we print No in a separate column named high_stu_id.

the IF Statement in MySQL

As seen in the syntax above, the IF statement in MySQL requires a condition. This works similarly to the CASE statement.

We can utilize the following program to get the desired result in MySQL.

SELECT stu_firstName, IF(stu_id>3,"yes","no") AS high_stu_id
FROM student_details;

The aforementioned code gets each student’s first name and a new column named high_stu_id.

This result column has the value Yes if the stu_id of the student is greater than 3. Otherwise, the value No is printed if the stu_id is less than 3.

The output of the above code can be visualized as follows:

stu_firstName	high_stu_id
Preet				no
Rich				no
Veron				no
Geo					yes
Hash				yes
Sachin				yes
David				yes

Similarly, we can use the IF statement to leverage the data and meet our requirements. An alternative to the IF statement is the CASE statement in MySQL.

Thus, with the help of this tutorial, we can now successfully implement the IF statement in MySQL.