w3resource

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 Expression: MySQL IN() function.

Relational Algebra Tree:

Relational Algebra Tree: MySQL IN() function.

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()



Follow us on Facebook and Twitter for latest update.