SQL Self Join – Basics to Get Started!

Hey, folks! In this article, we will be focusing on SQL Self Join in detail. As we have already seen in our series of SQL Join tutorial, Joins enables us to associate information from different record tables in accordance with the condition.

Now, in this post, we will unveil the working of Self Join in detail.

What is Self Join in SQL?

SQL Self Join enables the administrator to join the single table with itself. That is, it connects and associates the data records with itself.

Thus, a Self Join maintains a Unary relationship between itself. Here, every record(row) of the table is combined and associated with itself and with every other record of the table.

Self Joins are useful when we come across situations wherein the data with the same table needs to be compared.

Moreover, it is useful to model the hierarchies within the single table of the database. Now, let us focus on the structure of Self Join in the upcoming section.

Syntax of SQL Self Join

Have a look at the below syntax!

SELECT columns
FROM table obj1, table obj2
WHERE condition;

Here, we create two different objects of the same table and refer the table as two different tables within the same database.

Thus, it can be referred to as a join among two replicas/copies of the single table.

Example of Self Join

Initially, we have created a table ‘Stud_Info’ using SQL Create query to store the student information. Further, we have added values to it using Insert query.

create table Stud_Info(stud_id integer, Name varchar(200), sub varchar(200));
  
insert into Stud_Info(stud_id, Name, sub) values(001, 'John','Maths');
  
insert into Stud_Info(stud_id, Name, sub) values(002, 'Mary','Science');
  
insert into Stud_Info(stud_id, Name, sub) values(003, 'Ray','Social Science');
  
insert into Stud_Info(stud_id, Name, sub) values(004, 'Jim','History');
  
insert into Stud_Info(stud_id, Name, sub) values(005, 'Rick','Geo');

Now, we apply Self Join on the above table to link the data records with the other records of the same table.

Example:

SELECT X.Name AS Student_Name,Y.sub AS Subject
FROM Stud_Info X, Stud_Info Y
WHERE X.stud_id < Y.stud_id;

Output:

Conclusion

By this, we have come to the end of this topic. Feel free to comment below, in case you come across any question.

Till then, Happy Learning!!

References

• SQL Self Join — Documentation