Self-join

A self-join in SQL occurs when a table is joined with itself. In other words, it's a regular join operation, but the table is referenced twice in the query with different aliases to differentiate between the roles of each occurrence of the table. This is often used when a table has a hierarchical structure or when you want to compare rows within the same table.

Syntax

SELECT t1.column1, t2.column2
FROM your_table t1
JOIN your_table t2 ON t1.some_column = t2.some_column;

- `your_table`: The name of the table being joined with itself.

- `t1` and `t2`: Aliases assigned to the same table to differentiate between the two occurrences.

- `some_column`: The column used for the join condition.

Example

Consider a table named `employees` with a hierarchical structure where each employee has a manager identified by the `manager_id` column:

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

INSERT INTO employees VALUES (1, 'John Doe', NULL);
INSERT INTO employees VALUES (2, 'Jane Smith', 1);
INSERT INTO employees VALUES (3, 'Bob Johnson', 1);
INSERT INTO employees VALUES (4, 'Alice Brown', 2);

To find the names of employees and their managers, you can use a self-join:

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

This query retrieves the names of employees along with the names of their respective managers. The self-join is performed using the `LEFT JOIN` and the join condition `e.manager_id = m.employee_id`.

Self-joins are particularly useful when dealing with hierarchical or recursive relationships within a table. They allow you to model and query relationships where records in the same table are related to each other.