SQL - Join - Full

A full join, also known as a full outer join, is a type of join operation in SQL. It combines rows from two or more tables based on a related column between them. The full join returns all the rows from both tables, including the matching and non-matching rows. If there are no matches in one table, NULL values are returned for the columns of the other table.

A full join is used when you want to include all the rows from both tables, regardless of whether there are matching rows or not. It is commonly used to retrieve a combination of records from multiple tables, including those that do not have matching values in the related columns.

Syntax:

SELECT column_list
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

In this syntax, table1 and table2 are the tables to be joined, and the ON clause specifies the condition for matching the rows.

Example:

Consider two tables: Customers and Orders. The Customers table contains information about customers, while the Orders table contains information about orders placed by customers.

To retrieve a combination of all customers and their corresponding orders, including customers who have not placed any orders, we can use a full join:

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

In this example, the full join returns all rows from both the Customers and Orders tables. The result set includes customers who have placed orders as well as customers who have not placed any orders yet. For the customers who have not placed any orders, NULL values are returned for the OrderID and OrderDate columns.

Note: It's important to mention that the syntax and availability of full join may vary slightly depending on the specific database management system (DBMS) you are using. Some databases may not support the FULL JOIN syntax directly, but you can achieve the same result using other techniques such as UNION ALL and LEFT JOIN/RIGHT JOIN combinations.