MySQL SELF JOIN

Introduction

In this chapter, we will learn about the SELF JOIN in MySQL. A SELF JOIN is a join of a table with itself. This is useful when you need to compare rows within the same table, such as finding pairs of rows that meet a certain condition. We will cover the syntax, a simple example, and important considerations for using the SELF JOIN.

Syntax

The basic syntax for the SELF JOIN is:

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

• a and b: Aliases for the same table.
• common_column: The column that is used to match rows within the table.

Example with SELF JOIN

Let’s go through a simple example where we use SELF JOIN to retrieve data from the employees table to find pairs of employees who work in the same department.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, department_id) VALUES
('Rahul', 'Sharma', 1),
('Priya', 'Singh', 2),
('Amit', 'Kumar', 1),
('Neha', 'Verma', 3),
('Sahil', 'Mehta', 2);

5. Use the SELF JOIN:

SELECT a.first_name AS first_employee, a.last_name AS first_employee_last, b.first_name AS second_employee, b.last_name AS second_employee_last, a.department_id
FROM employees a
JOIN employees b ON a.department_id = b.department_id AND a.id <> b.id;

Output

Explanation

In the above example:

• The SELF JOIN is used to join the employees table with itself.
• The aliases a and b are used to distinguish between the two instances of the employees table.
• The ON clause specifies that the join condition is that the department_id in both instances should be the same, and the id should not be equal to avoid pairing the same employee.
• The result set includes pairs of employees who work in the same department.

Important Considerations

• Aliasing: Use table aliases to distinguish between the two instances of the table in the SELF JOIN.
• Comparison: The SELF JOIN is useful for comparing rows within the same table, such as finding relationships or hierarchies.
• Performance: SELF JOIN can impact performance, especially on large tables. Ensure appropriate indexing on the columns used in the join condition to optimize query performance.

Conclusion

The SELF JOIN is used for comparing rows within the same table in MySQL. This chapter covered the syntax and usage of the SELF JOIN, provided a simple example with the employees table, and discussed important considerations.