SQL – SELECT IN

IN operator is a membership operator which returns values that match the values in a list or subquery. Using this operator we can specify multiple values in a WHERE Clause. This operator compares a value with a set of values, and it returns a true if the value belongs to that given set, else it returns false. It is a shorthand for multiple OR conditions.

Syntax 1(LIST): 

SELECT column1, column2….columnN

FROM table_name

WHERE column_name IN (val-1, val-2,…val-N);

Syntax 2(SUBQUERY): 

SELECT column1, column2….columnN

FROM table_name1

WHERE column_name IN 

(SELECT column_name

FROM table_name2);

Let’s try this step-by-step in Microsoft SQL Server:

Creating the Database:

Use the below SQL statement to create a database called RECORD:

CREATE DATABASE RECORD;

Change to Database:

Use the below SQL statement to change database to RECORD:

USE RECORD;

Creating table:

Now we create a table named COURSE in our RECORD database:

CREATE TABLE COURSE(
course_id INT,
course_name VARCHAR(20),
duration_of_course INT,
PRIMARY KEY(course_id)
); 

Now again we create another table named STUDENT in our RECORD database:

CREATE TABLE STUDENT(
roll_no INT,
student_name VARCHAR(20),
course_id INT,
PRIMARY KEY(roll_no)
); 

Inserting the data in the tables :

Use the below statement we insert data to the COURSE table:

INSERT INTO COURSE(course_id, course_name, duration_of_course) VALUES(1, 'BCA', 3);
INSERT INTO COURSE(course_id, course_name, duration_of_course) VALUES(2, 'MCA', 3);
INSERT INTO COURSE(course_id, course_name, duration_of_course) VALUES(3, 'B.E.', 4);
INSERT INTO COURSE(course_id, course_name, duration_of_course) VALUES(4, 'M.E.', 2);
INSERT INTO COURSE(course_id, course_name, duration_of_course) VALUES(5, 'Integrated BE and ME', 5);

Again, using the below statement we insert data to the STUDENT table:

INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(1, 'ANDREW', 1);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(2, 'BOB', 1);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(3, 'CHARLES', 1);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(4, 'DAIZY', 3);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(5, 'EMMANUEL', 2);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(6, 'FAIZAL', 2);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(7, 'GEORGE', 4);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(8, 'HARSH', 5);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(9, 'ISHA', 2);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(10, 'JULIAN', 2);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(11, 'KAILASH', 3);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(12, 'LAIBA', 5);
INSERT INTO STUDENT(roll_no, student_name, course_id) VALUES(13, 'MICHAEL', 3);

Displaying the data of the tables:

Now using the below statement we display the content of the COURSE table:

SELECT * FROM COURSE;

Again using the below statement we display the content of the STUDENT table:

SELECT * FROM STUDENT;

IN Clause with the list:

SELECT * FROM
STUDENT
WHERE course_id
IN (1, 2, 3);

IN Clause with the sub-query:

SELECT * FROM
STUDENT
WHERE course_id
IN (SELECT course_id FROM
COURSE
WHERE duration_of_course = 3);

My Personal Notes arrow_drop_up

Save