Full Join in SQL is a method to combine rows from two or more tables based on a related column
In Structured Query Language, we often find ourselves needing to combine data from multiple tables, and there are several techniques to help us accomplish this. One of these methods, which provides a comprehensive view of our data, is the SQL Full Join.
SQL Full Join Syntax
A Full Join in SQL, also known as Full Outer Join, returns all records when there is a match in either the left table or the right table. If there is no match, the result is NULL on either side.
Here is how the syntax looks:
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
Executing this command will result in a new table that includes rows from both the left and right tables, regardless of whether there’s a match.
Unpacking the Syntax with Examples
To illustrate this concept, let’s use our familiar Orders and Customers tables.
The Orders table is as follows:
| OrderID | CustomerID | Product |
|---------|------------|------------|
| 1 | 101 | Apples |
| 2 | 102 | Bananas |
| 3 | 103 | Cherries |
| 4 | 104 | Dates |
| 5 | 105 | Eucalyptus |
And the Customers table looks like this:
| CustomerID | Name | Country |
|------------|----------|----------|
| 101 | Alice | USA |
| 102 | Bob | UK |
| 103 | Charlie | Canada |
| 104 | David | Australia|
| 106 | James | UK |
Our goal is to create a table that includes all orders from the Orders table and all customers from the Customers table, matching them where possible.
Here’s how I’d use the Full Join syntax to achieve this:
SELECT Orders.OrderID, Customers.Name, Orders.Product
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Executing this command gives us a new table:
| OrderID | Name | Product |
|---------|----------|-----------|
| 1 | Alice | Apples |
| 2 | Bob | Bananas |
| 3 | Charlie | Cherries |
| 4 | David | Dates |
| 5 | NULL | Eucalyptus|
| NULL | James | NULL |
In this result, all orders and all customers are included. If an order does not have a matching customer, or a customer does not have a matching order, the respective fields will be NULL.
Wrapping Up
Mastering the Full Join operation in SQL is a crucial step in becoming proficient with handling and analyzing relational data. It allows you to compile and correlate data from multiple tables based on shared attributes, ensuring that no data from either table is overlooked.
