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.

1. Performing 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 customers and 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,
    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;

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) );

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;

Output

Query:

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

Output

2. 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.

3. 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.