Back to course

FULL OUTER JOIN and Self Joins

Database Fundamentals: From Zero to Hero

Lesson 29: FULL OUTER JOIN and Self Joins

1. FULL OUTER JOIN

The FULL OUTER JOIN (or simply FULL JOIN) returns all rows when there is a match in either the left table or the right table.

  • It combines the results of both LEFT JOIN and RIGHT JOIN.
  • Where there is no match, the non-matching side contains NULL.

Use Case

Identifying all data points from two tables and seeing where they overlap, where Table A has non-matches, and where Table B has non-matches.

sql SELECT A.RegionName, B.SalesID FROM Regions A FULL JOIN Sales B ON A.RegionID = B.RegionID;

2. Self Joins

A Self Join is simply a regular join where a table is joined to itself. This is essential for hierarchical data structures (like organizational charts).

Example: Employees and Managers

In an Employees table, the ManagerID column is actually a Foreign Key that references the EmployeeID (Primary Key) within the same table.

sql SELECT E.Name AS EmployeeName, M.Name AS ManagerName FROM Employees E INNER JOIN Employees M -- Joining the table to itself ON E.ManagerID = M.EmployeeID; -- Linking the FK to the PK

Self Joins require the use of distinct table aliases (E and M) to differentiate the role of the table in the query.