How To Use SQL UNION OPERATOR With Example Using PHP

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

UNION operator is used to combine the data of two or more SELECT statements and fetch the data .

SQL UNION Operator

Syntax for SQL UNION Operator.

SELECT column_name list FROM table1
UNION
SELECT column_name list FROM table2;​​​
and
SELECT column_name list FROM table1
UNION ALL
SELECT column_name list FROM table2;

The columns used in both the select statements must be in same order and datatype.
The Union operator provides distinct values in the result set, to fetch the duplicate values too UNION ALL must be used instead of just UNION.

Lets Learn.
1.Create database and two tables 
Student 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.
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 UNION Operator  for both the  table.

Example(1)
SELECT CourseID FROM student UNION SELECT  CourseID FROM course ORDER BY CourseID

selecting courseid from course table and student table and making UNION  between selected columns and fetching data

2.Complete code of SQL UNION Operator  in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
    <title>SQL UNION 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 UNION 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 CourseID FROM student UNION SELECT  CourseID FROM course ORDER BY CourseID";

        echo "<h2 class='text-danger'> SQL UNION Operator,</h2>";
        if($result_1 = mysqli_query($connect, $sql_1)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>CourseID</th>
                
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   
                   <td>".$row['CourseID']."</td>      
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }


        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post