SQL FULL OUTER JOIN Explained [Practical Examples]

Overview of SQL FULL OUTER JOIN

SQL FULL OUTER JOIN is used to retrieve all records from both joined tables left table and right table, it combine the functionality of left outer join and right outer join

A FULL OUTER JOIN returns unmatched rows from both tables as well as the overlap between them. When no matching rows exist for a row in the left table, the columns of the right table will have NULLs for those records. Similarly, when no matching rows exist for a row in the right table, the columns of the left table will have NULLs

 

Syntax of SQL FULL OUTER JOIN

SELECT column1,column2…column(n)
FROM left_table_name
FULL JOIN | FULL OUTER  JOIN  right_table_name
ON left_table.column_name = right_table.column_name
[where condition]
[order by column1,column2..];

Here,

• column1,column2…column(n) : Specify each column name separated by comma from both the tables
• left_table_name : It is the name of table of which records will be matched with right table,
• Right_table_name : It is the name of table from which the data will be retrieved
• FULL  JOIN|FULL OUTER JOIN  : It is the keyword used to join two tables using FULL join, we can specify either FULL JOIN or FULL OUTER JOIN keyword to perform right joining
• [Where condition] : It is optional argument, used to retrieve records conditionally
• [Order by column1, column2..] : It is optional argument, specified to display the resulting records in the specified order of column value

 

Examples of SQL FULL OUTER JOIN

Consider hospital management database with four tables’ patient, doctor, laboratory, bill for performing practical examples

Patient Table

Doctor Table

Bill Table

Laboratory Table

 

SQL FULL OUTER JOIN with two tables

Example 1: Write SQL query to display patient information with their doctor details

SELECT  patient.patient_id AS 'Patient ID', patient.name AS 'Patient Name', patient.age AS 'Patient Age', patient.gender AS 'Patient Gender', patient.address AS 'Patient Address', patient.disease AS 'Patient Disease', patient.doctor_id, doctor.name AS 'Doctor Name', doctor.age AS 'Doctor Age', doctor.address AS 'Doctor Location' FROM  patient 
FULL OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id

• In the above query, SQL FULL JOIN is used to retrieve combine records from both table patient and doctor
• In the output we will get NULL in the patient data for those doctors who does not have any patient

OUTPUT:

 

SQL FULL OUTER JOIN with Three tables

Example 2 : Write SQL query to retrieve all patient information , all doctor’s name and billing details

SELECT  patient.patient_id, patient.name AS 'Patient Name', doctor.name AS 'Doctor Name', patient.disease AS 'Disease', bill.doctor_charge AS 'Doctor Charge', bill.room_charge AS 'Room Charge', bill.amount AS 'bill amount' FROM  doctor 
FULL OUTER JOIN patient ON patient.doctor_id = doctor.doctor_id 
FULL OUTER JOIN bill ON patient.patient_id = bill.patient_id

• In the above query, SQL FULL OUTER JOIN is used join three tables to retrieve combine record set of all records from three tables patient, doctor and bill
• SQL FULL OUTER JOIN joining three tables based on common column , if the matching records for a common column does not exist, SQL returns NULL value

OUTPUT:

 

SQL FULL OUTER JOIN on multiple tables with Where Clause

Example 3: Write SQL query to display male patient data whose age is more than 30 years with their lab report details and doctor name

SELECT  patient.patient_id, patient.name AS 'Name', doctor.name AS 'Doctor name', patient.age, patient.gender, patient.address, patient.disease, doctor.address AS 'Doctor Address', laboratory.lab_no, laboratory.date,  laboratory.amount FROM  laboratory 
FULL OUTER JOIN patient ON patient.patient_id = laboratory.patient_id 
FULL OUTER JOIN doctor ON laboratory.doctor_id = doctor.doctor_id
WHERE (patient.gender = 'male') AND (patient.age > 30)

• In above query , SQL FULL OUTER JOIN is used to join two tables and retrieve records from both the tables based on conditions and common column value
• The output contains one record which stratified two condition specified with SQL where clause and retrieve data from all three tables

OUTPUT:

 

Example 4 : Write SQL query to display all billing details of patient with patient personal information if number of admitted days is more than 5

SELECT  patient.patient_id, patient.name, patient.age, patient.gender, patient.address, patient.disease, patient.doctor_id, bill.bill_no, bill.doctor_charge, bill.room_charge, bill.no_of_days, bill.amount FROM  patient 
FULL OUTER JOIN bill ON patient.patient_id = bill.patient_id
WHERE  (bill.no_of_days > 5)

• In the above query, SQL FULL OUTER JOIN is applied to retrieve combine records from two tables patient and bill
• SQL where clause is used to check for the condition as no.of admitted days must be greater than 5

OUTPUT:

 

SQL FULL OUTER JOIN on multiple tables with Group by Clause

