Posted By
Bajarangi soft ,
Posted On 18-09-2020
SQL JOINs
The SQL JOINs clause is used to combine records from two or more tables . A JOIN is a means for combining fields from Different tables by using common names
Different Types of SQL JOINs
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Let's Learn.
SQL INNER JOIN
Syntax
SELECT column_name list
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name list
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
1.Create database and tables
here we have Course table and Student table to join courseId and fetching Course Name and Student Name from both 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 |
Example(1) for two tables
SELECT course.CourseID, student.StudentName, course.CourseCode
FROM course
INNER JOIN student ON course.CourseID=student.CourseID
Selecting 'CourseID' by the refers of 'Course table ' and 'StudentName' by the refers of 'student table' and 'CourseCode ' by the refers of 'Course table' and from course table inner joining 'Student table' on 'courseID ' and 'Student table CourseID'.
2.Complete code of SQL INNER JOIN in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL INNER 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 INNER 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 course.CourseID, student.StudentName, course.CourseCode
FROM course
INNER JOIN student ON course.CourseID=student.CourseID";
echo "<h2 class='text-danger'>SQL INNER JOIN - combining course table and student table using SQL Joins </h2>";
if($result_1 = mysqli_query($connect, $sql_1)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>CourseID</th>
<th>StudentName</th>
<th>CourseCode</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>".$row['CourseID'] /*course table*/ ."</td>
<td>".$row['StudentName'] /*student table*/ ."</td>
<td>".$row['CourseCode'] /*course table*/ ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
</div>
<br>
</div>
</body>
</html>