WHERE IN Statement in MySQL

In this tutorial, we aim at exploring how to use the WHERE IN clause in MySQL.

There are many different data filtering techniques in MySQL. IF ELSE, CASE, and WHERE statements are examples of this. Let us explore the implementation details for the WHERE IN clause in this article.

The WHERE IN clause helps us set a particular condition for data filtering. It takes in the column name and the set in which the values are to be found. The IN part of the query helps look for the value in the defined set.

Let us understand how this method works. Before we begin, we must create a dummy dataset by creating a table, student_details, along 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

WHERE IN Statement in MySQL

The basic syntax of the WHERE IN technique is as follows.

SELECT * FROM name_of_the_table WHERE column_name IN <set_condition>;

Let us try to filter students from the student_details table.

We fetch records only for students with their stu_id less than 3. Using the WHERE IN clause, this operation can be done with the help of the following query.

SELECT * FROM student_details WHERE stu_id IN (1,2,3);

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow

As shown in the code above block, we fetch records with stu_id as 1, 2, or 3 only as required.

An alternative to the WHERE IN technique is the CASE WHEN statement. An IF ELSE stored procedure can also be used instead of the WHERE IN clause.

Therefore, with the help of the WHERE statement with IN, we can efficiently filter data based on any entry availability condition in MySQL.