Previous
Written by: rJTutorial Team | Last Updated:

SQL FULL OUTER JOIN

The FULL OUTER JOIN (often referred to simply as FULL JOIN) is the most inclusive join in SQL. It acts as a combination of both a LEFT JOIN and a RIGHT JOIN. While an INNER JOIN only shows you the "overlap" where data exists in both tables, a FULL OUTER JOIN ensures that no data is left behind from either side.

When you run this join, SQL returns every record from both tables. If a row in Table A matches a row in Table B, they are joined together. If there is no match, the result set still includes that row, but fills the columns from the "missing" side with NULL values.

Developer Tip: Think of a FULL OUTER JOIN as the "Full Picture" join. It is incredibly useful for auditing data and finding discrepancies or "orphaned" records that don't have a partner in the related table.

 

Syntax of FULL OUTER JOIN

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
  • table1: The "Left" table in your query.
  • table2: The "Right" table you are joining.
  • column: The common key (usually a Primary Key and Foreign Key) used to link the two datasets.
Watch Out: Not all database engines support the FULL OUTER JOIN syntax directly. For example, MySQL does not support it. In MySQL, you typically have to achieve this by using a LEFT JOIN and a RIGHT JOIN combined with a UNION.

 

Example

To understand how this works in a real-world scenario, let's look at a typical e-commerce database. We have a list of registered Customers and a list of Orders.

Customers Table

customer_id name
1 Alice
2 Bob
3 Charlie

Orders Table

order_id customer_id order_date
101 1 2024-12-01
102 2 2024-12-02
103 4 2024-12-03

FULL OUTER JOIN Query

Imagine we want a report of all activity. We want to see every customer (even those who haven't ordered yet) and every order (even those placed by guests who aren't in our customers table). We use the following query:

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

Output:

name order_id order_date
Alice 101 2024-12-01
Bob 102 2024-12-02
Charlie NULL NULL
NULL 103 2024-12-03

Explanation of the Results:

  • Alice & Bob: These customers have IDs that exist in both tables. Their data is merged perfectly.
  • Charlie: Charlie is in our Customers table but hasn't placed an order. Because it's a FULL OUTER JOIN, he is included, but the order columns are NULL.
  • Order 103: This order was placed by customer_id = 4. However, there is no customer with ID 4 in our Customers table. This is an "orphaned" row, likely from a guest checkout or a data error. It is included, but the name column is NULL.
Best Practice: When using FULL OUTER JOIN, always use Table Aliases (e.g., FROM customers AS c) to keep your query readable, especially when selecting columns with identical names from both tables.

 

FULL OUTER JOIN with WHERE Clause

One of the most powerful uses of a FULL OUTER JOIN is to identify records that do not have a match in the other table. This is effectively the opposite of an Inner Join.

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:

name order_id order_date
Charlie NULL NULL
NULL 103 2024-12-03

This result is incredibly helpful for database cleanup. It shows you exactly which customers have never ordered and which orders are missing customer profiles.

Common Mistake: Beginners often use WHERE table.column = NULL. In SQL, you must use IS NULL to check for null values, as NULL is not equal to anything, not even itself.

 

FULL OUTER JOIN with Multiple Tables

You can expand this logic to three or more tables. For example, if you want to see the entire relationship chain between Customers, Orders, and Payments:

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

In this scenario, SQL will first perform a full join between customers and orders, and then perform another full join between that result and the payments table. This helps you find customers who haven't ordered, orders that haven't been paid, or even payments that aren't linked to a valid order.

 

Key Points

  • FULL OUTER JOIN captures 100% of the data from both tables involved in the join.
  • It is essentially a UNION of a Left Join and a Right Join.
  • NULL values are the engine's way of saying "I found a row here, but there was no corresponding data in the other table."
  • It is most frequently used in Reporting, Data Warehousing, and Data Validation tasks.
Watch Out: Full joins can be resource-intensive on very large datasets (millions of rows) because the database has to map every single row from both sides. Always check your execution plan if performance seems slow.

 

Summary

The FULL OUTER JOIN is your "safety net" join. It ensures that you don't lose records just because they don't have a match. Whether you are reconciling financial accounts or checking for data integrity between two systems, the Full Outer Join provides the visibility you need to see the "big picture" of your data.

Previous