IN Operator in MySQL

Consider a scenario where you want to retrieve records of all the patients whose age is 30, 35, or 40. To do so, you can use three WHERE statements with OR operator between them. However there is another more concise and effective approach to achieve these results. You can use IN operator to select records of patients where patient_age is equal to certain values. The syntax of IN operator is as follows:

SELECT column1, column2, column3 
FROM table
WHERE column1 IN (value1, value2, value3 ....)

Using IN Operator in Object Oriented MySQLi

To select filtered data using IN operator via MySQLi, pass the query for selecting filtered data to the query function of the mysqli object. The following example demonstrates this concept.




Using IN Operator in Object Oriented Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using IN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age IN (30, 33, 40)";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 30, 33 or 40.

Using IN operator in Procedural MySQLi

Selecting filtered table data via IN operator in procedural MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.




Using IN Operator in Mysqli



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Using IN Operator

$query = "SELECT patient_fname, patient_age ".
"FROM Patient ".
"WHERE patient_age IN (23, 35, 42)";
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
";
    }
} else {
    echo "0 results";
}

$connection->close();
?>

Download the code

The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 23, 35 or 42.

<<< Select Unique Data via Distinct In MySQLUsing BETWEEN operator in MySQL >>>