SQL FULL JOIN or SQL FULL OUTER JOIN is used to combine the results of both left join and right join, i.e. it returns all the rows from both tables where there is a match and also the unmatched rows from either of the tables.
Syntax of SQL FULL JOIN
SELECT column1, column2, column3, ... FROM table1 FULL JOIN table2 ON table1.column = table2.column;
Example of SQL FULL JOIN
Consider the following two tables, “departments” and “employees”:
| DepartmentID | DepartmentName |
|---|---|
| D001 | IT |
| D002 | Marketing |
| D003 | HR |
| D004 | Sales |
| EmployeeID | EmployeeName | DepartmentID |
|---|---|---|
| E001 | John | D001 |
| E002 | Jane | D002 |
| E003 | Bob | D003 |
| E004 | Alice | D004 |
| E005 | Eve |
Now, if we want to return all the departments and their respective employees, we can use the following SQL FULL JOIN statement:
SELECT departments.DepartmentID, departments.DepartmentName, employees.EmployeeID, employees.EmployeeName FROM departments FULL JOIN employees ON departments.DepartmentID = employees.DepartmentID;
The output of this SQL FULL JOIN statement would be:
| DepartmentID | DepartmentName | EmployeeID | EmployeeName |
|---|---|---|---|
| D001 | IT | E001 | John |
| D002 | Marketing | E002 | Jane |
| D003 | HR | E003 | Bob |
| D004 | Sales | E004 | Alice |
| E005 | Eve |
As you can see, the SQL FULL JOIN statement returns all the departments and their respective employees, as well as the employees who don’t have a matching department, represented by NULL values in the “DepartmentID” and “DepartmentName” columns.
Advantages of using SQL FULL JOIN
- It returns all the rows from both tables, including the unmatched rows from either of the tables.
- It helps to get a complete picture of the data from both tables, making it easier to identify any missing values or data discrepancies.
- It can be useful in cases where you need to combine data from multiple tables, and you want to see all the data even if there is no matching value in one of the tables.
Disadvantages of using SQL FULL JOIN
- It can produce a large amount of data, making it difficult to analyze and interpret the results, especially when dealing with large datasets.
- It can also slow down the query performance, as the database has to process a larger amount of data to produce the result.
Conclusion
SQL FULL JOIN or SQL FULL OUTER JOIN is a useful operation in SQL when you want to combine data from multiple tables and get a complete picture of the data, including the unmatched rows from either of the tables. However, it’s important to consider the amount of data being processed and the query performance when using this operation.
