How To Use SQL EXISTS STATEMENT With Example In PHP

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

EXISTS operator in SQL is used to check whether a subquery fetches at least one row. EXISTS Operator return true .if the subquery returns one or more rows or else return false

Syntax SQL EXISTS Operator:

SELECT column_name list
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

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

StudentID	StudentName	ParentsContact	Address	Country	Section	Branch	Percentage	Fee	CourseID
1	Liam	10101010	23 Andrew road	USA	A	ISE	66	66000	5
2	Andrew	20202020	2415 Anthony market str	Germany	A	ISE	80	65000	6
4	Ryan	40404040	18 Christopher sq.	INDIA	B	EE	67	70000	5
5	Charles	50505050	878 Eli Berguvsvägen 8	UK	C	ECE	52	75000	8
8	Anikth	10101010	23 Andrew road	AFRICA	A	CSE	66	66000	3
9	Andrew	20202020	2415 Anthony market str	Germany	A	ISE	80	65000	2
10	Bhavya	40404040	18 Christopher sq.	INDIA	B	CE	67	7000	2

Course table.

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

We use student table and course table for EXISTS operator.

Example(1)

SELECT StudentName FROM student WHERE EXISTS (SELECT CourseID FROM course WHERE course.CourseID = student.CourseID AND Percentage < 80)

Selecting studentname from student tabel and exists operator check for courseid in student and course table and list the students with a percentage is less than 80

Example(2)

SELECT StudentName,Branch FROM student WHERE EXISTS (SELECT CourseName,CourseID FROM course WHERE student.CourseID = course.CourseID AND Percentage =52)

Selecting studentname,branch from student tabel and exists operator check for courseid in student and course table and list the student ,who's percentage is equal to 52

2.Complete code of EXISTS operator in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>How to use SQL EXISTS 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 EXISTS 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 StudentName FROM student WHERE EXISTS (SELECT CourseID FROM course WHERE course.CourseID = student.CourseID AND Percentage < 80)";
        $sql_2 = "SELECT StudentName,Branch FROM student WHERE EXISTS (SELECT CourseName,CourseID FROM course WHERE student.CourseID = course.CourseID AND Percentage =52)";


        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>
                    
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*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>
                     <th>Branch</th>
                
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['Branch'] ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
       ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post