Table of Contents
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
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
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
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
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
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
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
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
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