Introduction To MySQL IN Operator

What is the MySQL IN Operator?

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

• MySQL IN Operator used to retrieve the result set by checking whether a value from the set of literal values or provided by the subquery is matched or not.
• Basically, this operator helps to determine the specific value if available in the result of subquery using the WHERE clause.
• It fetches the set of columns from the table if a particular value is present in the list of values provided to match a conditional expression.
• Thus, the MySQL IN operator like other MySQL operators allows comparing a value matching from the set of column values returned by a subquery statement.
• If we want to get the rows as result from the Database table where a particular value should be present in a list of values available through conditional query then, we use MySQL IN operator.
• We extract the rows which have matching values in the list of values in IN operator function from a particular value of column given using WHERE keyword in MySQL.

IN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

or:SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

In this syntax:

• First, specify the value to test on the left side of the IN operator. The value can be a column or an expression.
• Second, specify a comma-separated list of values to match in the parentheses.

The IN operator is functionally equivalent to the combination of multiple OR operators:

value = value1 OR value = value2 OR value = value3 OR ...

How does in operator works in MySQL?

Introduction to MySQL IN Operator. MySQL IN Operator used to retrieve the result set by checking whether a value from the set of literal values or provided by the subquery is matched or not. Basically, this operator helps to determine the specific value if available in the result of subquery using the WHERE clause.

Demo Database

The table below shows the complete “Customers” table from the Northwind sample database:

IN Operator Examples

The following SQL statement selects all customers that are located in “Germany”, “France” or “UK”:

Example

SELECT * FROM Customers<br>WHERE Country IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are NOT located in “Germany”, “France” or “UK”:

Example

SELECT * FROM Customers<br>WHERE Country NOT IN ('Germany', 'France', 'UK');

The following SQL statement selects all customers that are from the same countries as the suppliers:

Example

SELECT * FROM Customers<br>WHERE Country IN (SELECT Country FROM Suppliers);

Examples to Implement IN operator in MySQL

Let us now discuss the IN operator using some examples and learn the code to write the SQL query for it.

Example #1 – within, NOT IN and OR

We are using tables named ‘Customers’ and ‘Employees’. Below is the SQL statement using IN operator in the Customers table.

Customers Table:

Employees Table:

<code>SELECT * FROM Customers WHERE City IN (‘Delhi’, ‘Mumbai’);</code>

The above query with IN operator will fetch all the records from the table where the customers are from the cities provided in the IN keyword: Delhi and Mumbai.

Output:

If we use OR operator in the query above then, the result will be the same as above:

Output:

But if we have multiple values in the list, then we need to separate each value with OR operators and the statement will be constructed very long. Therefore, to avoid such kind of issue and making the SQL syntax more proper and readable, the IN operator is introduced in MySQL. So, the IN keywords has helped to shorten the statement by listing values together separated by commas.

In reverse, if we want to get the result set which does not match the values in the list then, we use the following query with NOT IN operator in MySQL:

Query:

<code>SELECT * FROM Customers WHERE City NOT IN (‘Delhi’, ‘Mumbai’);</code>

Output:

You can see from the result above that NOT IN operator has fetched the rows of customers form the table that does not belong or related to the cities provided in the list: Delhi and Mumbai.

Example #2 – With Subquery

Generally, the IN operator is used with an inner query or subquery. It is done so because instead of passing a list of literal values with IN keyword, we can use a subquery which fetches the list of values from one or multiple tables and provides them as input to IN operator to match the value in the outer query and produce the result from the SQL statement. In the following query, we have taken both tables Customers (CustNum, Name, City, Phone) and Employees (EmpNum, Profile, CustNum, Salary) to filter the result set from both the tables in MySQL:

Query:

<code>SELECT;CustNum,Name, City FROM Customers WHERE;CustNum;IN (SELECT;CustNum;FROM Employees where;EmpNum;&gt; 202 ) ORDER BY;CustNum;</code>

Output:

Here, the IN Operator has provided the result rows from Customers using subquery where the output of subquery from Employees is the values of the CustNum column which includes the employees having their salary greater than 5000.