MySQL SELECT WHERE IN SubQuery

This article will discuss how to use the SELECT WHERE IN statement along with a related sub-query. Subqueries are SELECT statements within another statement. 

SELECT WHERE IN subquery is generally used when the sub-query results are interlinked to the primary/ outer query results. The final result depends on the relationship of the output of the inner query with the outer-query. Hence, SELECT WHERE IN subquery is a widespread practice while writing queries to get data from multiple tables or even a single table.

 Let us get started by making the sample data used across the examples. We will be creating two tables student_college_details, student_personal_details followed by inserting some rows into them.

# create table student_college_details
CREATE TABLE student_college_details (
student_id INT,
student_college_name VARCHAR(50)
);
# insert data to student_college_details
INSERT INTO student_college_details(student_id,student_college_name) 
VALUES(1,"XVR Academy"),
(2,"XVR Academy"),
(3,"XVR Academy"),
(4,"Learner's Intitute"),
(5,"Learner's Intitute"),
(6,"XVR Academy"),
(7,"Learner's Intitute"),
(8,"Learner's Intitute"),
(9,"XVR Academy");
# create table student_personal_details
CREATE TABLE student_personal_details (
student_id INT,
student_name VARCHAR(255),
student_ssn_no BIGINT,
total_score INT
);
# insert data to student_personal_details
INSERT INTO student_personal_details(student_id,student_name,student_ssn_no,total_score) 
VALUES(1,'Daniel',1147483782,455),
(2,'Sandy',1147483783,500),
(3,'Veronica',1147483784,390),
(4,'Jennifer',1147483785,280),
(5,'Austin',1147483786,458),
(6,'Veronica',1147483787,498),
(7,'George',11474837988,200),
(8,'Veronica',1147483789,232),
(9,'Havard',1147483790,345),
(10,'Mounica',1147483791,405),
(11,'Will',1147483792,284),
(12,"Ryma",1147483793,150);

Let us see what got added to both the tables by executing SELECT * statements on them.

SELECT * FROM student_college_details;

Output:-

SELECT * FROM student_personal_details;

Output:-

We will be looking into two examples to demonstrate how to use SELECT WHERE IN subquery, but before that, let us glance at the syntax.

SELECT * FROM tableName WHERE columnName IN (subquery);

Example1: Get all the column values from student_personal_details for only those student_ids which exist in table student_college_details.

Observe the below query for the solution.

SELECT 
    *
FROM
    student_personal_details
WHERE
    student_id IN (SELECT 
            student_id
        FROM
            student_college_details);

Action Output Message:-

9 row(s) returned

Output:-

Explanation:-

The query we ran above needs to get all the personal data for the students who have registered their college details in the table student_college_details. Therefore, in the subquery, we are getting all the student_id values from the table student_college_details. Then for these student_id values, the outer query gets the other details from table student_personal_details.

Example2: Get the student_id and student_college_name of the students who have a total_score of more than 400.

Observe the below query for the solution.

SELECT 
    student_id, student_college_name
FROM
    student_college_details
WHERE
    student_id IN (SELECT 
            student_id
        FROM
            student_personal_details
        WHERE
            total_score > 400);

Action Output Message:-

4 row(s) returned

Output:-

Explanation:-

The concept is the same as getting data from one table using the results got from the subquery. In the above query, we are getting student_id and student_college_name from the table student_college_details for only the student_id values resulting from the subquery. The subquery will return the student_id of the students who have a total_score > 400. 

Note that even though student_id = 10 had total_score = 405 was not eligible to be part of the final result because student_id = 10 is missing in the table student_college_details.

READ MORE

• MySQL SELECT WHERE
• MySQL SELECT WHERE LIKE

We hope this article helped you with MySQL SELECT WHERE IN subqueries. Good Luck!!!.