SQL IN Operator

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

1. selects customer_id from Orders table
2. select rows from Customers table where customer_id is in the result set of subquery

To learn more, visit SQL Subquery.