In

The IN operator is used in the WHERE clause to check if a value matches certain values in the list of output.

value IN (value1, value2,…)

The list of values can be numbers, literals such as string expressions, or the result of a SELECT statement such as:

value IN (SELECT column_name FROM table_name); (The query in parentheses is a subquery and detailed information will be given in the next sections.)

SELECT customer_id, rental_id, return_date FROM rental WHERE
customer_id IN (1, 2) ORDER BY return_date DESC;

Results for values with a customer_id of 1 or 2 are returned.

SELECT customer_id FROM rental WHERE CAST (return_date AS DATE) =
'2005-05-27' ORDER BY customer_id;

Returns the customer_id of columns with return_date value of ‘2005-05-27’.

SELECT customer_id, rental_id, return_date FROM rental WHERE
customer_id NOT IN (1, 2);

Returns all results except for customer_id 1 or 2.

SELECT customer_id, rental_id, return_date FROM rental WHERE
customer_id <> 1 AND customer_id <> 2;

Returns the same result as the previous query.

SELECT
customer_id,
first_name,
last_name
FROM customer
WHERE
customer_id IN (
SELECT customer_id
FROM rental
WHERE CAST (return_date AS DATE) = ‘2005-05-27’ )
ORDER BY customer_id;

The working logic is as follows:

The query inside the IN function is executed. The customer_id values returned as a result of the query are validated within the IN function, and the columns corresponding to those values are displayed as a result.