What is Self-Join in DBMS?

In DBMS, we sometimes need to fetch various information from the same relation by applying some conditions or constraints to its fields. In such cases, a self-join can be used to obtain the desired result. Hence, in this blog, we will see what is self-join in DBMS and how to implement it. In this blog, we will use the MySQL database for performing the self-join to the database tables. Now, let us learn self-join in detail.

Self-Join

A self-join is a join that can be used to join a table with itself. Hence, it is a unary relation.

In a self-join, each row of the table is joined with itself and all the other rows of the same table. Thus, a self-join is mainly used to combine and compare the rows of the same table in the database.

But whenever we perform self-join, it creates ambiguity because we have to use the name of the same table again. In a query, if we write the same table name twice, it will give an error. So as to avoid these circumstances, we have to use 'alias' names for the same tables. An alias name simply provides a table with a different name for performing such operations. In MySQL, an 'AS' keyword can be used to provide alias names to the tables.

The basic query syntax for self-join is as follows:

SELECT t1.column1, t2.column2 FROM table AS t1, table AS t2 WHERE condition;

In the above syntax, t1 and t2 are the alias names, column1 and column2 are the attributes or fields of the table, and some condition needs to be applied to the WHERE clause.

Now, let us understand the self-join with an example.

Consider the below mentioned 'student' table with id, name, city, and department as the attributes.

We can write a self-join query in MySQL fetching some specific fields and giving some conditions in the following manner.

SELECT s1.name AS student1, s2.name AS student2, s1.city AS city FROM student AS s1, student AS s2 WHERE s1.city = s2.city AND s1.id <> s2.id;

The output for the above query is as follows:

This is all about self-join in DBMS. Hope you learned something new today. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!