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 table is as follows:
| OrderID | CustomerID | Product | |---------|------------|------------| | 1 | 101 | Apples | | 2 | 102 | Bananas | | 3 | 103 | Cherries | | 4 | 104 | Dates | | 5 | 105 | Eucalyptus |
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
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.