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:
sqlSELECT 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
andtable2
: 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:
sqlSELECT 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_id
s that correspond to department names "HR" and "Finance."
0 Comments