SQL Self Join

SQL SELF JOIN

A SELF JOIN is a regular join, but the table is joined with itself. It is useful when rows in the same table relate to other rows in the same table.

Why SELF JOIN?

Common use cases include hierarchical data such as:

• Employees and their managers
• Categories and subcategories
• Records that reference other records in the same table

SELF JOIN Syntax

Because the same table is used twice, you must use table aliases to distinguish the two instances.

SELECT a.column, b.column
FROM table_name AS a
JOIN table_name AS b
  ON a.related_column = b.id;

Example: Employees and Managers

Imagine an employees table where manager_id points to another employee id.

SELECT e.name AS employee_name,
       m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

This returns each employee and their manager. If an employee has no manager, manager_name will be NULL.

Example: Customers in the Same City

You can also match rows that share a common value.

SELECT a.name AS customer_a,
       b.name AS customer_b,
       a.city
FROM customers AS a
JOIN customers AS b
  ON a.city = b.city
WHERE a.id < b.id;

The condition a.id < b.id avoids duplicate pairs and self-matching.

Common Mistakes

• Forgetting to use aliases and getting ambiguous column errors
• Creating duplicate pairs when matching rows (solve with id comparisons)
• Using INNER JOIN when you need rows without a match (use LEFT JOIN)

Next Step

Continue with SQL UNION to learn how to combine the results of multiple SELECT statements.