Updated June 6, 2023
Introduction to MySQL WHERE IN Array
MySQL supports different data types implemented for inserting various data values to store in the database. Similarly, an array is a data type responsible for storing column values. An array is a type of data structure defined in MySQL. MySQL provides a WHERE IN clause that is useful to apply in the array variable to produce the query set from a specific table in the database. WHERE IN clause supports fetching data values from an array of parameters provided in the query statement in MySQL. Here, the WHERE works as a conditional expression similar to other programming languages used that is essential to compare the given data values of a specific field value that is available in the MySQL database. In an array, the WHERE IN is used where IN() function is implemented to find a particular value within the range of column values provided as parameters.
Syntax
Following is an elementary syntax structure to code for MySQL WHERE IN Array command in MySQL server to fetch information using array values and WHERE IN clause:
SELECT ColumnName1, ColumnName2, …., ColumnNameNFROM TableNameWHERE ColumnName1 IN(ColumnName1_Value1, ColumnName1_Value2, ColumnName1_Value3);Here, in the above syntax structure, we have defined the following terms:
- ColumnName1 denotes the name of the column present in the table.
- TableName is the specified table holding array values in the database to use MySQL WHERE IN Array.
- Following the WHERE clause, we define a specific column name from which data is retrieved using a SELECT statement. The IN() function includes the range of values as parameters to filter the desired data.
How does WHERE IN Array work in MySQL?
- MySQL supports storing data values in tables, allowing for indexing denormalized columns or arrays. This facilitates efficient management and access to the data through MySQL queries. In genuine, MySQL does not contain any actual array-type storage. As MySQL is also useful in Data Warehousing, this is a fundamental need, but it provides problems in designs for storing denormalized rows. However, we can access them using indexes.
- An array stores items together in adjacent memory locations, enabling the storage of multiple items of the same type. It structures as rows and columns design manner which presents multiplication concepts. Objects, values, numbers, or links are organized in columns and rows in an array. This is similar to the data records stored in a database table in the form of rows and columns.
- Therefore, an array is designed in a structure or storage space when equivalent groups are organized in equivalent rows. So, our using the WHERE IN clause, we can fetch the rows and columns from the database table array by following and validating a condition to result in the required set of output tables in the MySQL server.
- However, MySQL introduced a solution for storing arrays since MySQL v5.7 by implementing the JSON data type. This allows for advanced querying processes in MySQL. We now illustrate the WHERE IN array in the MySQL server through the examples provided below.
- In the case of an array in MySQL with the WHERE IN clause, the MySQL WHERE clause combines with the IN() function, which filters only the rows whose column values match the list of values provided as parameters in the IN keyword. Also, using IN() function with the WHERE clause decreases the number of OR keywords or clauses that may be used in the query sometimes.
Examples of MySQL WHERE IN Array
Here are the following examples:
Example #1
In MySQL, we implement the clause WHERE IN to pass an array in the database. Let us illustrate MySQL WHERE IN Array with a valid example by creating a table with some entries and performing the query.
Initially, let us build up a table providing the name as DemoArray with the help of the following examples:
Code:
CREATE TABLE DemoArray(ID INT PRIMARY KEY, PersonName VARCHAR (255) NOT NULL);Again, we will insert a few records with the help of the INSERT command as follows:
Code:
INSERT INTO DemoArray VALUES (1,'Nikhil'), (2,'Rita'), (3,'Poonam'), (4,'Pragya'), (5,'Meenu'), (6,'Sandhya'), (7,'Anita');Now, display the table contents by using the SELECT statement below:
Code:
SELECT * FROM DemoArray;Output:
The code structure presented below demonstrates how to pass an array parameter using the WHERE IN clause in MySQL:
Code:
SELECT * FROM DemoArray WHERE ID IN(1,3,6);From this, the output of MySQL WHERE IN Array is as follows:
Or, you may also try this one below:
Code:
SELECT * FROM DemoArray WHERE PersonName IN('Nikhil', 'Meenu', 'Anita');From this, the output of MySQL WHERE IN Array is as follows:
Example #2
Again, let us demonstrate using another example; suppose we have a table in our database named books having fields BookID, BookName, Language,, and Price as follows:
Code:
SELECT * FROM Books;Output:
You can observe that we have inserted several records into the Books table.
The following command structure illustrates an array result type using the WHERE IN clause in MySQL. Let’s assume we want to filter rows from a table based on a range of book IDs specified in the IN() parameters.
Code:
SELECT * FROM Books WHERE BookID IN(101,103,106);Output:
As you can see, the resultant output provides an array-type design of rows and columns filtered using the WHERE clause from the table.
Again, we can query using the price column and apply conditions to get the specified matching result of values from the records. They are as follows.
Code:
SELECT BookID, BookName, Language FROM Books WHERE Price IN(5000, 2000,1500);Output:
Thus, the query is helpful to retrieve specific parts of records from a large collection of data in the database in the MySQL server in the form of an array structure using condition also to filter the matching values and further checking this with listed ranges of data values from specific table applying IN() function.
Conclusion
With MySQL WHERE clause, we query results in rows filtered using conditional expression defining a rule to fetch values. This filtration process includes any single value, subquery, or range. The parameters passed to the IN() function represent a range of items, which, when used, create an array-like structure in rows and columns format. The MySQL WHERE IN array is a structured data type representing an array.
Recommended Articles
We hope that this EDUCBA information on “MySQL WHERE IN Array” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
