Open In App

MySQL SELF JOIN

Last Updated : 22 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

Joins are very important for effective data retrieval and analysis. The ‘JOIN’ clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like inner join, outer join, left join, right join, full join, and self join.

In this article, we will discuss the concept of MySQL SELF JOIN, exploring various examples to illustrate its usage. A SELF JOIN is a special type of join operation where a table is joined with itself. We will demonstrate how to implement MySQL SELF JOIN using both INNER JOIN and LEFT JOIN clauses, providing clear and concise examples to showcase the different scenarios in which SELF JOIN can be applied.

MySQL SELF JOIN

Generally joins are used to combine data from two or more tables based on a common column, but in Self Join we will combine data within the same table itself. Self Join is done by combing rows from the same table based on some specific conditions. To perform Self Join Alias names are used with the Join condition based on the columns that define the relationship. There is no keyword as ‘Self Join’, but it is an ordinary ‘Join’ where a table is joined to itself. Self Join is a special type of join and it is commonly used for creating hierarchy by simplifying the retrieval of interconnected data within the same table.

MySQL SELF JOIN Syntax:

SELECT *

FROM table_name AS t1

JOIN table_name AS t2 ON t1.column_name = t2.column_name;

MySQL SELF JOIN Examples

Table Creation and Insertion of Values

We are going to create a table named employees and we are inserting five employees and their details.

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

INSERT INTO employees VALUES (1, 'John', NULL);
INSERT INTO employees VALUES (2, 'Jane', 1);
INSERT INTO employees VALUES (3, 'Bob', 2);
INSERT INTO employees VALUES (4, 'Alice', 1);
INSERT INTO employees VALUES (5, 'Charlie', 3);

select * from employees;

The above table displays the employees and their details inserted in the table.

Example 1: MySQL SELF JOIN Using INNER JOIN Clause

SELECT e1.employee_id AS employee_id, 
e1.employee_name AS employee_name,
e2.employee_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

In the above example, e1 and e2 are aliases of the same table(employees) and we are joining the columns manager_id and employee_id of the same table using inner join which includes all data that are common from both e1 and e2 aliases. Since we use inner join here, all rows from the employees table with e1 and e2 will be displayed in the result only if they match the condition e1.manager_id = e2.employee_id will be displayed in the result.

Output:

Output-SELF-JOIN-using-INNER-JOIN

Output-SELF JOIN using INNER JOIN

Example 2: MySQL SELF JOIN Using LEFT JOIN Clause

SELECT e1.employee_id AS employee_id, 
e1.employee_name AS employee_name,
e2.employee_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

In the above example, e1 and e2 are aliases of the same table and we are joining the columns manager_id and employee_id of the same table using left join which includes all data from alias e1. Since we use left join here, all rows from the employees table with e1 will be displayed in the result and only the matching rows from the same table with alias e2 based on the condition e1.manager_id = e2.employee_id will be displayed in the result.

Output:

Output-SELF-JOIN-using-LEFT-JOIN

Output-SELF JOIN using LEFT JOIN

Applications of SQL Self Join

SQL Self Join can be used in many different kinds of scenarios like:

  1. Hierarchical Structures: When dealing with hierarchical structures, such as organizational charts, where individuals relate to one another within the same table, self joins are helpful.
  2. Comparing Data Within a Table: Self join makes it easier to analyze relationships between various rows by doing comparisons of data within a single table.
  3. Recursive Relationships: When handling recursive relationships—like a table that references itself—inside a table, self join is crucial.
  4. Data Partitioning: When there exist relationships between rows in a table, self join can be used to partition and analyze data within the table.
  5. Tracking Historical modifications: Self Join helps keep track previous changes by enabling you to compare different versions of records within the same table.
  6. Aliases Creation: It makes it possible to create aliases for a table, giving users a mechanism for differentiating between multiple instances of the same table when joining them.

Conclusion

Through this article, we’ve explored the fundamentals of MySQL SELF JOIN, from understanding its syntax to practical applications using INNER JOIN and LEFT JOIN clauses.Whether it’s creating organizational charts, tracking historical changes, or unraveling social networks, the SELF JOIN proves to be a valuable join, providing a clearer picture of relationships within a single table.


Previous Article
Next Article

Similar Reads

Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
4 min read
MySQL RIGHT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, joins enable the merging of multiple tables based on the common columns. In this article, we are going to explore MySQL RIGHT JOINS which is a type of outer join in MySQL. Introduction to MySQL RIGH
7 min read
MySQL DELETE JOIN
MySQL is an open-source, user-friendly, powerful, and popular choice, relational database management system. When maintaining and modifying data, tables usually interact in a complex way. MySQL's DELETE JOIN function is one of its most powerful functions. MySQL DELETE JOIN is explored in detail in this article, which also offers examples to help vi
3 min read
MySQL UPDATE JOIN
A widely used open-source relational database management system that allows you to efficiently store, organize, and retrieve data. Developed by Oracle, My SQL is widely used for building and managing databases that handle interactive websites and applications. We'll discuss the syntax, and demonstrate how this dynamic duo can efficiently modify dat
6 min read
MySQL CROSS JOIN
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL also provides the CROSS JOIN statement to combine
3 min read
MySQL Inner Join
Have you encountered a query requiring you to somehow join or get the data spread across two or more tables? Are you wondering how to achieve the same by writing a MySQL query? Do you want to learn about MySQL INNER JOIN? If your answer to any of these questions is "YES", you have arrived at the correct place. Follow along with the below article an
7 min read
INNER JOIN ON vs WHERE clause in MySQL
Have you ever wondered about the optimal use of INNER JOIN ON versus the WHERE clause in MySQL queries? In this article, we'll delve into the distinctions between these two approaches, elucidating their respective purposes, syntax, and best practices. By the end, you'll have a solid grasp of how to leverage these tools to enhance the efficiency and
4 min read
MySQL LEFT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, Joins enable the merging of multiple tables based on the common columns.  In this article, we are going to explore MySQL LEFT JOIN Keyword which is a type of Outer Join in MySQL. We will cover every
7 min read
How to Use Full Outer Join in MySQL
The FULL OUTER JOIN or FULL JOIN is a useful technique in MySQL that allows us to fetch all rows from both tables when there is a match in the records. In this article, we will learn about the FULL OUTER JOIN through practical examples and MySQL queries. MySQL FULL OUTER JOINThe FULL OUTER JOIN in MySQL returns all rows of both tables involved in t
4 min read
When should we use CROSS APPLY over INNER JOIN in MySQL
CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data. It becomes very useful when we need to perform row-
4 min read