MySQL IN() function

Last update on April 04 2022 13:09:26 (UTC/GMT +8 hours)

IN() function

MySQL IN() function finds a match in the given arguments.

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: 5.6

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

Sample 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

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

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example1-in-function- php MySQL examples | w3resource</title>
<meta name="description" content="example1-in-function- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of books whose number of pages are either 300 or 400 or 500 along with their date of publish and number of pages:</h2>
<table class='table table-bordered'>
<tr>
<th>Book</th><th>Date of publish</th><th>Number of pages</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT book_name,dt_of_pub,no_page
FROM book_mast
WHERE no_page IN (300,400,500)') as $row) {
echo "<tr>";
echo "<td>" . $row['book_name'] . "</td>";
echo "<td>" . $row['dt_of_pub'] . "</td>";
echo "<td>" . $row['no_page'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example1-in-function</title>
</head>
<body>
<%
try {
Class.forName("com.MySQL.jdbc.Driver").newInstance();
String Host = "jdbc:MySQL://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT book_name,dt_of_pub,no_page FROM book_mast WHERE no_page IN (300,400,500)";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Book</td>
<td>Date of publish</td>
<td>Number of pages</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("book_name")%></TD>
<TD><%=rs.getString("dt_of_pub")%></TD>
<TD><%=rs.getString("no_page")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Online Practice Editor:

Slideshow of MySQL Comparison Function and Operators

Previous: GREATEST()
Next: INTERVAL()