How To Use SQL ANY AND ALL STATEMENT With Example In PHP

Posted By Bajarangi soft , Posted On 18-09-2020

The ANY and ALL operators returns true if any of the subquery values match the condition and ANY and ALL operators used in where and having clause SQL statement

SQL ANY and ALL Operators

Syntax for SQLANY Operators:

SELECT column_name list
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Syntax for SQL ALL Operators:

SELECT column_name list
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Lets Learn.
1.Create database and table.
Student table.

StudentID	StudentName	Address	Branch	Percentage	Fee	CourseID
1	Liam	23 Andrew road	ISE	66	66000	5
2	Andrew	2415 Anthony market str	ISE	80	65000	6
4	Ryan	18 Christopher sq.	ME	67	70000	1
5	Charles	878 Eli Berguvsvägen 8	ECE	52	75000	8
8	Anikth	23 Andrew road	Civil	66	66000	3
9	Andrew	2415 Anthony market str	Civil	80	65000	3
10	Bhavya	18 Christopher sq.	Civil	67	7000	3

Course table.

CourseID	CourseName	CourseCode
1	Mechanical	ME20
2	Chemical	C20
3	Civil	CL20
4	Electrical	E20

We have student table and course table to perform SQL ANY and ALL Operators:

Example(1)

SELECT `StudentName`,`Branch` FROM student WHERE `CourseID` = ANY (SELECT CourseID FROM course WHERE CourseID = 3)

selecting studentName,Branch from student table where any operator find any record in the course table return true if its equal to 3

Example(2)

SELECT StudentName FROM student WHERE CourseID = ALL (SELECT CourseID FROM course WHERE CourseID= 5)

selecting studentName,Branch from student table where all operator find any record in the course table return true if its equal to 5 and it will fetch all rows because not ALL records in the course table has courseid= 5.

2.Complete code of SQL ANY and ALL Operators in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL ANY and ALL Operators</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 ANY and ALL Operators</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 `StudentName`,`Branch` FROM student WHERE `CourseID` = ANY (SELECT CourseID FROM course WHERE CourseID = 3)";
        $sql_2 = "SELECT StudentName FROM student WHERE CourseID = ALL (SELECT CourseID FROM course WHERE CourseID= 5)";


        echo "<h4 class='text-danger'>Example(1)</h4>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                      <th>Branch</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['Branch'] /*student table*/ ."</td> 
                
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        echo "<h4 class='text-danger'>Example(2) </h4>";
        if($result_1 = mysqli_query($connect, $sql_2)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                   </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
       ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post