FULL OUTER JOIN

A FULL OUTER JOIN is a type of join that returns all the rows from both tables, with matching rows from both sides wherever available. If there is no match, the result will contain NULL on the side that does not have a match.

Syntax of FULL OUTER JOIN:

SELECT column_names

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name;

Key Points about FULL OUTER JOIN:

1. Return Rows from Both Tables:
• Matched Rows: The rows where the specified condition (ON table1.column_name = table2.column_name) is met will be returned.
• Unmatched Rows: If a row from table1 does not find a matching row in table2, it will still appear in the result, but the columns from table2 will be NULL.
• Similarly, if a row from table2 does not find a match in table1, it will still appear in the result, but the columns from table1 will be NULL.
2. NULL for Unmatched Values:
• When a row in table1 does not have a corresponding match in table2, the result will show NULL for the columns of table2 in that row.
• Likewise, when a row in table2 does not have a corresponding match in table1, the result will show NULL for the columns of table1.
3. Result Set:
• The result set of a FULL OUTER JOIN includes all the rows from both tables, with the condition applied for matching records. The table that does not have a match will show NULL in the columns of the other table.
4. Performance:
• FULL OUTER JOINs are generally more resource-intensive than INNER or LEFT/RIGHT OUTER JOINS because they must return all records from both tables.
• In terms of performance, it is recommended to use FULL OUTER JOIN only when necessary, especially on large datasets.

Example 1: Basic FULL OUTER JOIN

Let’s consider two tables employees and departments.

employees:

emp_id	emp_name	dept_id
1	Alice	10
2	Bob	20
3	Charlie	30

departments:

dept_id	dept_name
10	HR
20	Finance
40	Marketing

The query to perform a FULL OUTER JOIN would look like this:

SELECT employees.emp_id, employees.emp_name, employees.dept_id, departments.dept_name

FROM employees

FULL OUTER JOIN departments

ON employees.dept_id = departments.dept_id;

Result:

emp_id	emp_name	dept_id	dept_name
1	Alice	10	HR
2	Bob	20	Finance
3	Charlie	30	NULL
NULL	NULL	40	Marketing

Explanation of the Result:

• Rows 1 and 2: These rows match between employees and departments based on dept_id, so all columns are returned.
• Row 3: The employee "Charlie" in employees does not have a corresponding dept_id in departments (because dept_id = 30 is missing in departments), so NULL appears in the dept_name column.
• Row 4: The department "Marketing" has no employees associated with it, so NULL appears in the emp_id and emp_name columns.

Example 2: FULL OUTER JOIN with more than one match

Let’s consider two tables with multiple matches.

orders:

order_id	customer_id	amount
1001	101	200
1002	102	150
1003	103	300

customers:

customer_id	customer_name
101	Alice
102	Bob
104	Carol

The FULL OUTER JOIN query would be:

SELECT orders.order_id, orders.customer_id, orders.amount, customers.customer_name

FROM orders

FULL OUTER JOIN customers

ON orders.customer_id = customers.customer_id;

Result:

order_id	customer_id	amount	customer_name
1001	101	200	Alice
1002	102	150	Bob
1003	103	300	NULL
NULL	104	NULL	Carol

Explanation of the Result:

• Rows 1 and 2: These rows have matching customer_id values between orders and customers, so the result returns the order details along with the customer name.
• Row 3: The order with customer_id = 103 does not have a matching customer in the customers table, so the customer_name is NULL.
• Row 4: The customer with customer_id = 104 does not have any corresponding orders in the orders table, so the order_id and amount are NULL.

When to Use FULL OUTER JOIN:

1. Combining All Data: Use a FULL OUTER JOIN when you need to combine all rows from both tables, even if there is no match.
2. Handling Missing Data: This type of join is useful when you want to see records that may be missing in either of the tables.
3. Data Analysis: It can be helpful in scenarios where both matched and unmatched data needs to be examined, for example, when analyzing sales records alongside customer data.

Conclusion:

The FULL OUTER JOIN is a useful operation when you need to retrieve all records from two tables, including those that do not have a match in the other table. It returns a complete set of rows from both tables and fills in missing matches with NULL. While powerful, it should be used judiciously, especially on large datasets, due to potential performance concerns.