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:
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
| EmployeeID | Employee_Name | Employee_Salary |
|---|---|---|
| 1 | Arun Tiwari | 50000 |
| 2 | Sachin Rathi | 64000 |
| 3 | Harshal Pathak | 48000 |
| 4 | Arjun Kuwar | 46000 |
| 5 | Sarthak Gada | 62000 |
| 6 | Saurabh Sheik | 53000 |
| 7 | Shubham Singh | 29000 |
| 8 | Shivam Dixit | 54000 |
| 9 | Vicky Gujral | 39000 |
| 10 | Vijay Bose | 28000 |
Table 2: Department
| DepartmentID | Department_Name | Employee_ID |
|---|---|---|
| 1 | Production | 1 |
| 2 | Sales | 3 |
| 3 | Marketing | 4 |
| 4 | Accounts | 5 |
| 5 | Development | 7 |
| 6 | HR | 9 |
| 7 | Sales | 10 |
Table 3: Loan
| LoanID | Branch | Amount |
|---|---|---|
| 1 | B1 | 15000 |
| 2 | B2 | 10000 |
| 3 | B3 | 20000 |
| 4 | B4 | 100000 |
| 5 | B5 | 150000 |
| 6 | B6 | 50000 |
| 7 | B7 | 35000 |
| 8 | B8 | 85000 |
Table 4: Borrower
| CustID | CustName | LoanID |
|---|---|---|
| 1 | Sonakshi Dixit | 1 |
| 2 | Shital Garg | 4 |
| 3 | Swara Joshi | 5 |
| 4 | Isha Deshmukh | 2 |
| 5 | Swati Bose | 7 |
| 6 | Asha Kapoor | 10 |
| 7 | Nandini Shah | 9 |
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