SQL Right Outer Join allow us to use Group by clause along with aggregate functions to group records having the same value for specified column

Example 5 : Write SQL query to count total number of patient of each doctor by combining patient and doctor able records

SELECT patient.doctor_id, COUNT(patient.patient_id) AS 'Total Patient' FROM  patient 
FULL OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id
GROUP BY patient.doctor_id

• In the above query, SQL FULL OUTER JOIN is used to join two tables patient and doctor to retrieve records from both the tables
• SQL group by clause is used to make a group of same doctor_id in patient table , and count aggregate function is used to make total of patient of each doctor id group

OUTPUT:

 

SQL FULL OUTER JOIN Using Union Clause

If the database doesn’t allow FULL JOIN (MySQL doesn’t), we can combine LEFT OUTER JOIN and RIGHT OUTER JOIN using Union clause to get the same result of SQL FULL OUTER JOIN

Example 6: Write SQL query to retrieve combine records from patient and doctor

SELECT  patient.patient_id AS 'Patient ID', patient.name AS 'Patient Name', patient.age AS 'Patient Age', patient.gender AS 'Patient Gender', patient.address AS 'Patient Address', patient.disease AS 'Patient Disease', patient.doctor_id, doctor.name AS 'Doctor Name', doctor.age AS 'Doctor Age', doctor.address AS 'Doctor Location' FROM  patient 
LEFT OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id
UNION
SELECT patient_1.patient_id AS 'Patient ID', patient_1.name AS 'Patient Name', patient_1.age AS 'Patient Age', patient_1.gender AS 'Patient Gender', patient_1.address AS 'Patient Address', patient_1.disease AS 'Patient Disease', patient_1.doctor_id, doctor_1.name AS 'Doctor Name', doctor_1.age AS 'Doctor Age', doctor_1.address AS 'Doctor Location' FROM  patient AS patient_1 
RIGHT OUTER JOIN doctor AS doctor_1 ON patient_1.doctor_id = doctor_1.doctor_id

• In the above query , UNION clause is used combine two SQL select query to perform SQL FULL OUTER JOIN
• First SQL select query use SQL LEFT OUTER JOIN to join patient and doctor table in which patient is the left table
• Second SQL select query used SQL RIGHT OUTER JOIN to join patient and doctor table in which doctor is the right table

OUTPUT:

 

SQL FULL OUTER JOIN Using Union Clause & Where condition

MySQL does not support FULL OUTER JOIN so to perform FULL OUTER JOIN we can use UNION clause between two SQL select query of LEFT OUTER JOIN and RIGHT OUTER JOIN, to conditionally retrieve records in MySQL using UNION clause we can used SQL Where clause

Example 7: Write SQL query to retrieve all patient records having age more than 50 years with all doctors’ details using Union clause

SELECT  patient.patient_id AS 'Patient ID', patient.name AS 'Patient Name', patient.age AS 'Patient Age', patient.gender AS 'Patient Gender', patient.address AS 'Patient Address', patient.disease AS 'Patient Disease', doctor.name AS 'Doctor Name', doctor.age AS 'Doctor Age', doctor.address AS 'Doctor Location' FROM  patient 
LEFT OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id
WHERE (patient.age > 50)
UNION
SELECT  patient_1.patient_id AS 'Patient ID', patient_1.name AS 'Patient Name', patient_1.age AS 'Patient Age', patient_1.gender AS 'Patient Gender', patient_1.address AS 'Patient Address', patient_1.disease AS 'Patient Disease', doctor_1.name AS 'Doctor Name', doctor_1.age AS 'Doctor Age', doctor_1.address AS 'Doctor Location' FROM  patient AS patient_1 
RIGHT OUTER JOIN doctor AS doctor_1 ON patient_1.doctor_id = doctor_1.doctor_id 
WHERE (patient_1.age > 50)

• In the above query, SQL Union clause is use to combine result set of two select query of LEFT OUTER JOIN and RIGHT OUTER JOIN
• First SQL select query use SQL LEFT OUTER JOIN to join patient and doctor table in which patient is the left table, SQL where clause is applied with age column to check for condition
• Second SQL select query used SQL RIGHT OUTER JOIN to join patient and doctor table in which doctor is the right table, SQL where clause is applied with age column to check for condition

OUTPUT:

 

Summary

In this article of SQL FULL OUTER JOIN, we have covered overview of SQL FULL OUTER JOIN with syntax and explanation of each syntax argument, we have discuss practical examples of SQL FULL OUTER JOIN starts with two tables, multiple tables, SQL FULL OUTER JOIN with where condition, SQL FULL OUTER JOIN with Group by clause, we have also covered examples of performing FULL JOIN in MySQL using Union clause with two practical examples

 

References

SQL OUTER JOIN

 

Read More

SQL Outer Join