FULL OUTER JOIN - All Records When There is a Match in Either Table in SQL

The FULL OUTER JOIN is used in SQL to combine the results of both a LEFT JOIN and a RIGHT JOIN. It returns all records from both the left and the right table. If there is no match, NULL values are returned for the columns from the table that does not have the matching row. In other words, the FULL OUTER JOIN ensures that no rows from either table are excluded, even if there are no corresponding matches.

1. Syntax of FULL OUTER JOIN

The basic syntax of a FULL OUTER JOIN is as follows:

      SELECT column1, column2, ...
      FROM table1
      FULL OUTER JOIN table2
      ON table1.column = table2.column;
    

In this syntax:

• table1 is the left table, and table2 is the right table.
• column1, column2 are the columns you want to retrieve from the tables.
• table1.column and table2.column are the columns from each table being compared for a match.

2. Example of FULL OUTER JOIN

Consider two tables: Employees and Departments. The Employees table contains employee details, and the Departments table contains department details. If you want to list all employees and departments, regardless of whether they have a match in the other table, you can use a FULL OUTER JOIN:

      SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
      FROM Employees
      FULL OUTER JOIN Departments
      ON Employees.DepartmentID = Departments.DepartmentID;
    

This query will return all records from both the Employees and Departments tables. If an employee is not assigned to a department, or if a department has no employees, NULL values will appear for the missing data.

3. Example of FULL OUTER JOIN with No Matches

If there are departments in the Departments table that have no employees, or employees in the Employees table that are not assigned to any department, the FULL OUTER JOIN will still include these records. For example, if the "HR" department has employees, but the "IT" department has no employees, and some employees are not assigned to any department, the query might return this result:

      EmployeeID | Name        | DepartmentName
      ------------------------------------------
      101        | John Doe    | HR
      102        | Jane Smith  | HR
      NULL       | NULL        | IT
      103        | Bob Brown   | NULL
    

Here:

• The "HR" department has employees, so their details are shown.
• The "IT" department has no employees, so NULL values appear for the employee details.
• Employee "Bob Brown" is not assigned to any department, so NULL appears for the DepartmentName.

4. Example of FULL OUTER JOIN with Multiple Tables

You can use the FULL OUTER JOIN with multiple tables. For instance, if you want to list all employees, departments, and their respective orders, you can join three tables:

      SELECT Employees.Name, Departments.DepartmentName, Orders.OrderID
      FROM Employees
      FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
      FULL OUTER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;
    

This query will return all employees, departments, and orders. If an employee has not placed an order, or if there is a department without employees, NULL values will be shown for the missing data.

5. FULL OUTER JOIN with Conditions

You can also filter the results of a FULL OUTER JOIN by using the WHERE clause. For example, if you want to find all employees and their respective departments, and only show records where an employee is assigned to a department, you can use the following query:

      SELECT Employees.Name, Departments.DepartmentName
      FROM Employees
      FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
      WHERE Employees.DepartmentID IS NOT NULL;
    

This query will exclude employees who do not have a department assigned and departments that have no employees.

6. FULL OUTER JOIN with Aliases

To make your queries cleaner, you can use table aliases. Here's an example of using aliases with a FULL OUTER JOIN:

      SELECT e.Name, d.DepartmentName, o.OrderID
      FROM Employees AS e
      FULL OUTER JOIN Departments AS d
      ON e.DepartmentID = d.DepartmentID
      FULL OUTER JOIN Orders AS o
      ON e.EmployeeID = o.EmployeeID;
    

In this query, e is used as an alias for the Employees table, d for the Departments table, and o for the Orders table. This makes the query more readable.

7. Key Points to Remember About FULL OUTER JOIN

• The FULL OUTER JOIN returns all rows from both the left and right tables, with NULL values for columns that do not have matching rows in the other table.
• It combines the behavior of both LEFT JOIN and RIGHT JOIN, ensuring that no records from either table are excluded.
• By using a WHERE clause, you can filter the results based on conditions, such as excluding rows with NULL values.

8. Conclusion

The FULL OUTER JOIN is a powerful SQL operation that allows you to combine data from two tables, ensuring that all records from both tables are included in the result set. It is particularly useful when you need to ensure that no data from either table is left out, even if there are no matching records. Understanding how to use the FULL OUTER JOIN can help you perform more comprehensive queries and gain a fuller picture of your data.