Blog

Let’s dive into learning the basics of SQL Self Join. SQL Self Join is used when you want to join a table to itself. This is useful when you have a table with hierarchical data or when you want to compare rows within the same table. In this tutorial, we’ll cover the concept of SQL Self Join with detailed explanations and examples.

1. Understanding Self Join:

A self-join is a regular join, but the table being joined is the same table as the one in the FROM clause. It allows you to compare rows within the same table.

2. Syntax:

The basic syntax for a self-join in SQL is as follows:

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

• table_name: Name of the table being joined.
• t1 and t2: Aliases for the same table to distinguish between the two instances.
• common_column: Column(s) used for joining the table to itself.

3. Example Scenario:

Let’s say we have a table named employees which stores information about employees and their managers. The table has columns employee_id and manager_id, where manager_id refers to the employee_id of the manager.

4. Creating Sample Data:

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

INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'John', NULL),
(2, 'Alice', 1),
(3, 'Bob', 1),
(4, 'Charlie', 2),
(5, 'Diana', 3);

• Here, NULL in manager_id represents top-level managers.

5. Performing a Self Join:

Let’s write a query to retrieve the name of each employee and their manager’s name.

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

• In this query:
• e is an alias for the employees table representing the employees.
• m is an alias for the employees table representing the managers.
• We’re joining employees table with itself using the manager_id and employee_id.
• e.manager_id = m.employee_id ensures that we match each employee’s manager_id with their manager’s employee_id.

6. Understanding the Output:

The output of the above query would be:

employee_name | manager_name
--------------|--------------
Alice         | John
Bob           | John
Charlie       | Alice
Diana         | Bob

• Each row represents an employee and their corresponding manager.

Conclusion

In this tutorial, you’ve learned the basics of SQL Self Join. You now understand how to perform self-joins, their syntax, and have seen an example scenario with sample data. Self-joins are particularly useful when working with hierarchical data structures or comparing rows within the same table. Experiment with different scenarios to solidify your understanding.