MySQL IN() function
IN() function
MySQL IN() function finds a match in the given arguments.
This function is useful in -
- It provides a concise and readable way to check if a value matches any value in a list.
- IN() is frequently used in parameterized queries, where a dynamic list of values is passed to a query.
- In some cases, using IN() can lead to better query performance, as MySQL can optimize the execution plan for this type of operation.
- It makes queries more readable and understandable, as it clearly indicates that you're checking if a value is contained within a specified list.
- It's valuable for filtering data based on specific criteria, such as retrieving records for a specific set of IDs or names.
- It's often used in subqueries where you want to filter results based on a set of values returned by an inner query.
- When dealing with enumerated data, IN() is handy for retrieving records that fall within certain categories.
Syntax:
expr IN (value,...)
The function returns 1 if expr is equal to any of the values in the IN list, otherwise, returns 0. 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
Example: MySQL IN() function
The following MySQL statement will return 1 because the specified value is within the range of values.
Code:
SELECT 10 IN(15,10,25);
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.
Code:
SELECT book_name,dt_of_pub,no_page
FROM book_mast
WHERE no_page IN (300,400,500);
Relational Algebra Expression:
Relational Algebra Tree:
Sample table: book_mast
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)
Slideshow of MySQL Comparison Function and Operators
Previous: GREATEST()
Next: INTERVAL()
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics