The IN
operator is used with the WHERE clause to match values in a list. For example,
SELECT first_name, country
FROM Customers
WHERE country IN ('USA', 'UK');
Here, the SQL command selects rows if the country is either USA or UK.

SQL IN Operator With Columns
The IN
operator can also be used to select rows in which a certain value exists in the given field. Let's see an example to clarify it.
SELECT first_name, country
FROM Customers
WHERE 'USA' in (country);
Here, the SQL command selects the rows if the USA value exists in the country field.

SQL NOT IN Operator
The NOT IN
operator returns is used to exclude the rows that match values in the list. It returns all the rows except the excluded rows. For example,
SELECT first_name, country
FROM Customers
WHERE country NOT IN ('UK', 'UAE');
Here, the SQL command selects rows if UK or UAE is not in the country column.

Note: The working of IN
operator is reversed by NOT
Operator. They are basically two operators combined. To learn more, visit SQL AND, OR, and NOT Operators.
SQL IN Operator With Duplicate Values
By the way, the IN
operator ignores duplicate values in the list. For example,
This code
SELECT first_name, country
FROM Customers
WHERE country IN ('USA', 'UK', 'USA');
is equivalent to
SELECT first_name, country
FROM Customers
WHERE country IN ('USA', 'UK');
SQL IN Operator With Subquery
Suppose we want details of customers who have placed an order. Here's how we can do that using a subquery,
SELECT customer_id, first_name
FROM Customers
WHERE customer_id IN (
SELECT customer_id
FROM Orders
);
Here, the SQL command
- selects customer_id from Orders table
- select rows from Customers table where customer_id is in the result set of subquery
To learn more, visit SQL Subquery.