LEFT JOIN in MySQL

In the last article we saw what inner 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, Left join is slightly different. When left join is used for retrieving data from multiple tables, all the records from the Left tables are retrieved while only those records from the right 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 LEFT join to retrieve data from both the columns, all the records from the patient table will be retrieved and only those records from department 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.

 

   






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

$query = "SELECT patient.patient_fname, patient.patient_age, department.dep_name ".
    "FROM patient LEFT 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:

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

<<< INNER JOIN in MySQLRIGHT JOIN In MySQL >>>