Self join patterns in PostgreSQL

Introduction

A self join lets you compare rows in a table to other rows in the same table. It helps find relationships within the same data.

Finding employees who are managers of other employees in the same employee table.

Comparing products to other products in the same list to find similar items.

Finding pairs of dates in a table where one date is before another.

Checking if a person appears more than once with different details in the same table.

Syntax

PostgreSQL

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.common_field = b.common_field
WHERE condition;

Use table aliases (like 'a' and 'b') to distinguish the same table used twice.

The JOIN condition defines how rows from the table relate to each other.

Examples

Finds each employee and their manager by joining the employees table to itself.

PostgreSQL

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

Finds pairs of products in the same category but different products.

PostgreSQL

SELECT p1.product_name, p2.product_name
FROM products p1
JOIN products p2 ON p1.category = p2.category AND p1.id <> p2.id;

Finds all pairs of dates where one date is before another.

PostgreSQL

SELECT a.date AS earlier_date, b.date AS later_date
FROM dates a
JOIN dates b ON a.date < b.date;

Sample Program

This creates an employees table with a manager relationship. The query shows each employee and their manager's name.

PostgreSQL

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

INSERT INTO employees (name, manager_id) VALUES
('Alice', NULL),
('Bob', 1),
('Charlie', 1),
('David', 2);

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
ORDER BY e1.id;

Important Notes

Always use aliases to avoid confusion when joining a table to itself.

LEFT JOIN is useful if some rows may not have a matching related row.

Self joins can be slower on large tables, so use indexes on join columns.

Summary

Self join lets you compare rows within the same table.

Use table aliases to keep track of each instance of the table.

Common use cases include hierarchical data and finding related pairs.