SQL OUTER JOIN Explained [Practical examples]

Overview of SQL OUTER JOIN

SQL OUTER JOIN is used to preserve all of the records that have values in either the left or right table, In the SQL OUTER JOIN all the content of both tables are integrated together whether they are matched or not

When performing an inner join, rows from either table that are unmatched in the other table are not returned. In an outer join, unmatched rows in one or both tables can be returned

 

There are mainly three forms of Outer Join in SQL

1. Left outer join
2. Right outer join
3. Full outer join

 

Key points for SQL Outer Join

• Columns used in a join condition are not required to have the same name or be the same data type. However, if the data types are not identical, they must be compatible or be types that SQL Server can implicitly convert
• The select list is not required to contain columns from every table in the join
• Although join conditions usually have equality comparisons (=), other comparison or relational operators can be specified, as can other predicates

 

JOIN and ON keyword

ON is used with JOIN in a join condition to identify which columns in each table to link and can be used with all types of joins. USING is also combined with JOIN in a join condition, but it requires that the column name be identical in both joined tables. JOIN, which is followed by a table name, and ON, which is followed by a couple of column names separated by an equal's sign

 

LEFT JOIN | LEFT OUTER JOIN

SQL left outer join returns all the records from the left table combined with the matching rows of the right table. If you get no matching in the right table it returns NULL values

Left outer join to prevent all rows of the left table in resulting set and combine with matched records of left and right tables

SQL LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table.  On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table

 

Syntax of SQL LEFT OUTER JOIN

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2... FROM Table1 
LEFT JOIN | LEFT OUTER JOIN Table2 ON Table1.matchingcolumnname=Table2.matchingcolumnname
[where condition]
[order by column1,column2..];

 

Syntax argument of SQL LEFT OUTER JOIN

• column1,column2…column(n): Specify each column name separated by a comma from both the tables
• Table1: Table1 is the left table name from which all data will be retrieved, and combined only the matched records of Table2
• Table2: Table2 is the right table name which will be joined with the left table to retrieve match records, only matching records will be retrieved from the right table
• LEFT JOIN|LEFT OUTER JOIN : It is the keyword used to join two tables using left join, we can specify either LEFT JOIN or LEFT OUTER JOIN keyword to perform left 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 Left 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 LEFT OUTER JOIN with two tables

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

SELECT patient.patient_id, patient.name AS 'Patient Name', doctor.name AS 'Doctor Name', patient.age, patient.gender, patient.address, patient.city, patient.disease
FROM  patient LEFT OUTER JOINdoctor 
ON patient.doctor_id = doctor.doctor_id

• In the above query, SQL left outer join used to join the patient and the doctor table, patient is the left table and doctor is the right tables
• The above query returns all records from the left table patient and the matching records from the right table doctor

OUTPUT:

 

SQL LEFT OUTER JOIN with Three tables

Example 2: Write SQL query to display patient personal details with doctor name and billing details

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

• In the above query, SQL left outer join is used with three tables patient, doctor, and bill to retrieve combined records from all tables
• In this query, patient table is the left table so all records of the patient table will be retrieved and the matching records from the doctor and billing tables combine with the records of the patient table

OUTPUT:

 

SQL LEFT OUTER JOIN with where condition and order by clause

We can conditionally retrieve combine records of two or more tables and display them in specified order of mention column using SQL LEFT OUTER JOIN with where condition and order by clause

Example 3: Write a SQL query to display patient personal information with diseses name and lab report data whose lab bill amount is more than 500 in the order of diseses name

SELECT  patient.patient_id, patient.name AS 'Patient Name', doctor.name AS 'Doctor Name', patient.disease, laboratory.date AS 'lab report date', laboratory.lab_no AS 'laboratory number', laboratory.amount AS 'lab report amount'
FROM   patient 
LEFT OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id 
LEFT OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id
WHERE (laboratory.amount > 500)
ORDER BY patient.disease

• In the above query, SQL LEFT OUTER JOIN is used to join three tables patient, doctor, and laboratory to fetch a combined record set of all three tables
• Where clause is used to conditionally retrieve records as laboratory bill amount is more than 500 and order by clause is used to display resulting record set in the order of disease name=

OUTPUT:

 

RIGHT JOIN | RIGHT OUTER JOIN

The RIGHT OUTER JOIN or RIGHT JOIN operation returns all records from the right table and matches records from the left table. On a matching element not found in the left table then it returns NULL

Right outer join prevent all rows of a right table from combining the matching record of both the tables

SQL RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table.  On the left table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table

 

Syntax of Right outer join

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2... FROM Table1 
RIGHT JOIN|RIGHT OUTER JOIN Table2 ON Table1.matchingcolumnname=Table2.matchingcolumnname
[where condition]
[order by column1,column2..];

 

Syntax argument of SQL Right outer join

• column1,column2…column(n): Specify each column name separated by a comma from both the tables
• Table1: Table1 is the left table name from which all data will be retrieved and combined only the matched records of Table2
• Table2: Table2 is the right table name which will be joined with the left table to retrieve match records, only matching records will be retrieved from the right table
• LEFT JOIN|LEFT OUTER JOIN : It is the keyword used to join two tables using left join, we can specify either LEFT JOIN or LEFT OUTER JOIN keyword to perform left 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 RIGHT OUTER JOIN

SQL RIGHT OUTER JOIN with two tables

Example 4: Write SQL query to display all lab report data with the patient name

SELECT laboratory.lab_no AS 'Lab No', patient.name AS 'Patient name', laboratory.doctor_id AS 'Doctor ID', patient.disease AS 'Disease', laboratory.date AS 'Report date', laboratory.amount
FROM patient RIGHT OUTER JOIN laboratory ON laboratory.patient_id = patient.patient_id

