SQL FULL JOIN

The SQL FULL JOIN clause combines the results of both the LEFT JOIN and the RIGHT JOIN. It retrieves all rows from both tables, and for rows that do not have a match in the opposite table, it returns NULL.

This join is useful when you want to include all records from both tables, even if there is no corresponding match.

Tables Used

Orders Table:
OrderID Product CustomerID
1 Laptop 101
2 Phone 102
3 Tablet NULL
Customers Table:
CustomerID CustomerName
101 Alice
102 Bob
103 Charlie

Example

We’ll perform a FULL JOIN to include all rows from both the Orders and Customers tables.

Code Example


-- Perform a FULL JOIN between Orders and Customers
SELECT Orders.OrderID, Orders.Product, Customers.CustomerName
FROM Orders
FULL JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

-- View the resulting table

Output Table

OrderID Product CustomerName
1 Laptop Alice
2 Phone Bob
3 Tablet NULL
NULL NULL Charlie

Explanation

- The FULL JOIN ensures that all rows from both the Orders and Customers tables are included.
- For rows without a match, NULL is shown for missing values.