The IN operator in MySQL is used to match a value against a set of values. It is often used in the WHERE clause of a SELECT, UPDATE, or DELETE statement to filter the results based on specific criteria.

Points to Remember:

You can also use the IN operator with a subquery.
IN operator can also be used with NOT IN to find values that do not match a set of values.
IN operator allows you to match a value against a set of values, which can be useful for finding specific records in a table or narrowing down the results of a query.
Be careful when you're using IN operator with a large set of values, as it may cause performance issues.

The IN Operator:

The in operator allows you to specify multiple values in a WHERE clause.

Let's have a look at the film table before writing some queries.

SELECT * from FILM;

Output:

Suppose we want to find films that are rated 'PG' and 'G' and 'PG-13', our first instinct would be to use OR and do the needful.

But while writing OR we have to type a lot, so to avoid that we can use the IN operator.

SELECT title, rental_duration, rental_rate, rating 
FROM film 
WHERE rating IN ('PG','G','PG-13');

Output:

So we are basically providing a list of required values to the query.

We can also use NOT IN to find values other than the ones mentioned in the list

SELECT title, rental_duration, rental_rate, rating 
FROM film 
WHERE rating NOT IN ('PG','G','PG-13');

Output:

We can also use IN to refer to a select statement inside the initial select statement.

Problem: Suppose we want to find customers who are from Moscow, our first instinct would be to go to the customer table and find it, but when we go there we find out that the customer table consists of no column as a city, then we think maybe we could get the city from the address table, but sadly we won't, we just have the city id.

select * from customer;

Output: As we mentioned above, no city column.

Solution:

So what do we do in this case, we use 2 select statements which will link one another.

There might be different ways(joins) to find it but we will be using the IN operator here.

SELECT customer_id, first_name, last_name 
FROM customer 
WHERE address_id 
IN 
(SELECT address_id
FROM address 
WHERE city_id 
IN 
(SELECT city_id 
FROM city 
WHERE city = 'Moscow'));

Output:

In conclusion, the IN operator is a powerful tool for filtering results in MySQL. It allows you to match a value against a set of values, making it an effective tool for finding specific records in a table or narrowing down the results of a query.

'll meet again tomorrow!

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References: w3schools.com/mysql/default.asp

*****All the outputs provided are a snippet of the actual result.

12. MySQL: IN Operator

Points to Remember:

The IN Operator: