A self-join operates by treating a single table as two distinct entitise through table aliasing. This technique proves essential when modeling hierarchical relationships—such as organizational reporting structures—or when comparing records within the same dataset to identify duplicates or related pairs.

The fundamental pattern requires assigning unique aliases to reference the table twice with in the same query:

SELECT select_list
FROM table_name alias_a
INNER JOIN table_name alias_b ON join_condition;

SELECT select_list
FROM table_name alias_a
LEFT JOIN table_name alias_b ON join_condition;

When traversing recursive relationships like management chains, a LEFT JOIN preserves root-level records that lack parent associations, whereas INNER JOIN returns only connected pairs.

Consider modeling an organizational directory to map personnel and their supervisors:

CREATE TABLE staff (
  staff_id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES staff(staff_id) ON DELETE CASCADE
);

INSERT INTO staff (staff_id, first_name, last_name, manager_id) VALUES
  (1, 'Eleanor', 'Vance', NULL),
  (2, 'Marcus', 'Chen', 1),
  (3, 'Priya', 'Patel', 1),
  (4, 'Thomas', 'Anderson', 2),
  (5, 'Sofia', 'Lorenzo', 2),
  (6, 'William', 'Taylor', 3),
  (7, 'Olivia', 'Martinez', 3);

SELECT 
  s.first_name || ' ' || s.last_name AS team_member,
  m.first_name || ' ' || m.last_name AS reports_to
FROM 
  staff s
  INNER JOIN staff m ON m.staff_id = s.manager_id
ORDER BY 
  reports_to;

To locate distinct record pairs sharing identical attributes—such as equivalent measurements or specifications—apply an inequality condition on the primary key to prevent duplicate pairings and self-matching:

-- Identifying products with identical weights
SELECT 
  a.sku,
  b.sku,
  a.weight_grams
FROM 
  catalog_items a
  INNER JOIN catalog_items b ON a.item_id > b.item_id
    AND a.weight_grams = b.weight_grams;

This approach efficiently retrieves all unique combinations where separate items share common characteristics with out returning mirrored results or a record paired with itself.