SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword combines rows from two tables based on a join condition. Unlike INNER JOIN, which only returns rows with matches in both tables, FULL OUTER JOIN returns all rows from both tables. If there's a match, the columns from both tables are combined; if there's no match in one table, the columns from the other table are shown with NULL values for the missing columns.

FULL OUTER JOIN: Definition and Usage

This type of join is very useful when you want to see *all* data from both tables, regardless of whether there's a matching row in the other table. It's particularly helpful for identifying rows in one table that don't have corresponding entries in another.

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 a FULL OUTER JOIN. It shows all customers, and all orders, even if a customer has no orders or an order has no matching customer.

Syntax


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

Output

The output will list all customers. If a customer has orders, the corresponding order IDs will be displayed. If a customer has no orders, the OrderID will be NULL. Similarly, if there are orders without a matching customer (which shouldn't happen in a well-designed database), the CustomerName will be NULL for those orders.


CustomerName             | OrderID
---------------------------------
Alfreds Futterkiste      | 1
Ana Trujillo Emparedados y helados | 2
...                        | ...
(Customer with no orders)| NULL
...                        | ...