SQL SELF JOIN

Introduction

In this chapter, we will focus on the SELF JOIN operation in SQL. The SELF JOIN keyword is used to join a table to itself as if it were two separate tables. This type of join is useful for querying hierarchical data or comparing rows within the same table. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the SELF JOIN effectively.

What is a SELF JOIN?

A SELF JOIN is a regular join but the table is joined with itself. It allows you to compare rows within the same table, which can be particularly useful for hierarchical data, such as organizational charts or family trees.

Syntax for SELF JOIN

Basic Syntax

SELECT a.columns, b.columns
FROM table a
JOIN table b
ON a.common_column = b.common_column;

• a and b: Aliases for the same table.
• common_column: The column used to join the table with itself.

Step-by-Step Example

Sample Table

First, let's create a sample table named employees with hierarchical data.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    manager_id INT, -- Self-referencing foreign key
    salary DECIMAL(10, 2)
);

Insert Sample Data

INSERT INTO employees (first_name, last_name, email, manager_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', NULL, 90000), -- Top-level manager
('Sita', 'Patel', 'sita.patel@example.com', 1, 60000), -- Managed by Ramesh
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000), -- Managed by Ramesh
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000), -- Managed by Sita
('Ravi', 'Verma', 'ravi.verma@example.com', 2, 50000); -- Managed by Sita

Using SELF JOIN

To retrieve employees along with their managers:

SELECT e1.first_name AS Employee, e1.last_name AS EmployeeLastName, e2.first_name AS Manager, e2.last_name AS ManagerLastName
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

Output

Employee	EmployeeLastName	Manager	ManagerLastName
Ramesh	Kumar	NULL	NULL
Sita	Patel	Ramesh	Kumar
Arjun	Singh	Ramesh	Kumar
Priya	Sharma	Sita	Patel
Ravi	Verma	Sita	Patel

In this example, the employees table is joined with itself to retrieve employees along with their managers.

Practical Use Case for SELF JOIN

A practical use case for SELF JOIN could be querying an organizational chart to find all employees and their respective managers.

Example

To find all employees who share the same manager:

SELECT e1.first_name AS Employee1, e1.last_name AS Employee1LastName, e2.first_name AS Employee2, e2.last_name AS Employee2LastName, e1.manager_id
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.id <> e2.id;

Output

Employee1	Employee1LastName	Employee2	Employee2LastName	manager_id
Sita	Patel	Arjun	Singh	1
Arjun	Singh	Sita	Patel	1
Priya	Sharma	Ravi	Verma	2
Ravi	Verma	Priya	Sharma	2

In this example, we join the employees table with itself to find all pairs of employees who share the same manager.

SELF JOIN with Aggregate Functions

You can also use aggregate functions with SELF JOIN to perform calculations on the joined table.

SELECT m.first_name AS Manager, COUNT(e.id) AS NumberOfEmployees, SUM(e.salary) AS TotalSalary
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.first_name;

Output

Manager	NumberOfEmployees	TotalSalary
Ramesh	2	115000
Sita	2	112000

In this example, we join the employees table with itself to calculate the number of employees and total salary for each manager.

Conclusion

The SELF JOIN is used for querying hierarchical data or comparing rows within the same table. This chapter covered the basic syntax for SELF JOIN, provided examples to illustrate its use, and demonstrated practical use cases. Understanding how to use SELF JOIN effectively will greatly enhance your ability to query and analyze hierarchical data in a relational database.