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

Advertisement

SQL RIGHT OUTER JOIN Explained [Practical Examples]

 

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

patient_id name age gender address disease doctor_id
1 reema 23 female althan,Surat fever 21
2 kusum 50 female vadodara heart failure 22
3 carlin 43 male vapi infection 23
4 rahul 26 male navsari cancer 21
6 hansha 55 female vapi diabetes 22

Doctor Table

doctor_id name age gender address
21 asif 55 male baruch
22 dhawal 40 male Surat
23 krishna 39 female Surat
24 lissa 35 female Navsari
25 leeba 34 female baruch
26 vini 33 female Surat
27 Dhiren 32 male Navsari

Bill Table

bill_no patient_id doctor_id room_charge no_of_days
5005 1 340 500 4
5006 2 600 480 8
5008 3 800 340 3
5009 4 780 890 6
5010 3 400   1
5011 1 200 300 1
5012 2 600 110 2
5013 3 330 210 1
5014 1 230 340 2

Laboratory Table

lan_no patient_id doctor_id date amount
10 1 21 02-02-2000 4000
20 2 21 09-09-2001 300
30 3 22 03-03-2001 600
40 1 23 02-06-2002 800
50 4 21 05-07-2003 900
60 2 25 10-04-2004 550
70 4 22 03-04-2005 900

 

SQL RIGHT OUTER JOIN with two tables

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

Advertisement
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 Explained [Practical Examples]

 

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 OUTER JOIN Explained [Practical Examples]

 

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 OUTER JOIN Explained [Practical Examples]

 

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

Advertisement
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 OUTER JOIN Explained [Practical Examples]

 

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:

SQL RIGHT OUTER JOIN Explained [Practical Examples]

 

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 Explained [Practical Examples]

Advertisement

 

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:

SQL RIGHT OUTER JOIN Explained [Practical Examples]

 

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

 

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X