SQL Full Outer Join Using Where Clause

A FULL OUTER JOIN is a type of SQL Join that returns all the rows from both tables (says Table 1 or Table 2), regardless of whether they have matching values. In short, we can say it is used to retrieve all rows when there is a match in either left or right table.

• It combines the results of the LEFT JOIN and the RIGHT JOIN into a single result set.
• It returns all rows from both tables, with NULL values in place where there is no match.

Note: This is helpful when you want a complete overview of the data from both tables, ensuring that no information is excluded

Syntax:

SELECT * FROM Table1
FULL OUTER JOIN Table2
ON Table1.column_match=Table2.column_match;

Here,

• Table 1: First Table in Database.
• Table 2: Second Table in Database.
• column_match: The column common to both tables.

Example: FULL OUTER Join

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 and get complete data about the customers and the products they  purchased from the site.

Table 1: PURCHASE_INFORMATION

You can use the following query to create the demo table1:

CREATE TABLE PURCHASE_INFORMATION (
    Product_ID INT,
    Mobile_Brand VARCHAR(50),

    Cost INT,
//Driver Code Starts
    Customer_Name VARCHAR(50)
);


INSERT INTO PURCHASE_INFORMATION (Product_ID, Mobile_Brand, Cost, Customer_Name)
VALUES
(1, 'OnePlus Nord 5G', 30000, 'Rishabh'),
(2, 'Samsung Galaxy M51', 28000, 'Srishti'),
(3, 'iPhone 12 Pro', 128000, 'Aman'),
(4, 'Samsung Galaxy S20', 55000, 'Harsh'),
(5, 'Realme X50 Pro', 40000, 'Manjari');

Select * From PURCHASE_INFORMATION;
//Driver Code Ends

Output:

Table 2: CUSTOMER INFORMATION

Let's create demo table2 using the following SQL query:

CREATE TABLE CUSTOMER_INFORMATION (
    Customer_ID INT,
    Customer_Name VARCHAR(50),

    E_Mail_Address VARCHAR(100)
//Driver Code Starts
);

INSERT INTO CUSTOMER_INFORMATION (Customer_ID, Customer_Name, E_Mail_Address)
VALUES
(1, 'Srishti', '[email protected]'),
(2, 'Rajdeep', '[email protected]'),
(3, 'Aman', '[email protected]'),
(4, 'Pooja', '[email protected]');

Select * from CUSTOMER_INFORMATION;
//Driver Code Ends

Output:

Query:

SELECT *
FROM PURCHASE_INFORMATION
FULL OUTER JOIN CUSTOMER_INFORMATION
ON PURCHASE_INFORMATION.Customer_Name = CUSTOMER_INFORMATION.Customer_Name;

Output:

Example: FULL OUTER JOIN using WHERE CLAUSE

Adding a WHERE clause to a FULL OUTER JOIN allows us to filter the results based on specific conditions. For example, we can retrieve only those rows where no match was found in either table, ensuring we focus on unmatched records. This is particularly useful for identifying differences or gaps between datasets.

Query:

SELECT *
FROM PURCHASE_INFORMATION
FULL OUTER JOIN CUSTOMER_INFORMATION
ON PURCHASE_INFORMATION.Customer_Name = CUSTOMER_INFORMATION.Customer_Name
WHERE PURCHASE_INFORMATION.Customer_Name IS NULL
 OR CUSTOMER_INFORMATION.Customer_Name IS NULL;

Output:

Explanation:

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.

Using FULL OUTER JOIN with AND Condition

An AND condition in the ON clause ensures that multiple conditions must be true simultaneously for rows to match. If either condition is not satisfied, the rows from both tables will still appear in the result but with NULL values for the unmatched columns. This approach is useful for performing more granular matches while still preserving unmatched data for analysis.

Query:

SELECT *FROM Table1
FULL OUTER JOIN Table2
ON Table1.column1 = Table2.column1
   AND Table1.column2 = Table2.column2;

Tip: Try to Print result of this query on your workbench.

Conclusion

The FULL OUTER JOIN in SQL is an important tool for combining datasets and ensuring no data is left out. By using WHERE clause, we can filter the results to focus on specific cases such as unmatched records. This join is perfect for ensuring that no data is missed in scenarios where you want to keep both matching and non-matching rows. For generating complete reports where all data from two different tables needs to be displayed, even if some data entries do not match.