• In the above query, SQL RIGHT OUTER JOIN is used to display all laboratory data of patients by combining matched records patient personal information from the patient table
• The above query will display all records from the laboratory and also the matched records of the patient table

OUTPUT:

 

SQL RIGHT OUTER JOIN with Three tables

Example 5 : Write SQL query to display doctor details with the patient name , disease, nad the lab report records

SELECT doctor.name AS 'doctor name', patient.name AS 'patient name', patient.disease, laboratory.lab_no, laboratory.amount FROM  patient 
RIGHT OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id 
RIGHT OUTER JOIN laboratory ON laboratory.doctor_id = patient.doctor_id

OUTPUT:

 

SQL RIGHT OUTER JOIN with where condition and order by clause

We can use where and order by clause with RIGHT OUTER JOIN to conditionally retrieve records from two and more tables in the specific order of the given column name

Example 6: Write SQL query to display doctor details with the fees charged if the fees are more than 300 with patient details in the order of doctor fees whose fee is more than 300

SELECT  bill.bill_no, patient.name, doctor.name AS 'doctor name', bill.doctor_charge AS 'Doctor Fees', patient.disease
FROM bill 
RIGHT OUTER JOIN doctor ON bill.doctor_id = doctor.doctor_id 
RIGHT OUTER JOIN patient ON patient.doctor_id = doctor.doctor_id
WHERE        (bill.doctor_charge > 300)
ORDER BY 'Doctor Fees'

• In the above query, SQL RIGHT OUTER JOIN is used to retrieve join records of three tables doctor, bill, and patient in which doctor is the right table
• To specified condition where clause is used as we need to retrieve records if the doctor charges are more than 300 and order by clause is used to display records in the order of doctor fees charges

OUTPUT:

 

FULL OUTER JOIN

The SQL FULL OUTER JOIN combines the results of both left and right outer joins, it returns all matched or unmatched records from the tables on both sides of the join clause

When no matching rows exist for the row in the left table, the columns of the right table will contain NULL. Likewise, when no matching rows exist for the row in the right table, the column of the left table will contain NULL

SQL full outer join and SQL join are the same, Generally, it is known as SQL FULL JOIN

 

Syntax of SQL FULL OUTER JOIN

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2... FROM Table1 
FULL OUTER JOIN Table2 ON Table1.matchingcolumnname=Table2.matchingcolumnname
[where condition]
[order by column1,column2..];

 

Syntax argument of SQL FULL outer join

• column1,column2…column(n): Specify each column name separated by a comma from both the tables
• Table1: Table1 is the left table name from which all data will be retrieved and combined only the matched records of Table2
• Table2: Table2 is the right table name which will be joined with the left table to retrieve match records, only matching records will be retrieved from the right table
• FULL OUTER JOIN : It is the keyword used to join two tables using left join, we can specify either LEFT JOIN or LEFT OUTER JOIN keyword to perform left 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 FULL OUTER JOIN

SQL FULL OUTER JOIN With two table

Example 7 : Write SQL query to display patient personal information by combining billing information

SELECT patient.patient_id, patient.name AS 'Patient Name', patient.age, patient.gender, patient.city, patient.disease, 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

• In the above query, we have used SQL FULL OUTER JOIN to get records of both the tables patient and bill
• the above query returns the list of records for each patient record it shows billing details so, for each patient record of the patient table we will get more than one billing detail available in the bill table

OUTPUT:

 

SQL FULL OUTER JOIN With three tables

Example 8: Write SQL query to patient data with billing and lab report details

SELECT patient.patient_id, patient.name AS 'Patient Name', patient.age, patient.gender, patient.city, patient.disease, bill.bill_no, bill.doctor_charge, bill.room_charge, bill.no_of_days, bill.amount, laboratory.lab_no,laboratory.date AS 'Lab report date'
FROM patient 
FULL OUTER JOIN bill ON patient.patient_id = bill.patient_id 
FULL OUTER JOIN laboratory ON patient.patient_id = laboratory.patient_id

• In the above query, SQL FULL OUTER JOIN is used to retrieve combined data of patients from three tables patient, bill, and laboratory
• Using SQL FULL OUTER JOIN we combine each patient record of the patient table with billing and lab report data of the same patient id, each patient can have multiple lab reports and multiple billing entry so we will get multiple records for each patient in the resulting set

OUTPUT:

 

SQL FULL OUTER JOIN With Where condition and Order by clause

Example 9: Write SQL query to display those patient data who has admitted for more than 3 days with doctor name in the order of number of admitted days

SELECTpatient.patient_id, patient.name AS 'Patient Name', patient.age, patient.gender, patient.city, patient.disease, bill.bill_no, bill.doctor_charge, bill.room_charge, bill.no_of_days, bill.amount, doctor.name
FROM patient 
FULL OUTER JOIN bill ON patient.patient_id = bill.patient_id 
FULL OUTER JOIN doctor ON patient.doctor_id = doctor.doctor_id
WHERE(bill.no_of_days > 3)
ORDER BY bill.no_of_days

• In the above query, SQL FULL OUTER JOIN is used to conditionally retrieve patient records with their doctor name and bill details
• The where clause is used to specify condition and order by clause is used to display records in the order of no. of admitted days

OUTPUT:

 

Summary

In this Article on SQL OUTER JOIN, we have started with the overview of SQL OUTER JOIN, define three forms of outer join that are LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, also explain each form of outer join with syntax, use of each syntax argument and also discuss practical examples of each form with two tables, more than two tables and with the where and order by clause

 

References

SQL LEFT JOIN

 

Read More

Joins (SQL Server)