Posted By
Bajarangi soft ,
Posted On 18-09-2020
SQL IN Operator
The IN operator allows to test multiple values in a WHERE clause.IN operator is a shorthand for multiple OR conditions.
SELECT column_name list
FROM table_name
WHERE column_name IN (value1, value2, ...);
or
SELECT column_name list
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Let's Learn.
1.Create database and table
Student table.
here we have student table for this table we use SQL IN Operator.
| StudentID | StudentName | Address | DOB | Section | Branch | Percentage | Fee |
|---|---|---|---|---|---|---|---|
| 1 | Anikth | 23 Andrew road | 1995-04-18 | A | CSE | 66 | 66000 |
| 2 | Andrew | 2415 Anthony market str | 1996-08-20 | A | ISE | 80 | 65000 |
| 4 | Bhavya | 18 Christopher sq. | 1996-07-13 | B | EE | 67 | 70000 |
| 5 | Charles | 878 Eli Berguvsvägen 8 | 1995-05-20 | C | ECE | 52 | 75000 |
| 8 | Rajesh | 23 Andrew road | 1995-02-03 | A | CL | 65 | 66000 |
| 9 | Daniel | 2415 Anthony market str | 1995-09-10 | A | ME | 80 | 65000 |
| 10 | Jackson | 18 Christopher sq. | 1995-10-17 | B | EEE | 63 | 70000 |
| 11 | Connor | 878 Eli Berguvsvägen 8 | 1995-11-11 | C | ECE | 52 | 75000 |
| 12 | Kiran | banglore | 1996-12-26 | C | ME | 68 | 68500 |
Example(1)
SELECT * FROM Student WHERE Branch IN ('ISE', 'ME', 'CSE');
Selecting branch which ISE,ME and CSE in Branch column from student table using SQL IN Operator
Example(2)
SELECT * FROM Student WHERE Branch NOT IN ('ISE', 'ME', 'CSE')
Selecting branch which NOT ISE,ME and CSE in Branch column from student table using SQL IN Operator
2.Complete code of SQL IN Operator in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL IN Operator</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="text-center">
<h1>SQL IN Operator</h1>
</div>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql_1 = "SELECT * FROM Student WHERE Branch IN ('ISE', 'ME', 'CSE');";
$sql_2= "SELECT * FROM Student WHERE Branch NOT IN ('ISE', 'ME', 'CSE')";
echo "<h2 class='text-danger'>SQL IN Operator->Selected multiple values </h2>";
if($result_1 = mysqli_query($connect, $sql_1)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
<th>Section</th>
<th>Branch</th>
<th>Percentage</th>
<th>Fee</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>" . $row['StudentID'] . "</td>
<td>".$row['StudentName'] ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
<td>".$row['Section'] ."</td>
<td>".$row['Branch'] ."</td>
<td>".$row['Percentage'] ."</td>
<td>".$row['Fee'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo"<br>";
echo "<h2 class='text-danger'>SQL IN Operator->Removed multiple values</h2>";
if($result_2 = mysqli_query($connect, $sql_2)){
if(mysqli_num_rows($result_2) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
<th>Section</th>
<th>Branch</th>
<th>Percentage</th>
<th>Fee</th>
</tr>
";
while($row = $result_2->fetch_assoc()){
echo "<tr>
<td>" . $row['StudentID'] . "</td>
<td>".$row['StudentName'] ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
<td>".$row['Section'] ."</td>
<td>".$row['Branch'] ."</td>
<td>".$row['Percentage'] ."</td>
<td>".$row['Fee'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
</div>
<br>
</div>
</body>
</html>