SQL IN Operator

Using IN operator in SQL, We can SELECT, UPDATE or DELETE operations providing multiple values in the WHERE clause.

That means SQL query will be executed for those provided values in the IN clause. This IN clause works like OR condition. If one value supplied in the IN clause is not found the query will look for other values.
You can also provide SELECT Query in the IN clause.

Syntax (supply values)

SELECT column_name1,column_name2...column_name_n
FROM table_name WHERE
column_name IN (value1,value2...valuen)

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Query_1

Select those employees from above table who's experience is either 7 or 5 years or both.

SELECT * FROM Employee_details
WHERE Experience in (7,5)

Output

ID	Name	State	JoinDate	Experience
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Query_2

Select those employees from above table who belongs to state either punjab or Pune or both.

SELECT * FROM tbl_Record WHERE
State in ('Punjab','Pune')

Output

ID	Name	State	JoinDate	Experience
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3

Apply NOT to IN clause

Query_3

Select those employees from above table who's experience not in 3 or 5 years.

SELECT * FROM tbl_Record
WHERE Experience NOT IN (3,5)

Output

ID	Name	State	JoinDate	Experience
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

Using SELECT query in IN clause

Syntax

SELECT column_name1,column_name2...column_name_n
FROM table_name WHERE
column_name IN (SELECT Query)

Consider below tables

Example

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3
2	Tarun Sharma	Maharashtra	2017-06-15 01:03:34.247	5
3	Gaurav Kapoor	Pune	2017-08-21 07:30:00.543	3
4	Arjit Singh	Punjab	2017-07-11 10:03:30.577	7

ID	CustomerName	Customer_Type	OrderID	Date
1	Bob	Prime	334	2018-02-11 05:45:54.000
2	Raj	Non-Prime	364	2018-01-04 04:30:54.000

Query_4

SELECT * FROM tbl_Record
WHERE ID in (SELECT ID FROM customers)

Output

ID	Name	State	JoinDate	Experience
1	Anoop Sharma	Delhi	2017-06-12 12:04:30.233	3