when we use in operator

Answer 1

We use IN operator in order to specify multiple values in a WHERE clause.
The syntax is:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

e.g.:
SELECT *
FROM employees
WHERE Salary IN (7000, 8000, 9000);

It is similar when we use OR operator, OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
The syntax is:
SELECT column_name(s)
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN];

e.g.:
SELECT *
FROM employees
WHERE Salary=7000 OR Salary=8000 OR Salary= 9000;

You will get the same result set, these queries are going to return only employees that their salaries are equal to 7000, 8000, 9000.

Answer 2

Checking a string for a list of options.
For example:
Table dbo.Country
ID (int), Name (string)
1,'America'
2,'United States of America'
3,'Canada'
4,'China'

SELECT ID
FROM dbo.Country
WHERE Name in ('America', 'Canada')
--Returns 1 and 3
--Note that 2 is not returned because 'United States of America' does NOT match 'America' (no wildcard) and 4 (China) is not one of the options specified

Shorter form of:
WHERE Name = 'America'
OR Name = 'Canada'

keeping in mind that the list of possibilities could potentially be quite long...