SQL RIGHT OUTER JOIN Explained [Practical Examples]

Overview of SQL RIGHT OUTER JOIN

SQL RIGHT OUTER JOIN is one type of SQL OUTER JOIN  used to retrieve all records from the right table of the join and match records from the left table side of the join, the records for which there is no matching record on the left table, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN

A RIGHT OUTER JOIN designates the rightmost table as the required table, we can use either RIGHT OUTER JOIN or RIGHT JOIN keywords to perform SQL JOIN

 

SQL RIGHT OUTER JOIN Syntax

SELECT column1,column2…column(n)
FROM left_table_name
RIGHT JOIN|RIGHT 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 a comma from both the tables
• left_table_name: It is the name of the table of which records will be matched with the RIGHT table,
• RIGHT_table_name : It is the name of the table from which the data will be retrieved
• RIGHT JOIN|RIGHT OUTER JOIN  : It is the keyword used to join two tables using RIGHT join, we can specify either RIGHT JOIN or RIGHT OUTER JOIN keyword to perform RIGHT joining
• [Where condition] : It is an optional argument, used to retrieve records conditionally
• [Order by column1, column2..] : It is an optional argument, specified to display the resulting records in the specified order of column value

 

Examples of SQL RIGHT OUTER JOIN

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

Doctor Table

Bill Table

Laboratory Table

 

SQL RIGHT OUTER JOIN with two tables

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

SELECT doctor.name AS 'Doctor Name', 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' 
FROM doctor 
RIGHT OUTER JOIN patient ON doctor.doctor_id = patient.doctor_id

• In the above query, SQL RIGHT OUTER JOIN is used to join two table doctor and patient to retrieve patient information with their doctor name
• Table doctor is the left table and patient is on the RIGHT table so all records of the patient table will retrieve

OUTPUT:

 

SQL RIGHT OUTER JOIN with Three tables

Example 2: Write SQL query to retrieve patient information with their doctor 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 
RIGHT OUTER JOIN patient ON patient.doctor_id = doctor.doctor_id 
RIGHT OUTER JOIN bill ON patient.patient_id = bill.patient_id

• In the above query, SQL RIGHT OUTER JOIN is used to join three tables patient, doctor, and bill to combine data of a patient with their billing and doctor details
• Here the patient table  is the RIGHT table whereas bill and doctor is on the left tables

OUTPUT:

 

SQL RIGHT JOIN on multiple tables with Where Clause

Example 3: Write SQL query to display female patient data who has laboratory bill amount more than 400rs with their doctor details

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 
RIGHT OUTER JOIN patient ON patient.patient_id = laboratory.patient_id 
RIGHT OUTER JOIN doctor ON laboratory.doctor_id = doctor.doctor_id WHERE  (patient.gender = 'female') AND (laboratory.amount > 400)

• In the above query, SQL RIGHT OUTER JOIN is used to get combine records from three tables
• Here the patient table is the RIGHT table and laboratory and doctor are the left tables
• SQL where clause is used with a select statement to retrieve records conditionally for patient’s gender as female and laboratory amount must be greater than 400

OUTPUT:

 

SQL RIGHT JOIN on multiple tables with Where Clause having three conditions

Example 4: Write SQL query to retrieve female patient information whose age is more than 30 years and having laboratory bill amount more than 500

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

• In the above query, SQL RIGHT join is used to connect three tables patient, laboratory and doctor to fetch combined records
• Here the patient table is he RIGHT table whereas doctor and laboratory is the left tables
• To retrieve data conditionally SQL where clause is used, the logical operator AND is used in the where condition to join all three conditions to be TRUE in resulting recordset

OUTPUT:

 

SQL RIGHT JOIN on multiple tables with Group by Clause

SQL RIGHT OUTER JOIN allows us to use Group by clause along with aggregate functions to group records having the same value for the specified column

Example 5: Write SQL query to count the total number of patients of each doctor

SELECT  patient.doctor_id, doctor.name, COUNT(patient.patient_id) AS 'total patient'
FROM  doctor 
RIGHT OUTER JOIN patient ON patient.doctor_id = doctor.doctor_id
GROUP BY patient.doctor_id, doctor.name

• In the above query, SQL RIGHT OUTER JOIN is applied to join two tables patient, doctor to retrieve combined records
• Here the patient table is the RIGHT table to perform counting of all patients as per the doctor id
• SQL Group by clause is used to make a group of same doctor id

OUTPUT:

 

Example 6: Write SQL query to retrieve each doctor’s total patient whose has taken lab

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

• In the above query, SQL RIGHT OUTER JOIN is used to join two tables doctor and laboratory to retrieve combined records from both tables
• IN the query laboratory table is the RIGHT table and doctor is the left table
• SQL group by clause is used to do a grouping of same doctor id and count aggregate function is used to count total patient for each group of doctor id

OUTPUT:

 

SQL RIGHT OUTER JOIN on four tables with Group by having clause example

Example 7 : Write SQL query to retrieve each doctor details who is having more than one patient and having maximum lab report charges more than 500

SELECT doctor.doctor_id, doctor.name, COUNT(laboratory.patient_id) AS 'Total patient', MAX(laboratory.amount) AS 'Maximum lab charges amount' FROM  doctor 
RIGHT OUTER JOIN laboratory ON doctor.doctor_id = laboratory.doctor_id
GROUP BY doctor.doctor_id, doctor.name
HAVING (COUNT(laboratory.patient_id) > 1) AND (MAX(laboratory.amount) > 500)

• In the above query, SQL RIGHT OUTER JOIN is used to join two tables doctor and laboratory to retrieve combined records from both tables
• Here laboratory table is the RIGHT table and doctor is the left table
• SQL Group by used to make a group of same doctor id records and having is used to perform condition on each group of doctors

OUTPUT:

 

Summary

This tutorial Article on SQL RIGHT OUTER JOIN with practical examples have covered the overview of SQL RIGHT join, Syntax of SQL RIGHT join with an explanation of each syntax argument, practical examples of SQL RIGHT join on two tables, SQL RIGHT join on three tables, SQL RIGHT join with where clause and also covered practical example of SQL RIGHT join with the group by and having clause

 

References

SQL OUTER JOIN