What is the difference between an INNER and SELF JOIN?

An INNER JOIN and a SELF JOIN serve different purposes in SQL, though they are often confused. An INNER JOIN combines rows from two tables based on a matching condition, returning only the rows where there’s a match in both tables. A SELF JOIN, on the other hand, is not a distinct type of join but a technique where a table is joined to itself. This means a SELF JOIN can use any join type (INNER, LEFT, etc.), but it operates on the same table twice using aliases. The key difference lies in their application: INNER JOIN connects two tables, while SELF JOIN explores relationships within a single table.

To illustrate, consider an employees table with columns for employee_id, name, and manager_id (which references another employee’s employee_id). An INNER JOIN might link employees to a departments table to show which department each employee belongs to. A SELF JOIN, however, would use aliases to treat the employees table as two logical entities. For example, to list employees with their managers, you’d write:

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

Here, the SELF JOIN uses an INNER JOIN to match employees to their managers within the same table. Without aliases, SQL wouldn’t distinguish between the two roles, making the query ambiguous.

SELF JOINs are useful for hierarchical data (like organizational charts) or comparing rows within a table (e.g., finding duplicate entries). INNER JOINs are broader, used whenever data from two separate tables needs merging based on a relationship. Importantly, a SELF JOIN doesn’t require an INNER JOIN—it could use LEFT JOINs to include employees without managers. The core distinction is that a SELF JOIN is a structural approach (reusing the same table), while INNER JOIN defines the matching logic between datasets.