How To Use SQL UNION OPERATOR With Example Using PHP

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 .

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

Learn How To Use Double Question Mark Operator In PHP
How To Use SQL AND OR,NOT Operators In Statement In PHP
How To Use SQL LIKE Operator With Example Using PHP
How To Use SQL BETWEEN Operator With Example In PHP
How To Use SQL IN Operator In Query With Example In PHP
How To Use SQL UNION OPERATOR With Example Using PHP
How Can I Use Operators In JavaScript With Examples
What Is The Arithmetic Operator In JavaScript
How To Ternary Conditional Operator In Flutter Dart