SQL FULL JOIN

SQL FULL JOIN is a type of join operation in Structured Query Language (SQL) used to combine data from two or more tables based on the values of both tables. The full join returns all the rows from both tables, including the matching and non-matching rows. If there's no match, the result will contain NULL values for the columns from the corresponding table.

Orders Table:

OrderID	CustomerID
1	10
2	20
3	30
4	40

Customers Table:

CustomerID	CustomerName
10	William
20	Alice
30	Bob

Suppose we want to retrieve all the customers along with their order details, and for customers who have no orders, we want to see their names with a NULL value for the OrderID. And for orders without customers, we want to see the OrderID with a NULL value for the CustomerName. In that case, we can use the FULL JOIN to combine the data from the Orders and Customers tables as follows:

1SELECT *
2FROM Orders
3FULL JOIN Customers
4ON Orders.CustomerID = Customers.CustomerID;

The output of the above query will be:

OrderID	CustomerID	CustomerName
1	10	William
2	20	Alice
3	30	Bob
4	40	NULL
NULL	NULL	NULL

As we can see from the output, the FULL JOIN returns all the rows from both tables, including the matching and non-matching rows. For the customer with ID 40, there's no matching row in the Orders table, so the result contains NULL values for the OrderID and CustomerName columns from the Orders table. Also, for the order without a customer, the result contains NULL values for the CustomerID and CustomerName columns from the Customers table.