Typically, when you want to combine data that exists in two different tables, you use a type of JOIN. This allows you to find the relationships between existing data in these tables. However, when you want to get all rows from both tables, even those that don’t match, FULL JOIN comes into play.
FULL JOIN allows you to retrieve all rows from both tables, whether there is a relationship between those rows or not. This type of JOIN can complete the data from either side with NULL for unmatched columns.
This option is useful when you want to generate comprehensive reports from your data, ensuring that no record is missed. Using FULL JOIN is essential when you need to see complete data from each table between the two.
Using SQL FULL JOIN
Let’s illustrate this concept with a simple example. Assume there are two tables named customers and orders. The customers table contains information related to customers, while the orders table contains the list of orders.
To find out which customers placed orders and which did not, you can use FULL JOIN to examine all orders and customers.
SELECT
customers.id,
customers.name,
orders.order_date
FROM
customers
FULL JOIN
orders
ON
customers.id = orders.customer_id;
Explanation of SQL Code
SELECT: Select specific columns that you want to see in the output.
customers.id, customers.name, orders.order_date: The selected columns from both tables that you want to show in the output.
FROM customers: The primary table from which data is selected.
FULL JOIN orders: Use FULL JOIN to combine the rows from both tables.
ON customers.id = orders.customer_id: The condition for matching data between the two tables based on customer identification.