MySQL WHERE IN Array

MySQL is an RDBMS, it can store huge amounts of structured data, efficiently. Sometimes while managing a database, there is a need to extract data from the tables that meet multiple criteria. The WHILE IN operator can specify a list of values to extract the data that meets multiple criteria, It is used inside the SELECT statement.

This article will help you understand the WHILE IN operator as it will provide you with its syntax along with multiple examples.

Prerequisite: Connect to your Local MySQL Server

To connect your Local MySQL Server, open the command prompt and use this syntax, make sure that you provide your Local database server username:

mysql -u md -p

Your MySQL server is logged in successfully.

Type this command to see all your available databases:

SHOW DATABASES;

Use this syntax, to select the database:

USE <db-name>;

Your database is changed successfully.

Syntax of WHERE IN Operator
The given below is the syntax for the WHERE IN operator, “column-name” is the name of the column you want to search, “table_name” is the name of the table you want to search in, “value1, value2, …” are the array of values you want to search for. The IN keyword is used to specify that you want to search for values that match any of the values in the list:

SELECT * FROM <table-name> WHERE <column-name> IN ('value1', 'value2', ...);

Let’s see a few examples for better understanding of the WHERE IN operator.

Example 1: Using The WHERE IN Operator With an Array

The MySQL WHERE IN operator provides a way to search for data within a table based on a specified list of values. Let’s write a query to extract the table “Supplier” data that only contains the values “Germany, UK, Japan” in the column “Country”, type:

SELECT * FROM Supplier
WHERE Country IN ('Germany', 'UK', 'Japan');

The table is extracted successfully with the data where the values of “Country” meets the criteria.

Let’s extract the table data by providing multiple values for the “Id” column, by typing:

SELECT * FROM Product WHERE Id IN (2, 50, 70);

Example 2: Using the WHERE IN Operator with Subqueries

WHERE IN operator is to search for data in a table based on values found in another table. Let’s see an example to use the WHERE IN operator in combination with a subquery to search for data in a “Supplier” table based on values found in a related “Product” table:

SELECT * FROM Supplier
WHERE id IN (
SELECT SupplierId FROM Product
WHERE UnitPrice IN (22, 25, 23)
);

In the output above it is visible that it has successfully displayed the result of “Id” of “Suppliers” table based on “SupplierId” of “Product” table.

Example 3: Using the WHERE IN Operator with the NOT IN Clause

The MySQL WHERE IN operator can be combined with the NOT IN clause to search for data within a table that does not meet a specified set of values.

Let’s search for table values that do not meet a list of values in the “Country” column:

SELECT * FROM Supplier
WHERE Country NOT IN ('Germany', 'UK', 'Japan');

Let’s extract the table values that does not meet a list of values in the “Customers” column:

SELECT * FROM Customer
WHERE Country NOT IN ('Germany', 'UK', 'Mexico');

You have successfully used the WHERE IN Operator, to retrieve the data based on multiple values, making it an efficient and user-friendly solution for the data management tasks.

Conclusion

MySQL is an RDBMS that has the ability to store huge amounts of structured data. It provides many efficient and convenient ways to search for data based on queries. WHILE IN operator is used with an array of values, a subquery, the WHERE IN operator provides an efficient and convenient way to search for data that meets specific criteria.