PHP MySQL tutorial

Implementing FULL Join in MySQL

In the previous articles we saw what inner joins, left joins and right joins are and how they are used. Full joins are simplest of all the joins. Full join retrieves all the records from both the right and left tables irrespective of the similar values in the common column. Consider the example of patient and department table. The Full join will retrieve all the patient records and all the department records, if patient doesnt have a corresponding department, the fields for department columns will remain empty. Vice versa will be the case for department, records. An important point to note here is that unlike MS SQLSERVER and some of the other databases, MySQL doesnt support FULL JOIN directly. Rather, we take right join and left join and then take union of both.

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

 

Let’s implement full join on above two tables and see what result do we get. 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 LEFT JOIN department ".
    "ON patient.dep_id = department.dep_id ".
    "UNION ".
    "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: Vince – Age: 52 – Department:
First Name: Shawn – Age: 45 – Department: Pythology
First Name: Lara – Age: 43 – Department: Optician
First Name: Fred – Age: 8 – Department: Children
First Name: Pam – Age: 38 – Department:
First Name: – Age: – Department: Nephrology


It can be seen that all the records have been retrieved from both the patient and department tables and the fields have been left empty where there isnt any record found in the corresponding tables.

<<< RIGHT JOIN In MySQL
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .