MySQL IN() function

Last update on July 04 2024 06:48:34 (UTC/GMT +8 hours)

IN() function

The IN() function in MySQL is an efficient way to determine if a given value exists within a specified list of values. This function enhances query readability and can often lead to performance improvements in specific scenarios.

Key Uses of the MySQL IN() Function

Simplified Matching: Provides a concise and readable method to check if a value matches any item in a list of values.

Parameterized Queries: Often used in queries with dynamic lists, allowing for flexible and dynamic data filtering.

Query Optimization: MySQL can optimize the execution of IN() queries, especially when the list of values is constant.

Improved Readability: Makes SQL queries more understandable by clearly indicating membership checks within a specified set.

Data Filtering: Essential for filtering data based on criteria like specific IDs, names, or categories.

Subqueries: Frequently used to filter results based on a set of values returned by a subquery.

Enumerated Data: Ideal for retrieving records that match certain enumerated categories or types.

Syntax and Behavior

Syntax:

expr IN (value,...)

expr: The expression to be compared against the list of values.

value1, value2, ..., valueN: The list of values to be checked against.

Behavior:

Returns 1 (true) if expr matches any value in the list.

Returns 0 (false) if expr does not match any value in the list.

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules.

MySQL Version: 8.0

Basic Example: MySQL IN() function

The following MySQL statement will return 1 because the specified value is within the range of values.

Code:


-- This query checks if the number 10 is present in the list of values (15, 10, 25).
SELECT 10 IN(15, 10, 25);
-- This statement returns 1 (true) if 10 is found in the list, otherwise it returns 0 (false).

Explanation:

The purpose of this SQL query is to determine if the number 10 is present in the specified list of values (15, 10, 25).

SELECT 10 IN(15, 10, 25): This part of the query performs a comparison operation using the IN operator.

The IN operator is used to check if a specified value (in this case, 10) matches any value in a list of values (15, 10, 25).

The expression 10 IN(15, 10, 25) evaluates to 1 (true) if 10 is found in the list, and 0 (false) if it is not.

The query will return 1 because the number 10 is indeed present in the list of values (15, 10, 25).

This type of query is useful for checking membership of a value within a set of possible values and can be used in various conditions within more complex queries to filter or validate data based on specific criteria.

Output:

mysql> SELECT 10 IN(15,10,25);
+-----------------+
| 10 IN(15,10,25) |
+-----------------+
|               1 | 
+-----------------+
1 row in set (0.00 sec)

Example : IN() function with where clause

The following MySQL statement checks which books have either 300 or 400 or 500 pages.

Sample table: book_mast

Code:


-- This query selects specific columns from the 'book_mast' table where the number of pages is either 300, 400, or 500.
SELECT book_name, dt_of_pub, no_page
-- This statement specifies the columns to be retrieved: 'book_name', 'dt_of_pub', and 'no_page'.
FROM book_mast
-- This part of the query specifies the table from which data is being retrieved, which is 'book_mast'.
WHERE no_page IN (300, 400, 500);
-- This clause filters the rows to include only those where the 'no_page' column has a value of 300, 400, or 500.

Explanation:

The purpose of this SQL query is to retrieve information about books that have exactly 300, 400, or 500 pages.

SELECT book_name, dt_of_pub, no_page: This part of the query specifies the columns to be selected from the 'book_mast' table. It includes the book's name (book_name), date of publication (dt_of_pub), and number of pages (no_page).

FROM book_mast: This part specifies the table from which the data is being selected, which is the 'book_mast' table.

WHERE no_page IN (300, 400, 500): This clause filters the results to include only those rows where the no_page column has a value of 300, 400, or 500. The IN operator is used to check if the value of no_page is one of the specified values (300, 400, 500).

Output:

mysql> SELECT book_name,dt_of_pub,no_page
    -> FROM book_mast          
    -> WHERE no_page IN (300,400,500);
+-------------------------------------+------------+---------+
| book_name                           | dt_of_pub  | no_page |
+-------------------------------------+------------+---------+
| Understanding of Steel Construction | 2003-07-15 |     300 | 
| Fundamentals of Thermodynamics      | 2002-10-14 |     400 | 
+-------------------------------------+------------+---------+
2 rows in set (0.09 sec)

Relational Algebra Expression:

Relational Algebra Tree:

Using IN() with Subqueries

The IN() function is powerful when combined with subqueries, allowing for dynamic data filtering based on results from other queries:

Sample table: book_mast

Sample table: author

Code:


-- Selects the book name and author ID from the 'book_mast' table
SELECT book_name, aut_id
-- Specifies the 'book_mast' table as the source of the data
FROM book_mast
-- Filters the 'book_mast' table rows to include only those where the author ID matches any author ID
-- in the subquery. The author ID from 'book_mast' is compared using the 'latin1_swedish_ci' collation.
WHERE aut_id COLLATE latin1_swedish_ci IN 
    -- Subquery: Selects the author IDs from the 'author' table where the country is 'USA'
    (SELECT aut_id FROM author WHERE country = 'USA');

Explanation:

This SQL query retrieves the names of books (book_name) and their corresponding author IDs (aut_id) from the book_mast table.

It filters the results to include only books whose authors are from the USA, as determined by matching the aut_id from the book_mast table to those in the author table where the country is 'USA'.

The aut_id comparison uses the latin1_swedish_ci collation to ensure consistency in string comparison and avoid collation errors.

Output:

book_name                      |aut_id|
-------------------------------+------+
Fundamentals of Heat           |AUT006|
Human Anatomy                  |AUT008|
Fundamentals of Thermodynamics |AUT010|
Networks and Telecommunications|AUT015|

Combining IN() with Other Conditions

We can combine IN() with other SQL conditions to create more complex queries:

Sample table: book_mast

Code:


-- Selects the book name, publication date, and number of pages from the 'book_mast' table
SELECT book_name, dt_of_pub, no_page
-- Specifies the 'book_mast' table as the source of the data
FROM book_mast
-- Filters the rows to include only those where the publication date is after January 1, 2003
WHERE dt_of_pub > '2003-01-01'
-- Further filters the rows to include only those where the number of pages is 300, 400, or 500
AND no_page IN (300, 400, 500);

Explanation:

This SQL query fetches details of books from the book_mast table, specifically their names (book_name), publication dates (dt_of_pub), and page counts (no_page).

It filters the results to include only books published after January 1, 2003, and with a page count of either 300, 400, or 500 pages.

The combined use of date and page number criteria helps in narrowing down the selection to books meeting both conditions.

Output:

book_name                          |dt_of_pub |no_page|
-----------------------------------+----------+-------+
Understanding of Steel Construction|2003-07-15|    300|

Performance Considerations

Constant Values: When the list of values in IN() is a set of constants, MySQL can efficiently use a binary search, making the operation very fast.

Large Lists: For very large lists, consider alternatives like joining with temporary tables for better performance.

Indexes: Ensure that columns used with IN() are indexed for faster query execution.

Subqueries: Using IN() with subqueries can impact performance, especially if the subquery is complex or returns a large result set. Optimize subqueries for better overall performance.

Common Mistakes and Tips

Type Consistency: Ensure that the types of expr and the values in the list are consistent to avoid unexpected results due to implicit type conversion.

Null Values: Be cautious with NULL values. IN() does not consider NULL in the list of values unless explicitly included.

Use with Care in Large Sets: While IN() is useful, it can lead to suboptimal performance when dealing with very large lists or complex subqueries. Evaluate the need for alternative approaches in such cases.

Related Functions

ANY and SOME: These functions offer similar capabilities to IN() for comparing values against a list.

EXISTS: Checks for the existence of rows returned by a subquery, often used as an alternative to IN() for subquery comparisons.

JOIN: For more complex relationships, using JOIN operations might be more efficient than IN() in some scenarios.

Frequently Asked Questions (FAQ) - MySQL IN() Function

1. What is the MySQL IN() function?

The MySQL IN() function is used to determine if a specified value matches any value in a list of values. It is commonly used to simplify and enhance the readability of SQL queries.

2. Why should we use the IN() function?

The IN() function is particularly useful for:

Simplifying the syntax when checking if a value is within a specified set.

Improving query readability and making the intent of the query clearer.

Optimizing queries by allowing MySQL to efficiently handle the matching process, especially with constant value lists.

Filtering data dynamically in parameterized queries and subqueries.

3. How does the IN() function work?

The IN() function checks whether the given expression matches any value in a provided list. If the value is found in the list, the function returns 1 (true); otherwise, it returns 0 (false).

4. When should we use the IN() function in our queries?

Use the IN() function when you need to:

Check if a value exists within a specific list.

Perform comparisons against a fixed or dynamic set of values.

Enhance query readability by replacing multiple OR conditions.

Filter results based on criteria that can be represented as a list of values.

5. Can the IN() function be used with subqueries?

Yes, the IN() function is often used with subqueries to filter results based on a set of values returned by another query. This makes it a powerful tool for complex filtering operations.

6. What are the performance considerations for using the IN() function?

Constant Values: MySQL can optimize the IN() function when the list consists of constants, making the operation very fast.

Large Lists: For very large lists, consider using JOINs or temporary tables for better performance.

Indexes: Ensure the columns used with IN() are indexed to enhance query performance.

Subqueries: When using IN() with subqueries, be mindful of the potential impact on performance, especially with complex or large result sets.

7. What are some common mistakes to avoid with the IN() function?

Type Consistency: Ensure that the data types of the expression and the values in the list match to avoid unexpected results.

Null Values: Be aware that NULL values are not considered in the IN() check unless explicitly included in the list.

Large Sets: While IN() is convenient, it may not be the most efficient choice for very large sets or when more complex query logic is needed. Evaluate alternative approaches if performance is an issue.

8. How does the IN() function compare to similar SQL functions?

ANY and SOME: These functions provide similar functionality to IN() but are often used in conjunction with comparison operators.

EXISTS: The EXISTS function checks for the existence of rows returned by a subquery and can sometimes be a more efficient alternative to IN() for subquery comparisons.

JOIN: For complex relationships, using JOIN operations might be more efficient than IN() in some scenarios.

9. What are the benefits of using the IN() function for data filtering?

The IN() function is beneficial for:

Simplifying query conditions involving multiple values.

Enhancing the clarity and readability of SQL statements.

Allowing dynamic and flexible data filtering, especially in parameterized queries and subqueries.

Slideshow of MySQL Comparison Function and Operators

Previous: GREATEST()
Next: INTERVAL()