Using the SQL IN Operator: A Comprehensive Guide

The IN operator allows you to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

You can use this below syntax also

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example 1:

Code:

SELECT * FROM Employee
WHERE EmpDept IN ('IT', 'ADMIN')

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example 2:

Code:

SELECT * FROM Employee
WHERE EmpDept NOT IN ('IT', 'ADMIN')

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS

Example:

Code:

SELECT * FROM Employee
WHERE EmpDept IN (
SELECT EmpDept FROM Employee WHERE EmpName = 'Rambo' OR EmpName = 'Sorif')

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN