SQL OUTER JOIN
- In the SQL outer JOIN, all the content from both the tables is integrated together.
- Even though the records from both the tables are matched or not, the matching and non-matching records from both the tables will be considered an output of the outer join in SQL.
- There are three different types of outer join in SQL:
- Left Outer Join
- Right Outer Join
- Full Outer Join
Now let us take a deeper dive into the different types of outer join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.
Consider we have the following tables with the given data:
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:
- If we use the left outer join to combine two different tables, then we will get all the records from the left table. But we will get only those records from the right table, which have the corresponding key in the left table.
- Syntax of writing a query to perform left outer join:
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
Example 1:
Write a query to perform left outer join considering employee table as the left table and department table as the right table.
Query:
We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.
You will get the following output:
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 2 | Sachin Rathi | 64000 | NULL | NULL |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 6 | Saurabh Sheik | 53000 | NULL | NULL |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 8 | Shivam Dixit | 54000 | NULL | NULL |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
EmployeeID, Employee_Name, Employee_Salary, Department_ID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an employeeID doesn't match with the employeeID of the employee table; then, it is displayed as NULL.
Example 2:
Write a query to perform left outer join considering loan table as the left table and borrower table as the right table.
Query:
We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.
You will get the following output:
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 3 | B3 | 20000 | NULL | NULL |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 6 | B6 | 50000 | NULL | NULL |
| 7 | B7 | 35000 | 5 | Swati Bose |
| 8 | B8 | 85000 | NULL | NULL |
LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; are displayed as NULL.
2. Right Outer Join:
- Right outer join is the reverse of left outer join. If we use the right outer join to combine two different tables, then we will get all the records from the right table. But we will get only those records from the left table, which have the corresponding key in the right table.
- Syntax of writing a query to perform right outer join:
-
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 RIGHT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
Example 1:
Write a query to perform right outer join considering employee table as the left table and department table as the right table.
Query:
We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the RIGHT OUTER JOIN keyword to perform the right outer join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.
You will get the following output:
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name are retrieved from employee and department tables. All the records from the department table are retrieved. Only those records that have a corresponding EmployeeID in the department table are retrieved from the employee table.
Example 2:
Write a query to perform right outer join considering loan table as the left table and borrower table as the right table.
Query:
We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the RIGHT OUTER JOIN keyword to perform the right outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.
You will get the following output:
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 7 | B7 | 35000 | 5 | Swati Bose |
| NULL | NULL | NULL | 6 | Asha Kapoor |
| NULL | NULL | NULL | 7 | Nandini Shah |
LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the borrower table are retrieved. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table; then, are displayed as NULL.
3. Full Outer Join:
- If we use a full outer join to combine two different tables, then we will get all the records from both the table,e., we will get all the records from the left table as well as the right table.
- MySQL doesn't support FULL OUTER JOIN directly. So to implement full outer join in MySQL, we will execute two queries in a single query. The first query will be of LEFT OUTER JOIN, and the second query will be of RIGHT OUTER JOIN. We will combine the first and second query with the UNION operator to see the results of FULL OUTER JOIN.
- Syntax of writing a query to perform full outer join:
-
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 LEFT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName UNION SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 RIGHT OUTER JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
Example 1:
Write a query to perform full outer join considering the employee table as the left table and department table as the right table.
Query:
We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the employee and department table where 'e' and 'd' are aliases. Then we have written a SELECT query to perform right outer join operation on employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables. Both the SELECT queries are combined using the UNION operator.
You will get the following output:
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
| 2 | Sachin Rathi | 64000 | NULL | NULL |
| 6 | Saurabh Sheik | 53000 | NULL | NULL |
| 8 | Shivam Dixit | 54000 | NULL | NULL |
EmployeeID, Employee_Name, Employee_Salary, Department_ID, Department_Name are retrieved from employee and department tables. All the records from the employee table are retrieved as a result of the left outer join. Only those records that have a corresponding EmployeeID in the employee table are retrieved from the department table. Rest other records in the department table for which an employeeID doesn't match with the employeeID of the employee table; then, are displayed as NULL. All the records from the department table are retrieved as a result of the right outer join. Only those records that have a corresponding EmployeeID in the department table are retrieved from the employee table.
Example 2:
Write a query to perform full outer join considering loan table as the left table and borrower table as the right table.
Query:
We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the LEFT OUTER JOIN keyword to perform the left outer join operation on the loan and borrower table where 'l' and 'b' are aliases. Then we have written a SELECT query to perform the right outer join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables. Both the SELECT queries are combined using the UNION operator.
You will get the following output:
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 3 | B3 | 20000 | NULL | NULL |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 6 | B6 | 50000 | NULL | NULL |
| 7 | B7 | 35000 | 5 | Swati Bose |
| 8 | B8 | 85000 | NULL | NULL |
| NULL | NULL | NULL | 6 | Asha Kapoor |
| NULL | NULL | NULL | 7 | Nandini Shah |
LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the loan table are retrieved as a result of the left outer join. Only those records that have a corresponding LoanID in the loan table are retrieved from the borrower table. Rest other records in the borrower table for which a LoanID doesn't match with the LoanID of the loan table; are displayed as NULL. All the records from the borrower table are retrieved as a result of the right outer join. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table, are displayed as NULL.
