SQL Full Join is a type of join operation in SQL that combines the results of both left and right outer joins. It returns all the rows from both tables, matching rows from both tables, and null values for non-matching rows.
Full join is also known as a full outer join or a full outer join. It is used when we want to combine all the rows from two tables, including the unmatched rows. It is useful when we want to compare the data from two tables and find the differences between them.
The syntax for SQL Full Join is as follows:
SELECT column1, column2, ... FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Let's take an example to understand SQL Full Join better:
We have two tables, Customers and Orders. Customers table contains the customer information, and Orders table contains the order information. We want to combine both tables and get the customer name and order date for all the customers, including the customers who have not placed any orders.
Customers Table:
| CustomerID | CustomerName | ContactName | Country |
|---|---|---|---|
| 1 | Alfreds Futterkiste | Maria Anders | Germany |
| 2 | Berglunds snabbköp | Christina Berglund | Sweden |
| 3 | Island Trading | Helen Bennett | UK |
Orders Table:
| OrderID | CustomerID | OrderDate |
|---|---|---|
| 1 | 2 | 2021-01-01 |
| 2 | 3 | 2021-02-01 |
The SQL Full Join query to combine both tables is as follows:
SELECT Customers.CustomerName, Orders.OrderDate FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
The result of the above query will be:
| CustomerName | OrderDate |
|---|---|
| Alfreds Futterkiste | null |
| Berglunds snabbköp | 2021-01-01 |
| Island Trading | 2021-02-01 |
In the above result, we can see that the customer Alfreds Futterkiste has not placed any orders, so the OrderDate is null for that customer.
SQL Full Join is a powerful tool to combine data from two tables and find the differences between them. It is useful when we want to compare the data from two tables and find the missing or extra data.
References: