SQL FULL OUTER JOIN Keyword

 The FULL OUTER JOIN keyword in SQL is used to combine the result sets of two tables based on a related column, and it returns all records from both tables. When there is no match, it returns NULL values for the columns of the table that does not have a matching record.

Syntax:

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

Explanation:

• table1 and table2: The tables you want to join.
• column1, column2, ...: The columns you want to retrieve from the tables.
• ON table1.column = table2.column: The condition that specifies the related columns in the two tables.

Behavior:

• When there's a match: The matching rows from both tables are returned.
• When there's no match in the first table: The result will show NULL for columns from the first table, but the columns from the second table will be populated.
• When there's no match in the second table: The result will show NULL for columns from the second table, but the columns from the first table will be populated.

Example:

Consider two tables:

Employees

employee_id	name
1	Alice
2	Bob
3	Charlie

Departments

department_id	employee_id	department_name
10	1	HR
20	2	IT
30	4	Finance

A FULL OUTER JOIN on employee_id would return:

SELECT e.employee_id, e.name, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d
ON e.employee_id = d.employee_id;

Result:

employee_id	name	department_name
1	Alice	HR
2	Bob	IT
3	Charlie	NULL
4	NULL	Finance

In this example:

• Alice and Bob have matching records in both tables.
• Charlie has no department, so the department_name is NULL.
• The Finance department has no corresponding employee, so the name is NULL.

Notes:

• Not all database management systems (DBMS) support FULL OUTER JOIN. Some may have limitations or use alternatives like UNION to simulate the same behavior.