SQL Full Outer Join Using Left and Right Outer Join and Union Clause
An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL.
In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause.
Consider the two tables below:
Sample Input Table 1:
Purchase Information |
Product_ID |
Mobile_Brand |
Cost (INR) |
Customer_Name |
1 |
OnePlus Nord 5G |
30,000 |
Rishabh |
2 |
Samsung Galaxy M51 |
28,000 |
Srishti |
3 |
iPhone 12 Pro |
1,28,000 |
Aman |
4 |
Samsung Galaxy S20 |
55,000 |
Harsh |
5 |
Realme X50 Pro |
40,000 |
Manjari |
Sample Input Table 2:
Customer Information |
Customer_ID |
Customer_Name |
E_Mail Address |
1 |
Srishti |
abc@gmail.com |
2 |
Rajdeep |
def@gmail.com |
3 |
Aman |
xxx@gmail.com |
4 |
Pooja |
xyz@gmail.com |
FULL OUTER JOIN:
Full Join provides results with the concatenation of LEFT JOIN and RIGHT JOIN. The result will contain all the rows from both Table 1 and Table 2. The rows having no matching in the result table will have NULL values.
SELECT * FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_match=Table2.column_match;
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
The above query can also be written using a combination of LEFT OUTER JOIN, RIGHT OUTER JOIN, and UNION. The meaning of UNION is to join two or more data sets into a single set. The above query and the below query will provide the same output.
SELECT * FROM Table1
LEFT OUTER JOIN Table2
ON Table1.column_match=Table2.column_match
UNION
SELECT * FROM Table1
RIGHT OUTER JOIN Table2
ON Table1.column_match=Table2.column_match;
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
Sample Output:
RESULTS |
Product_ID |
Mobile_Brand |
COST (INR) |
Customer_Name |
Customer_ID |
Customer_Name |
E_Mail Address |
1 |
OnePlus Nord 5G |
30,000 |
Rishabh |
NULL |
NULL |
NULL |
2 |
Samsung Galaxy M51 |
28,000 |
Srishti |
1 |
Srishti |
abc@gmail.com |
3 |
iPhone 12 Pro |
1,28,000 |
Aman |
3 |
Aman |
xxx@gmail.com |
4 |
Samsung Galaxy S20 |
55,000 |
Harsh |
NULL |
NULL |
NULL |
5 |
Realme X50 Pro |
40,000 |
Manjari |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
2 |
Rajdeep |
def@gmail.com |
NULL |
NULL |
NULL |
NULL |
4 |
Pooja |
xyz@gmail.com |
SQL QUERY FOR THE SAMPLE INPUTS:
We have considered the Customer and Purchase Information of mobile phones from an E-Commerce site during Big Billion Days. The Database E-Commerce has two tables one has information about the Product and the other one has information about the Customer. Now, we will perform a FULL OUTER JOIN between these two tables to concatenate them into a single table and get complete data about the customers and the products they purchased from the site.
Now let’s consider the purchase_information table. To view the table use the below query:
SELECT * FROM purchase_information;
Output:
Purchase Table
To view the customer_information table use the below query:
SELECT * FROM customer_information;
Output:
Customer Table
Now we can simply call the FULL OUTER JOIN clause to achieve a combined result from both the above-created tables using the below query:
SELECT * FROM purchase_information
FULL OUTER JOIN customer_information
ON purchase_information.cust_name=customer_information.customer_name
Output:
RESULT TABLE USING FULL OUTER JOIN
But we can achieve the same results without using the FULL OUTER JOIN clause. For this we make use of the LEFT JOIN, RIGHT JOIN, and the UNION clause as shown below:
SELECT FROM purchase_information
LEFT OUTER JOIN customer information
ON purchase_information.cust_name=customer_information.customer_name
UNION
SELECT * FROM purchase_information
RIGHT OUTER JOIN customer_information
ON purchase_information.cust_name=customer_information.customer_name
Output:
RESULTANT TABLE OF FULL OUTER JOIN USING LEFT AND RIGHT AND UNION
Last Updated :
21 Apr, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...