SQL FULL OUTER JOIN

The FULL OUTER JOIN in SQL combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records from both tables, matching rows where possible. If there is no match, NULL values are returned for the columns from the non-matching table.

 

Syntax of FULL OUTER JOIN

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

• table1: The first table in the join.
• table2: The second table in the join.
• column: The column used to match rows between the two tables.

 

Example

Consider the following two tables:

Customers Table

Orders Table

FULL OUTER JOIN Query

To get a list of all customers and their orders, even if no match exists:

SELECT customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

Output:

Explanation:

• Alice and Bob have matching orders, so their details are included.
• Charlie has no matching order, so the order_id and order_date are NULL.
• The order with order_id = 103 has no matching customer, so the name is NULL.

 

FULL OUTER JOIN with WHERE Clause

You can filter the result using a WHERE clause. For instance, to find records where no match exists:

SELECT customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
WHERE customers.customer_id IS NULL OR orders.customer_id IS NULL;

Output:

This query returns only the unmatched rows.

 

FULL OUTER JOIN with Multiple Tables

To join more than two tables with a FULL OUTER JOIN, you chain the joins:

SELECT customers.name, orders.order_id, payments.payment_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id
FULL OUTER JOIN payments ON orders.order_id = payments.order_id;

This retrieves customers, their orders, and payment details, including unmatched rows from any table.

 

Key Points

• FULL OUTER JOIN combines the results of LEFT JOIN and RIGHT JOIN.
• It includes:
  • All rows where there is a match between the two tables.
  • Rows from the left table with no match in the right table.
  • Rows from the right table with no match in the left table.
• Unmatched rows have NULL values for columns of the other table.

 

Summary

The FULL OUTER JOIN is ideal when you need a complete view of both tables, including matching and non-matching rows. It is commonly used in reporting or when working with incomplete data from multiple sources.