Что такое SELF JOIN в SQL?

Что такое SELF JOIN в SQL

SELF JOIN — это операция объединения таблицы с самой собой. Это используется, когда нужно сравнить данные в рамках одной таблицы или построить иерархические связи.

Основная идея

Вместо того чтобы соединять две разные таблицы, SELF JOIN соединяет таблицу с её собственной копией (используя разные alias):

SELECT t1.column1, t2.column2
FROM table_name t1
JOIN table_name t2
ON t1.id = t2.parent_id;

Пример 1: Иерархия сотрудников

Типичный случай: таблица сотрудников, где каждый может иметь менеджера.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    salary INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees VALUES
(1, 'Alice', NULL, 100000),
(2, 'Bob', 1, 80000),
(3, 'Charlie', 1, 75000),
(4, 'David', 2, 60000),
(5, 'Eve', 2, 65000);

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

Пример 2: Поиск коллег

Найти сотрудников одного менеджера:

SELECT 
    e1.name AS employee1,
    e2.name AS employee2,
    m.name AS common_manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
JOIN employees m ON e1.manager_id = m.id
WHERE e1.id < e2.id
AND e1.manager_id IS NOT NULL;

Пример 3: Сравнение зарплат

Найти, чья зарплата меньше менеджера:

SELECT 
    e.name AS employee,
    e.salary AS emp_salary,
    m.name AS manager,
    m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary < m.salary;

Пример 4: Иерархия товаров

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (parent_id) REFERENCES products(id)
);

INSERT INTO products VALUES
(1, 'Electronics', NULL, NULL),
(2, 'Phones', 1, NULL),
(3, 'Laptops', 1, NULL),
(4, 'iPhone15', 2, 999.99),
(5, 'MacBook', 3, 1999.99);

SELECT 
    parent.name AS category,
    child.name AS subcategory,
    child.price
FROM products child
LEFT JOIN products parent ON child.parent_id = parent.id
WHERE child.parent_id IS NOT NULL;

Пример 5: Поиск друзей друзей

CREATE TABLE friendships (
    user_id INT,
    friend_id INT,
    PRIMARY KEY (user_id, friend_id)
);

SELECT DISTINCT u.id, u.name
FROM users u
JOIN friendships f1 ON u.id = f1.friend_id
JOIN friendships f2 ON f1.user_id = f2.friend_id
WHERE f2.user_id = 1
AND u.id NOT IN (SELECT friend_id FROM friendships WHERE user_id = 1)
AND u.id != 1;

SELF JOIN в Java/JPA

@Entity
public class Employee {
    @Id
    private Long id;
    private String name;
    private int salary;
    
    @ManyToOne
    @JoinColumn(name = "manager_id")
    private Employee manager;
    
    @OneToMany(mappedBy = "manager")
    private List<Employee> subordinates;
}

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    List<Employee> findByManager(Employee manager);
    
    @Query("SELECT e.manager FROM Employee e WHERE e.id = ?1")
    Employee findManagerById(Long employeeId);
}

public class EmployeeService {
    @Autowired
    private EmployeeRepository repo;
    
    public List<Employee> getTeam(Long managerId) {
        Employee manager = repo.findById(managerId).orElseThrow();
        return repo.findByManager(manager);
    }
}

INNER JOIN vs LEFT JOIN

INNER JOIN: только те, кто имеет связь
SELECT e.name, m.name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

LEFT JOIN: все, включая NULL
SELECT e.name, m.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Когда использовать SELF JOIN

1. Иерархические отношения (сотрудник-менеджер, товар-категория)
2. Одноуровневые связи (друзья, коллеги)
3. Поиск предыдущего/следующего элемента
4. Сравнение в таблице (кто зарабатывает больше?)
5. Рекурсивные отношения (CTE с SELF JOIN)

Оптимизация

ALTER TABLE employees ADD INDEX idx_manager_id (manager_id);
EXPLAIN SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Вывод

SELF JOIN — это мощный инструмент для работы с иерархическими данными. Основная идея: используй разные alias для одной таблицы и соединяй их по логике отношения (родитель-потомок, менеджер-подчинённый).