SQL - Full Join

In SQL Server, a FULL JOIN (also known as a full outer join) combines the results of both the LEFT JOIN and the RIGHT JOIN. It returns all the rows from both the left and right tables, matching rows where the join condition is met and including NULL values for non-matching rows.

The basic syntax for performing a FULL JOIN in SQL Server is as follows:

SELECT column_list
FROM table1
FULL JOIN table2 ON join_condition;

In this syntax, column_list represents the columns you want to select from the tables involved in the join operation. table1 is the left table, and table2 is the right table you want to join. The join_condition specifies the relationship between the tables.

The join condition is typically defined using the equality operator (=) to match values in the related columns of the joined tables. For example, if the related columns are "ID" in table1 and "ID" in table2, the join condition would be table1.ID = table2.ID.

Here's an example to illustrate the usage of a FULL JOIN:

Consider two tables, "Customers" and "Orders", with the following structures:

To retrieve all the customers and orders, including unmatched rows from both tables, you can use a FULL JOIN as follows:

SELECT Customers.Name, Orders.OrderID, Orders.Amount
FROM Customers
FULL JOIN Orders ON Customers.ID = Orders.CustID;

In the above query, we select the customer name from the "Customers" table and the order ID and amount from the "Orders" table. We perform a full join between the two tables, connecting them based on the customer ID (ID) column in the "Customers" table and the customer ID (CustID) column in the "Orders" table.

The full join ensures that all rows from both the left table ("Customers") and the right table ("Orders") are included in the result set. If there is a match, the corresponding customer and order information is displayed. If there is no match, NULL values are returned for the non-matching columns.

The output of the query would be:

As you can see, the result set includes all the customers and orders, even when there is no match between them. In this case, Mike doesn't have any orders, so NULL values are displayed for the order-related columns. Similarly, if there were customers without corresponding orders, NULL values would be displayed for the customer-related columns.

In summary, a FULL JOIN in SQL Server combines the results of both the LEFT JOIN and the RIGHT JOIN. It returns all the rows from both tables