Using FULL JOIN in SQL
The he FULL JOIN clause, also known as FULL OUTER JOIN, is used in SQL to combine rows from two tables. It returns all the rows from both tables, regardless of whether there is a match between the columns. If there is no match, NULL values are returned for the columns from the table that does not have a matching row.
Syntax of FULL JOIN
SELECT column1, column2, ... FROM table1 FULL JOIN table2 ON table1.common_column = table2.common_column;
- table1: The first table in the join operation (can be referred to as the left table).
- table2: The second table in the join operation (can be referred to as the right table).
- common_column: The column that exists in both tables and is used to match the rows.
Example of FULL JOIN
Consider two tables: employees and departments.
Table 1: employees
employee_id | first_name | last_name | department_id |
---|---|---|---|
1 | John | Doe | 101 |
2 | Jane | Smith | 102 |
3 | Jim | Brown | NULL |
4 | Jill | White | 103 |
Table 2: departments
department_id | department_name |
---|---|
101 | HR |
102 | IT |
104 | Marketing |
SQL Query
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;
Result:
first_name | last_name | department_name |
---|---|---|
John | Doe | HR |
Jane | Smith | IT |
NULL | NULL | Marketing |
Jim | Brown | NULL |
Jill | White | NULL |
Explanation of the Query
The query selects the first_name, last_name, and department_name columns.
It performs a FULL JOIN between the employees table and the departments table.
The ON keyword specifies the condition for the join, which is that the department_id column in employees must match the department_id in departments.
Since Marketing does not have any matching employees, it is included with NULL values for first_name and last_name.
Additionally, the employee Jim Brown is included with NULL for department_name since there is no matching department for him.
The HR and IT departments have matching employees, so both are listed with employee details.
Key Points:
- A FULL JOIN includes rows from both the left and right tables, even if there is no match between the columns.
- If no match is found in one of the tables, NULL values are returned for the columns from that table.
- A FULL JOIN is useful when you want to include every record from both tables, even if there is no match between them.
Use Cases for FULL JOIN:
- When You Need Complete Data: Use FULL JOIN when you need all records from both tables, including unmatched rows.
- When Handling Missing Data: It is useful when you want to identify rows in one table that do not have a match in the other table, ensuring no data is excluded from either side.
Conclusion
The FULL JOIN operation is helpful when you need to include all rows from both tables, regardless of whether they have matching rows. It ensures that no data is excluded from the query result and uses NULL to represent missing values from either table.