Syntax for SQL UNION Operator.
SELECT column_name list FROM table1
UNION
SELECT column_name list FROM table2;
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.
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 |
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 |
SELECT CourseID FROM student UNION SELECT CourseID FROM course ORDER BY CourseID
<!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>