PHP MySQL tutorial

RIGHT JOIN In MySQL

In the previous articles we saw what inner joins and left joins are how they are used. Inner joins retrieve data from multiple tables for only those records where there is a common value in a specific field. However, right joins are opposite of left joins. When right join is used for retrieving data from multiple tables, all the records from the right table are retrieved while only those records from the left table are retrieved where there is a common value in the column on which Join is being implemented.

This might sound complex at first. Lets again have a look at our patient and department table.

Patient Table

 

patient_id patient_fname patient_lname patient_age dep_id
1 Mike Getting 20 3
2 Sara Taylor 25 2
3 Vince James 52 9
4 Shawn Been 45 6
5 Lara Bran 43 7
6 Fred Vicks 8 5
7 Pam Beesly 38 8

 

Department Table

 

dep_id dep_name dep_capacity
2 Neurology 200
3 Cardiology 300
4 Nephrology 200
5 Children 100
6 Pythology 400
7 Optician 500

 

You can see from the ‘Patient’ and ‘Department’ tables that they both have dep_id field common, so now if we use RIGHT join to retrieve data from both the columns, all the records from the department table will be retrieved and only those records from patient table will be retrieved where there exist common values for dep_id in both the ‘Patient’ and ‘Department’ tables. Have a look at the following examples.

 

   


Implementing RIGHT JOIN



connect_error) {
    die("Connection not established: " . $connection->connect_error);
} 
    
$query = "Use Hospital;";
$connection->query($query);
    
// Implementing RIGHT JOIN

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient RIGHT JOIN department ".
    "ON patient.dep_id = department.dep_id";
     
$output = $connection->query($query);

if ($output->num_rows > 0) {
    // output data of each row
    while($row = $output->fetch_assoc()) {
        echo "First Name: " . $row["patient_fname"]. " - Age: " . $row["patient_age"]. " - Department: " . $row["dep_name"]. "
"; } } else { echo "0 results"; } $connection->close(); ?>

Download the code

The output of the above code will look like this.

First Name: Mike – Age: 20 – Department: Cardiology
First Name: Sara – Age: 25 – Department: Neurology
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children
First Name: – Age: – Department: Nephrology


It can be seen that all the records have been retrieved from department table while  only those columns have been retrieved from patient table where there was a common value for dep_id column in both the tables.

<<< LEFT JOIN in MySQLImplementing FULL Join in MySQL >>>
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .