SQL FULL OUTER JOIN

The FULL OUTER JOIN combines rows from two tables based on a join condition. Unlike an INNER JOIN, which only returns rows with matches in both tables, a FULL OUTER JOIN returns all rows from both tables.

FULL OUTER JOIN: Definition and Usage

A FULL OUTER JOIN is very useful when you need to see all data from both tables, whether or not there's a match in the other table. If a row in one table doesn't have a matching row in the other table (based on the join condition), that row is still included in the result, with NULL values for the columns from the table where there's no match. This helps to identify rows that might be missing relationships in your data.

Syntax


SELECT column_names
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
ORDER BY column_name;

Example

Joining Customers and Orders

This example joins the 'Customers' and 'Orders' tables using FULL OUTER JOIN to show all customers and all orders. Customers without orders will have NULL in the OrderID column, and orders without matching customers will have NULL in the CustomerName column. (Assumes 'Customers' and 'Orders' tables exist with a common `CustomerID` column.)

Syntax


SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Output


CustomerName             | OrderID
---------------------------------
Alfreds Futterkiste      | NULL       (Example output;  Actual data depends on your tables)
Ana Trujillo Emparedados y helados | 10308
Antonio Moreno Taquería | NULL
...                        | ...