How To Use FULL OUTER JOIN Query With Example Using PHP

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

FULL OUTER JOIN returns all rows from the left table and from the right table and combines the data of both LEFT and RIGHT joins.

Syntax for SQL FULL OUTER JOIN

SELECT column_name list
FROM table1
FULL JOIN table2
ON table1.column_name=table2.column_name;

or
SELECT column_name list
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Lets Learn.
1.Create database and two tables
Student table.(left table)

StudentID	StudentName	Address	Section	Branch	Percentage	CourseID
1	Liam	23 Andrew road	A	CSE	66	5
2	Andrew	2415 Anthony market str	A	ISE	80	5
5	Charles	878 Eli Berguvsvägen 8	C	ECE	52	9
9	Andrew	2415 Anthony market str	A	CE	80	2
10	Bhavya	18 Christopher sq.	B	CE	67	9
12	Rajesh	878 Eli Berguvsvägen 8	C	CL	52	3
13	Daniel	878 Eli Berguvsvägen 8	ME	CSE	52	8
15	Connor	878 Eli Berguvsvägen 8	C	ME	52	1
16	Kiran	878 Eli Berguvsvägen 8	ME	ECE	52	10

Course table.(right table)

CourseID	CourseName	CourseCode
1	Mechanical	ME20
2	Chemical	C20
3	Civil	CL20
4	Electrical	E20
5	Computer	CS20
6	Information	IS20
7	Electrical Electronics	EE20
8	Electrical Communication	ECE20

Now we do SQL FULL OUTER JOIN for both the table.

Example(1)

SELECT student.StudentName, course.CourseID
FROM student
FULL OUTER JOIN course
ON student.CourseID=course.CourseID
ORDER BY student.StudentName

FULL JOIN combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from student table and course table. The rows for which there is no matching that will contain NULL values or shows empty cell in table.this can also done by UNION Operator.

Example(2)

SELECT * FROM student 
LEFT JOIN course ON student.CourseID = course.CourseID 
UNION 
SELECT * FROM student 
RIGHT JOIN course ON student.CourseID = course.CourseID

selecting all row from LEFT table and selecting all from RIGHT table and making UNION between two table can get all row from both the table.

2.Complete code of SQL FULL OUTER JOIN in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>SQL FULL OUTER JOIN </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 FULL OUTER JOIN </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 student.StudentName, course.CourseID
                        FROM student
                        FULL OUTER JOIN course
                        ON student.CourseID=course.CourseID
                        ORDER BY student.StudentName";
//        $sql_2 = "SELECT * FROM student                                 // example of union we can also use this statement
//                    LEFT JOIN course ON student.CourseID = course.CourseID
//                    UNION
//                    SELECT * FROM student
//                    RIGHT JOIN course ON student.CourseID = course.CourseID";

        echo "<h2 class='text-danger'>SQL FULL OUTER JOIN </h2>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentName</th>
                     <th>CourseID</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentName'] /*student table*/ ."</td> 
                   <td>".$row['CourseID'] /*Course table*/ ."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post

How To Use SQL INNER JOIN Query With Example In PHP
How To Use SQL LEFT JOIN Query With Example In PHP
How To Use SQL RIGHT JOIN Query With Example In PHP
How To Use FULL OUTER JOIN Query With Example Using PHP
How To Use Inner Join Clause With Example Using Laravel
How To Use Left Join Clause With Example Using Laravel
How To Use Right Join Clause With Example Using Laravel
How To Use Cross Join Clause Statement Using Laravel
How To Join Sub Queries With Example Using Laravel
How to Create a String by Joining the Array Elements using PHP