Full Join in SQL Server

Full Join in SQL Server

Introduction:

In SQL Server, a FULL JOIN is a type of join that combines the rows from both the left and right tables, including unmatched rows from both sides. It retrieves all matching rows as well as non-matching rows from both tables. This join is useful when you want to see all data from both tables, even if some rows don't have corresponding matches in the other table.

Syntax:

The syntax for a FULL JOIN in SQL Server is as follows:

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

Explanation:

• SELECT column_list: Specifies the columns you want to retrieve in the result set.
• table1 and table2: The names of the tables you want to join.
• ON table1.column_name = table2.column_name: The condition used to match rows from both tables based on a specific column.

Example:

Let's illustrate the usage of FULL JOIN with two tables: employees and departments. The employees table contains information about employees, and the departments table contains information about different departments in a company.

employees table:

employee_id	employee_name	department_id
1	John	101
2	Jane	102
3	Bob	101
4	Alice	103

departments table:

department_id	department_name
101	HR
102	Finance
103	IT
104	Marketing

SQL Query:

sql
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;

Output:

employee_id	employee_name	department_name
1	John	HR
2	Jane	Finance
3	Bob	HR
4	Alice	IT
NULL	NULL	Marketing

Explanation of the Output:

In the result set, the FULL JOIN combined all rows from both tables, matching employees to their respective departments based on the department_id. If a match was found, the employee_id, employee_name, and department_name were displayed. If there was no match for a row in either table, the corresponding columns were filled with NULL.

In this example, the employee with employee_id 4 (Alice) has a department_id of 103, which corresponds to the department name "IT." However, department 104 ("Marketing") doesn't have any corresponding employees, so the employee-related columns in that row are NULL. Similarly, employees 1, 2, and 3 have department_ids that correspond to department names "HR" and "Finance."