SQL FULL JOIN – Tutorial with Examples

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.

Leave a Reply

Your email address will not be published. Required fields are marked *