SQL Left Join Multiple Tables [Easy Examples]

SQL left Join Multiple Tables - Overview

A left outer join designates the leftmost table as the required table. a LEFT JOIN in SQL returns all rows from the left table and only the matched rows from the right.

In SQL Left join, if a specific row is present in the left table but not in the right, the result will include this row but with a NULL value in each column from the right. If a record from the right table is not in the left, it will not be included in the result.

 

SQL Inner join vs SQL Left Outer Join

The INNER JOIN pertains to only elements with the join condition(s) existing in both tables joined. LEFT JOIN (or LEFT OUTER JOIN) takes all rows in the LEFT table along with matching results in the right table.

 

Points to be noted for SQL Left Join multiple Tables

More than two tables can be joined in a single SQL Statement. The maximum number of tables that can be joined in one SQL statement depends on the RDMS being used. But a join with, say more than 4 or 5 tables, is not a good idea. The order of magnitude and complexity of the join, is determined by the number of tables specified in the FROM clause.

In left Outer join the order in which we specified tables is more importance ,the tables from which we need to fetch all records that must be mentioned as left most tables that is starting table.

SQL LEFT JOIN operation is cascades for all joins within a query that means If you use a LEFT JOIN, often the subsequent tables should also be left-joined

 

SQL Left Join multiple tables Syntax

Here, Table1 would be consider as a left table and Table2 would be consider as a right table. similarly for second left join Table2 is considered as left table for table 3 , Table1.column1, Table1.column2, Table2.column1, are the name of the columns which you want to retrieve separated by comma

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2,Table3.column1,.. 
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.columnname=Table2.columnname 
LEFT OUTER JOIN Table3 ON Table2.columnname=Table3.columnname..

 

SQL Left Outer Join multiple tables

Consider following hospital database with four tables for practical examples
Patient Table

Doctor Table

Bill Table

Laboratory Table

 

SQL Left Join with three tables example

To get the combine records  of patients, doctors and their laboratory use SQL Left join multiple tables

SELECT dbo.patient.patient_id, dbo.patient.name AS 'Name', dbo.doctor.name AS 'Doctor name', 
dbo.patient.age, dbo.patient.gender, dbo.patient.address, dbo.patient.disease, dbo.doctor.address AS 'Doctor Address', 
dbo.doctor.city, dbo.laboratory.lab_no, dbo.laboratory.date, dbo.laboratory.amount 
FROM   dbo.patient 
LEFT OUTER JOIN dbo.laboratory ON dbo.patient.patient_id = dbo.laboratory.patient_id 
LEFT OUTER JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 

• The above Left Outer join on three tables query returns resulting set of all records from patient table patient id, patient name, doctor name, patient age, patient gender , patient address , patient diseases , and matching records from doctor table and laboratory table that is doctor address, doctor city with lab no, lab report date and lab report amount
• all three tables are joined by common column condition with ON keyword

 

OUTPUT:

 

SQL Left Join multiple tables with WHERE Clause

To display records of female patient whose has laboratory bill amount more than 300rs with their doctor details

SELECT dbo.patient.patient_id, dbo.patient.name AS 'Name', dbo.doctor.name AS 'Doctor name',
dbo.patient.age, dbo.patient.gender, dbo.patient.address, dbo.patient.disease, dbo.doctor.address AS 'Doctor Address', 
dbo.doctor.city, dbo.laboratory.lab_no, dbo.laboratory.date, dbo.laboratory.amount 
FROM  dbo.patient LEFT OUTER JOIN dbo.laboratory ON dbo.patient.patient_id = dbo.laboratory.patient_id  
LEFT OUTER JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 
WHERE (dbo.patient.gender = 'female') AND (dbo.laboratory.amount > 300) 

• To fetch rows from all three tables LEFT OUTER JOIN is used to connect patient, doctor and laboratory tables
• To retrieve records of all female patients whose has bill amount more than 300 we applied condition where clause statement on gender column and amount column
• To fulfil both condition at  the same time for each record relation AND operator is used between conditions

OUTPUT:

 

SQL Left Join multiple tables with WHERE Clause having three conditions

To fetch patient, doctor data with laboratory details having bill amount more than 500, where patients from Vapi city and doctors from Surat city

SELECT  dbo.patient.patient_id, dbo.patient.name AS 'Name', dbo.doctor.name AS 'Doctor name', 
dbo.patient.age, dbo.patient.gender, dbo.patient.address AS 'Patient City', dbo.patient.disease, 
dbo.doctor.city AS 'Doctor City',  dbo.laboratory.lab_no, dbo.laboratory.date, dbo.laboratory.amount 
FROM dbo.patient LEFT OUTER JOIN dbo.laboratory ON dbo.patient.patient_id = dbo.laboratory.patient_id 
LEFT OUTER JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 
WHERE (dbo.laboratory.amount > 500) AND (dbo.doctor.city = 'Surat') AND (dbo.patient.address = 'Vapi')

• To join three tables patient, doctor and laboratory tables LEFT OUTER JOIN is used
• In previous query we have given three conditions on SQL Select statement with where clause

OUTPUT:

 

SQL Left Join multiple tables with GROUP BY Clause

SQL Left Outer Join allow us to use Group by clause along with aggregate functions to group rows with specified column name.

Use SQL left join to count number of patient and doctor city wise use group by clause to make group of each city patient and doctor

SELECT  TOP (100) PERCENT dbo.doctor.city, COUNT(dbo.doctor.doctor_id) AS Doctor, COUNT(dbo.patient.patient_id) AS patient 
FROM  dbo.doctor LEFT OUTER JOIN dbo.patient ON dbo.patient.doctor_id = dbo.doctor.doctor_id 
GROUP BY dbo.doctor.city 
ORDER BY dbo.doctor.city DESC 

• In above query, SQL Left join is used to join doctor and patient table
• Group by clause is used to make group of patient and doctors city wise
• The result shows each city wise count of patient and doctors

OUTPUT: 

To display Minimum and Maximum doctor’s Charges, Minimum and Maximum Room charges for each patient use SQL Left join multiple tables

SELECT dbo.patient.name, MIN(dbo.bill.doctor_charge) AS 'Minimum Doctor Charge', 
MAX(dbo.bill.doctor_charge) AS 'Maximum Doctor Charge', MIN(dbo.bill.room_charge) AS 'Minimum Room Charge', 
MAX(dbo.bill.room_charge) AS 'Maximum Room Charge' 
FROM dbo.patient LEFT OUTER JOIN 
dbo.bill ON dbo.patient.patient_id = dbo.bill.patient_id 
GROUP BY dbo.patient.name 

• In previous query, SQL Left outer join is applied multiple tables ,patient and bill tables to fetch all patient name with there room and doctor min and max charges, min and max aggregate function is used
• If the patient is not admitted in hospital and their record is not available in bill room charges than the value in min, max function will be NULL for that particular record

 OUTPUT: 

 

SQL Left Join multiple tables with GROUP BY & HAVING Clause

HAVING Clause always utilized in combination with GROUP BY Clause. HAVING Clause restricts the data on the group records rather than individual records. WHERE and HAVING can be used in a single query of SQL Left Join multiple tables

HAVING Clause returns the grouped records which match the given condition. You can also sort the grouped records using ORDER BY. ORDER BY used after GROUP BY on aggregated column.

SQL Left outer join with group by having clause on three tables example

To retrieve doctor name with total number of patient and minimum lab charges per doctor if the lab charges is more than 200

SELECT  dbo.doctor.name AS 'Doctor Name', COUNT(*) AS 'Total Patient', MIN(dbo.laboratory.amount) AS 'Minimum lab Charges' 

FROM  dbo.laboratory LEFT JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 
LEFTJOIN dbo.patient ON dbo.laboratory.patient_id = dbo.patient.patient_id 

GROUP BY dbo.doctor.name 

HAVING (MIN(dbo.laboratory.amount) > 200) 

• In above SQL left join multiple tables query, we first connect three tables patient, doctor and laboratory with left outer join
• To make group of records of each doctor Group By doctor name is applied also to count number of patients under each doctor count (*) aggregate function is used, to find minimum laboratory charges doctor wise MIN function on laboratory amount is applied

OUTPUT:

 

SQL LEFT OUTER JOIN on four tables with GROUP BY HAVING clause example

In following example four SQL tables are join with Left Outer Join to fetch records of each doctor with count of patient, Minimum lab charges and Minimum doctor charges for each doctor having Minimum lab charges more than 100 and minimum doctor charges less than 500

SELECT dbo.doctor.name AS 'Doctor Name', COUNT(*) AS 'Total Patient', MIN(dbo.laboratory.amount) AS 'Minimum lab Charges', 
MIN(dbo.bill.doctor_charge) AS 'Minimum Doctor Charges'
FROM   dbo.laboratory LEFT OUTER JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 
LEFT OUTER JOIN dbo.patient ON dbo.laboratory.patient_id = dbo.patient.patient_id 
LEFT OUTER JOIN dbo.bill ON dbo.patient.patient_id = dbo.bill.patient_id
GROUP BY dbo.doctor.name
HAVING (MIN(dbo.laboratory.amount) > 100) AND (MIN(dbo.bill.doctor_charge) < 500)

• In above SQL Left join multiple tables query, four tables patient, doctor, bill and laboratory are joined with Left outer join on condition of common columns
• To find records doctor wise Group by doctor name is applied, for condition retrieval Having clause is used to check lab amount more than 100 and doctor charge more than 500

OUTPUT:

 

Summary

In the tutorial Article, SQL Left Outer Join multiple tables have covered with practical examples, starts with introduction of SQL left join, difference between inner join and left join, left join multiple tables has covered, also left join on three tables with SQL Where clause, SQL left join on four tables with Group by clause and having clause multiple examples has been covered in last section of article

 

References

SQL Left join with practical examples
SQL Group by & order by Statement with examples

 

Further Reading

Joins (SQL Server)
LEFT OUTER JOIN operation