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:


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

Relational Algebra Tree:

Relational Algebra Tree: MySQL IN() function.

Slideshow of MySQL Comparison Function and Operators

Previous: GREATEST()
Next: INTERVAL()



Follow us on Facebook and Twitter for latest update.