SQL FULL OUTER JOIN Keyword

 In SQL, the FULL OUTER JOIN keyword is used to combine the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns all rows from both tables, matching rows where possible. If there is no match, the result will contain NULL values for the columns of the table that does not have a matching row.

Syntax:

SELECT column1, column2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Explanation:

• table1 and table2 are the two tables to be joined.
• common_column is the column that both tables share (the one used for comparison).
• FULL OUTER JOIN ensures that all records from both tables are included in the result set. If a record from table1 does not have a matching record in table2, NULL values will be placed for the columns of table2, and vice versa.

Example:

Suppose you have two tables, employees and departments:

Table 1: employees

emp_id	emp_name	dept_id
1	Alice	101
2	Bob	102
3	Charlie	103
4	David	NULL

Table 2: departments

dept_id	dept_name
101	HR
102	IT
104	Finance

Query:

SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
FULL OUTER JOIN departments
ON employees.dept_id = departments.dept_id;

Result:

emp_id	emp_name	dept_name
1	Alice	HR
2	Bob	IT
3	Charlie	NULL
4	David	NULL
NULL	NULL	Finance

Key Points:

• Rows from employees with no matching rows in departments will show NULL in the dept_name column.
• Rows from departments with no matching rows in employees will show NULL in the emp_id and emp_name columns.
• The result contains all rows from both tables, even when there is no match.

When to Use:

• When you want to ensure that no data is excluded from either table, even if there is no corresponding match.