SQL SELECT IN Statement
SQL SELECT IN Statement allows to specify multiple values in the WHERE clause. It is similar to using multiple OR conditions.
The IN 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 a FALSE.
Syntax
Syntax 1: LIST
Using the IN operator to provide a list of values:
SELECT column1, column2….columnN
FROM table_name
WHERE column_name IN (val-1, val-2,…val-N);
Syntax 2: SUBQUERY
Using the IN operator to provide a subquery:
SELECT column1, column2….columnN
FROM table_name1
WHERE column_name IN
(SELECT column_name
FROM table_name2);
SQL SELECT IN Example
Let’s look at some examples of the SELECT IN in SQL and understand it’s working. First we have to create a demo database and table, on which we will perform the operation.
CREATE DATABASE RECORD;
USE RECORD;
CREATE TABLE COURSE(
course_id INT,
course_name VARCHAR(20),
duration_of_course INT,
PRIMARY KEY(course_id)
);
CREATE TABLE STUDENT(
roll_no INT,
student_name VARCHAR(20),
course_id INT,
PRIMARY KEY(roll_no)
);
INSERT INTO COURSE(course_id, course_name, duration_of_course)
VALUES
(1, 'BCA', 3),
(2, 'MCA', 3),
(3, 'B.E.', 4),
(4, 'M.E.', 2),
(5, 'Integrated BE and ME', 5);
INSERT INTO STUDENT(roll_no, student_name, course_id)
VALUES
(1, 'ANDREW', 1),
(2, 'BOB', 1),
(3, 'CHARLES', 1),
(4, 'DAIZY', 3),
(5, 'EMMANUEL', 2),
(6, 'FAIZAL', 2),
(7, 'GEORGE', 4),
(8, 'HARSH', 5),
(9, 'ISHA', 2),
(10, 'JULIAN', 2),
(11, 'KAILASH', 3),
(12, 'LAIBA', 5),
(13, 'MICHAEL', 3);
COURSE Table:
STUDENT Table:
SELECT IN with List Example
In this example, we will use SELECT IN statement to provide list of values in WHERE Clause.
SELECT * FROM
STUDENT
WHERE course_id
IN (1, 2, 3);
SELECT IN with the Sub-query Example
In this example, we will use SELECT IN to provide a subquery to WHERE clause.
SELECT * FROM STUDENT
WHERE course_id
IN (SELECT course_id FROM COURSE
WHERE duration_of_course = 3);