SQL OUTER JOIN: A Comprehensive Guide

SQL OUTER JOIN

All of the data from both tables is combined in SQL using the OUTER JOIN. Whether the records from both tables match or not, the result includes both matching and non-matching records. Three kinds of outside joins are supported by SQL:

• Left Outer Join
• Right Outer Join
• Full Outer Join

We shall examine several kinds of outer joins in this guide using examples from MySQL.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

Understanding Outer Joins with Sample Tables

We have example data in the following tables:

Table 1: Employee

Table 2: Department

Table 3: Loan

Table 4: Borrower

1. Left Outer Join

Only matched records from the right table are retrieved by a left outer join, which retrieves all records from the left table. NULL values are returned in the event that no match is discovered.

Syntax:

SELECT Table1.columnName1, Table2.columnName2
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

Example 1: Employee and Department Tables

SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name
FROM employee e
LEFT OUTER JOIN department d
ON e.EmployeeID = d.Employee_ID;

Example 2: Loan and Borrower Tables

SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName
FROM Loan l
LEFT OUTER JOIN Borrower b
ON l.LoanID = b.LoanID;

2. Right Outer Join

Only matched records from the left table are retrieved by a right outer join, which retrieves all entries from the right table. NULL values are returned in the event that no match is discovered.

Syntax:

SELECT Table1.columnName1, Table2.columnName2
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

Example 1: Employee and Department Tables

SELECT e.EmployeeID, e.Employee_Name, e.Employee_Salary, d.DepartmentID, d.Department_Name
FROM employee e
RIGHT OUTER JOIN department d
ON e.EmployeeID = d.Employee_ID;

Example 2: Loan and Borrower Tables

SELECT l.LoanID, l.Branch, l.Amount, b.CustID, b.CustName
FROM Loan l
RIGHT OUTER JOIN Borrower b
ON l.LoanID = b.LoanID;

3. Full Outer Join

All records, matching and non-matching, are retrieved from both tables using a full outer join. Although FULL OUTER JOIN is not natively supported by MySQL, it can be accomplished with UNION.

Workaround for FULL OUTER JOIN in MySQL:

SELECT Table1.columnName1, Table2.columnName2
FROM Table1
LEFT OUTER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName
UNION
SELECT Table1.columnName1, Table2.columnName2
FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName;

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

SQL OUTER JOINs facilitate the retrieval of data, including unmatched records, from several tables. Database administration requires an understanding of Left Outer Join, Right Outer Join, and workarounds for Full Outer Join in MySQL.

Would you like to explore more SQL queries? Let us know in the comments!

sql left outer join
sql outer join
oracle sql outer join
proc sql outer join
my sql outer join
spark sql outer join
pl sql outer join
mysql outer join
sql outer join example
sql full outer join
sql joins
sql join types
right outer join
self join in sql
sql inner join
left outer join