SELECT IN statement used to fetch specific rows or values from existing table with multiple conditions. The conditions are specified with IN clause. The operation of SELECT IN is same as OR operation. SELECT IN is used to reduce the multiple OR operators in SELECT statement.

SELECT column1, column2, …, columnN 
FROM table_name 
[WHERE column_name [NOT] [IN (value1, value2, …, valueN)]]; 

• column1, column2, …, columnN - Specifies the columns of the table.
• column_name – Specifies the name of the column used in the condition.
• table_name - Specifies the name of the table.

Consider employee_details, department_details tables as follows -

Scenario – Fetch single column using IN clause.

Requirement – Fetch designation of employees who are having emp_id is 003 or 004 from employee_details table. The query was as follows -

SELECT designation FROM employee_details WHERE emp_id IN (003, 004); 

By executing above query, we can get result like as follows –

Scenario – Fetch Specific columns using IN clause.

Requirement – Fetch emp_id, emp_name, designation column values of employees having id 003 or 004 from employee_details table. The query was as follows –

SELECT emp_id, emp_name, designation
FROM employee_details 
WHERE emp_id IN (003, 004); 

By executing above query, we can get output like as follows –

Scenario – All rows information using IN clause.

Requirement – Fetch all columns of employees having id 003 or 004 from employee_details table. The query was as follows –

SELECT * FROM employee_details 
WHERE emp_id IN (003, 004); 

By executing above query, we can get output like as follows –

Scenario – Fetch all columns data using NOT IN clause.

Requirement - Fetch employee data who are not "director" or "clerk" from employee_details table. The query was as follows –

SELECT * FROM employee_details 
WHERE designation NOT IN ('Director', 'Clerk'); 

By executing above query, we can get output like as follows –

Scenario – Fetch all columns data using subquery in IN clause.

Requirement – Fetch all employees whose department is "Office" from employee_details table. The query was as follows –

SELECT * FROM employee_details 
WHERE dept_id IN ( 
	SELECT dept_id 
	FROM department_details 
	WHERE dept_name = "Office" ); 

By executing above query, we can get output like as follows -