IN condition is used to reduce the OR operators in the query. IN condition increases the readability by decreasing the multiple OR operators in the query. Within the IN condition, we can specify multiple values in WHERE clause. By using IN condition, we can fetch the matching rows which are matched with the specified values in the IN condition.

IN Condtion uses with SELECT, UPDATE and DELETE statements.

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

UPDATE table_name
SET column1 = value1, column2 = value2, …, columnN = valueN 
[WHERE column_name [NOT] IN (value1, value2, …, valueN)]; 

DELETE FROM table_name
[WHERE column_name [NOT] IN (value1, value2, …, valueN)]; 

• column1, column2, …, columnN - Specifies the column names from table.
• table_name – Specifies the name of the table.
• Column_name - Specifies the column that used with IN operation.

Let us consider the employee_details as follows –

Scenario – Fetch single column using IN condition.

Requirement - Fetching designation from employee_details table whose emp_id 006 and 007. The query was as follows –

SELECT designation FROM employee_details WHERE emp_id IN (006,007); 

By executing above query, we can get results as shown below –

Scenario – Get multiple columns using IN condition.

Requirement – Get employee details whose manager is "Employee2" or "Employee3". The query was as follows –

SELECT * FROM employee_details
WHERE manager_id IN ("Employee2", "Employee3"); 

By executing above query, we can get results as shown below –

Scenario – Fetch rows using NOT IN condition.

Requirement - Fetch all employee details whose manager is not "Employee4" or "Employee3". The query was as follows –

SELECT * FROM employee_details
 WHERE manager_id NOT IN ("Employee3", "Employee4"); 

By executing above query, we can get results as shown below –

Scenario – Fetch rows using IN condition with subquery.

Requirement – Fetch employee details whose department location is "Location1". The query was as follows –

SELECT * FROM employee_details 
WHERE dept_id IN (SELECT dept_id FROM
department_details WHERE dept_location = "Location1");  

By executing above query, we can get results as shown below –

Scenario – Update multiple columns using IN condition.

Requirement – Update salary as 30000 whose manager is "Employee2" or "Employee3". The query was as follows –

UPDATE employee_details
SET salary = 30000 
WHERE manager_id IN ("Employee2", "Employee3"); 

By executing above query, we can update the salary as 30000 whose manager is "Employee2" or "Employee3". The updated table as follows –

Scenario – Delete multiple columns using IN condition.

Requirement – delete employee rows whose manager is "Employee2" or "Employee3". The query was as follows –

DELETE FROM employee_details
WHERE manager_id IN ("Employee2", "Employee3"); 

By executing above query, we can delete the rows whose manager is "Employee2" or "Employee3". The updated table as follows –