Skip to content
Related Articles

Related Articles

SQL Full Outer Join Using Where Clause
  • Difficulty Level : Medium
  • Last Updated : 22 Apr, 2021

A 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 about FULL OUTER JOIN using WHERE clause.

Consider the two tables below:

Sample Input Table 1

                             PURCHASE INFORMATION
Product_IDMobile_BrandCost (INR)Customer_Name
1OnePlus Nord 5G30,000Rishabh
2Samsung Galaxy M5128,000Srishti
3iPhone 12 Pro1,28,000Aman
4Samsung Galaxy S2055,000Harsh
5Realme X50 Pro40,000Manjari

Sample Input Table 2 :



                 CUSTOMER INFORMATION
Customer_IDCustomer_NameE_Mail Address
1Srishtiabc@gmail.com
2Rajdeepdef@gmail.com
3Amanxxx@gmail.com
4Poojaxyz@gmail.com

FULL OUTER JOIN : Full Join provides result with 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 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.

Sample Output :

                                                                                  RESULTS
Product_IDMobile_BrandCost (INR)Customer_NameCustomer_IDCustomer_NameE_Mail Address
1OnePlus Nord 5G30,000RishabhNULLNULLNULL
2Samsung Galaxy M5128,000Srishti1Srishtiabc@gmail.com
3iPhone 12 Pro1,28,000Aman3Amanxxx@gmail.com
4Samsung Galaxy S2055,000HarshNULLNULLNULL
5Realme X50 Pro40,000ManjariNULLNULLNULL
NULLNULLNULLNULL2Rajdeepdef@gmail.com
NULLNULLNULLNULL4Poojaxyz@gmail.com

FULL OUTER JOIN using WHERE CLAUSE : The use of WHERE clause with FULL OUTER JOIN helps to retrieve all those rows which have no entry matching on joining both the tables having NULL entry. 

SELECT * FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_match=Table2.column_match
WHERE Table1.column is NULL
OR Table2.coulmn is NULL;

Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
coulumn: The column having NULL value after Full Outer Join

The above Query returns only those customer who bought mobile phones and don’t have any record saved in Customer Information Table as well as the customer information who didn’t buy any product.

SQL QUERY FOR THE SAMPLE INPUTS : We have considered a 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 have information about the Customer. Now, we will perform 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.

BASIC SQL QUERY :

1. Creating a Database

CREATE DATABASE database_name;

2. Creating a Table



CREATE TABLE Table_name(
col_1 TYPE col_1_constraint,
col_2 TYPE col_2 constraint
.....
)

col: Column name
TYPE: Data type wheher an integer, variable character, etc
col_constraint: Constraints in SQL like PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, etc

3. Inserting into a Table

INSERT INTO Table_name
VALUES(val_1, val_2, val_3, ..........)

val: Values in particular column

4. View The Table

SELECT * FROM Table_name

For more information regarding SQL syntaxes visit our website SQL Tutorial.

Output :

1. Customer Information and Purchase Information Table

Purchase Information Table

Customer Information Table

2. Full Outer Join 

Result Table of FULL OUTER JOIN

3. Full Outer Join with WHERE clause

FULL OUTER JOIN  with WHERE clause Resultant Table

My Personal Notes arrow_drop_up
Recommended Articles
Page :