FULL Join

A FULL JOIN, also known as a FULL OUTER JOIN, is a type of join in SQL that combines rows from two tables, returning all rows from both tables and matching rows where the join condition is met. If there is no match between rows in either of the tables, the unmatched rows will still be included in the result set with NULL values for columns from the other table.

The basic syntax of the FULL JOIN is as follows:

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

In this syntax, table1 and table2 are the two tables you want to join, and column_name is the column in both tables that you use as the join condition. The ON keyword is used to specify the join condition, which indicates the relationship between the tables.

Here's an example to illustrate the FULL JOIN:

Suppose we have two tables, "Customers" and "Orders," with columns as follows:

Customers table:

CustomerID
CustomerName

Orders table:

OrderID
CustomerID
OrderDate
TotalAmount

If we want to retrieve all customer names along with order details for each order, including customers who have not placed any orders and orders with no corresponding customer, we can use the FULL JOIN on the CustomerID column:

SELECT OrderID, CustomerName, OrderDate, TotalAmount
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

In this example, the FULL JOIN combines all rows from "Customers" and "Orders," including matched rows where the CustomerID values match, as well as unmatched rows where there is no corresponding customer for an order or no corresponding order for a customer. For the unmatched rows, the columns from the other table will contain NULL values.

The result set will include all customer names along with order details for each order and NULL values for the orders without a customer and customers without orders.

FULL JOIN is less commonly used than other types of joins (such as INNER JOIN, LEFT JOIN, and RIGHT JOIN), but it can be useful when you need to retrieve all rows from both tables, even if there is no direct match between them.