MySQL is a widely used open-source relational database management system. One of the most commonly used features of MySQL is the WHERE IN list. This feature allows us to filter data based on multiple values in a single query. In this article, we will discuss in detail how to use MySQL WHERE IN List.
What is the WHERE IN List?
The WHERE IN List is a powerful feature in MySQL that allows us to filter data based on a list of values. The query returns all the rows that match any of the values in the list. It can be used with various data types, including numbers, strings, and dates.
The syntax for the WHERE IN List is as follows:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
In this syntax, the column_name(s) represent the column(s) that we want to retrieve data from. The table_name represents the name of the table that we want to retrieve data from. The WHERE clause is used to filter the data based on the list of values specified within the parentheses.
Using the WHERE IN List with Numeric Values
Let’s consider an example where we have a table named “products” that contains information about various products. We want to retrieve the details of products that have a price of $10, $20, or $30. We can use the WHERE IN List to filter the data based on these values.
The query for this would be:
SELECT * FROM products
WHERE price IN (10, 20, 30);
In this query, we have used the WHERE IN List to filter the data based on the price column. The query will return all the rows that have a price of $10, $20, or $30.
Using the WHERE IN List with String Values
The WHERE IN List can also be used with string values. Let’s consider an example where we have a table named “employees” that contains information about various employees. We want to retrieve the details of employees whose job title is either “Manager” or “Director”. We can use the WHERE IN List to filter the data based on these values.
The query for this would be:
SELECT * FROM employees
WHERE job_title IN (‘Manager’, ‘Director’);
In this query, we have used the WHERE IN List to filter the data based on the job_title column. The query will return all the rows that have a job_title of “Manager” or “Director”.
Using the WHERE IN List with Dates
The WHERE IN List can also be used with date values. Let’s consider an example where we have a table named “orders” that contains information about various orders. We want to retrieve the details of orders that were placed on either 2020-01-01 or 2021-01-01. We can use the WHERE IN List to filter the data based on these values.
The query for this would be:
SELECT * FROM orders
WHERE order_date IN (‘2020-01-01’, ‘2021-01-01’);
In this query, we have used the WHERE IN List to filter the data based on the order_date column. The query will return all the rows that have an order_date of either 2020-01-01 or 2021-01-01.
Using the WHERE IN List with Subqueries
The WHERE IN List can also be used with subqueries. Let’s consider an example where we have two tables named “customers” and “orders”. The “customers” table contains information about various customers, and the “orders” table contains information about various orders placed by these customers. We want to retrieve the details of customers who have placed an order. We can use the WHERE IN List with a subquery to filter the data based on these values.
The query for this would be:
SELECT * FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);
In this query, we have used the WHERE IN List with a subquery to retrieve the customer_id values from the “orders” table. The DISTINCT keyword is used to remove any duplicate values. The query will return all the rows from the “customers” table that have a customer_id value that exists in the subquery.
In this article, we have discussed how to use MySQL WHERE IN List. We have seen how it can be used with various data types, including numeric values, string values, and date values. We have also seen how it can be used with subqueries to filter data based on values from another table. The WHERE IN List is a powerful feature in MySQL that can save a lot of time and effort when filtering data based on multiple values.