SELF JOIN

A self join is a table joined to itself. There is nothing special about the SQL syntax involved — it is still just an INNER JOIN, LEFT JOIN, or any other join type you already know — the only thing that changes is that both sides of the join refer to the same table. That single detail forces one requirement: since a table cannot be joined to a table of the same name without ambiguity, both references to the table must be given different aliases.

Aliases are mandatory in a self join

Without two distinct aliases, the database has no way to tell which "copy" of the table a column reference like employee_id belongs to. See the Table Aliases page for a refresher on alias syntax — self joins are the case where aliases go from a readability nicety to an outright requirement.

Classic use case: employees and their managers

The textbook example of a self join is an employees table where each row has a manager_id column that points to another row in the very same table — the employee_id of that person's manager. To display each employee alongside their manager's name, you join the table to itself: once representing "the employee" and once representing "the employee's manager."

employees table with a self-referencing manager_id

SQL

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name        VARCHAR(50),
  manager_id  INT
);

INSERT INTO employees (employee_id, name, manager_id) VALUES
  (1, 'Grace',   NULL),  -- Grace is the top of the org chart, no manager
  (2, 'Henry',   1),     -- Henry reports to Grace
  (3, 'Ingrid',  1),     -- Ingrid reports to Grace
  (4, 'Jamal',   2);     -- Jamal reports to Henry

Worked example

Each employee alongside their manager's name

SQL

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

employee_name | manager_name
--------------+-------------
Grace         | NULL
Henry         | Grace
Ingrid        | Grace
Jamal         | Henry

In this query, e is the alias standing in for "the employee row" and m is the alias standing in for "the manager row" — even though both aliases point at the exact same employees table. A LEFT JOIN is used deliberately so that Grace, who has no manager (manager_id is NULL), still appears in the results with NULL in the manager_name column instead of being dropped, the way an INNER JOIN would drop her.

Other common uses

Self joins show up anywhere a table has a relationship to itself: comparing employees within the same department, finding pairs of products that are frequently priced identically, matching consecutive rows in a sequence, or finding duplicate records that share the same value in some column but have different primary keys.

A self join is a normal join where both tables in the FROM/JOIN clauses are actually the same table.
Table aliases are required to distinguish the two roles the table is playing.
The employees/manager_id pattern is the classic example — one row referencing another row in the same table.
Use LEFT JOIN in a self join when some rows (like a top-level manager) should still appear despite having no match.