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);
Last Updated :
20 May, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...