Self Join in SQL

What is a Self Join in SQL?

Self join in SQL is a type of inner join. It is a special type of join that is used to combine the table with itself. This join uses an alias to refer to the table with the different names in the same query. This type of join is used when we want to compare the rows within the same table. This will compare them based on the related columns. Let us understand the syntax of self join in SQL.

Syntax

The syntax of self join in SQL is mentioned below:

SELECT alias_name1.column_name1, alias_name2.column_name2 FROM table_name alias_name1, table_name alias_name2 
WHERE given_condition;

 

In this query, alias_name1 and alias_name2 will be the name of the alias. The name of the table will be table_name with columns column_name1 and column_name2. This query will retrieve the data based on the condition, i.e., given_condition.

Now you might have a doubt about the theoretical part. Let us understand this with the help of examples.

Examples of Self Join

Let us understand a few examples based on the self join in SQL.

Example 1

Suppose we have a table of ninjas with columns for ninjas_id, ninjas_name, and questions_solved. You can use a self-join to find pairs of ninjas with the same number of questions solved. Let us create the table first on our MySQL workbench.

Suppose we are creating a database with the name selfJoinExamples. So we have to run a query:

CREATE DATABASE selfJoinExamples;

 

So this query will create a database:

▼	selfjoinexamples
	Tables
	Views
	Stored Procedures
	Functions

After creating the database, we need to use the database. By writing this query, we can use the database:

USE selfJoinExamples;

 

Now we have to create a table ninjas with the columns ninjas_id, ninjas_name, and question_solved. 

CREATE TABLE ninjas(Ninjas_id INT PRIMARY KEY, Ninjas_name VARCHAR(30), Question_solved INT);

 

This query will create a table ninjas with given columns.

	Field	Type	Null	Key	Default	Extra
>	Ninjas_id	int	NO	PRI	NULL
	Ninjas_name	varchar(30)	YES		NULL
	Question_solved	int	YES		NULL

 

Now, we have to insert data into this table.

INSERT INTO ninjas(Ninjas_id, Ninjas_name, Question_solved) VALUES(1,"Narayan Mishra", 70),(2,"Kanak Rana", 150),(3,"Mehak Goel", 140),(4,"Abhishek Nayak", 85),(5,"Rohit Sharma",70),(6,"Akash",140);

 

To check whether the data is inserted or not, we can run a query:

SELECT * FROM ninjas;

 

This query will give us a table with the data:

	Ninjas_id	Ninjas_name	Question_solved
>	1	Narayan Mishra	70
	2	Kanak Rana	150
	3	Mehak Goel	140
	4	Abhishek Nayak	85
	5	Rohit Sharma	70
	6	Akash	140
·	NULL	NULL	NULL

Now we have to apply the self join in this table ninjas.

SELECT ninja1.Ninjas_name, ninja2.Ninjas_name, ninja1.Question_solved
FROM ninjas ninja1
INNER JOIN ninjas ninja2
ON ninja1.Question_solved = ninja2.Question_solved
AND ninja1.Ninjas_id <> ninja2.Ninjas_id;

 

This query will give the result:

	Ninjas_name	Ninjas_name	Question_solved
>	Rohit Sharma	Narayan Mishra	70
	Akash	Mehak Goel	140
	Narayan Mishra	Rohit Sharma	70
	Mehak Goel	Akash	140

In this query, we have made two aliases, ninja1, and ninja2, to join the table with itself. 

Example 2

Suppose you have a table called problems that contains columns for problem_id, ninjas_id, and problem_solved_date. You want to find pairs of problems that were solved by the same ninja on the same day. You can use a self-join with a WHERE clause to accomplish this:

Before writing a query by using self join on the problems table, we need to create a table problems with given columns.

CREATE TABLE problems(problem_id INT PRIMARY KEY, ninjas_name VARCHAR(30), problem_solved_date date);

 

After running this query, your table problems will be created in your existing database selfJoinExamples:

	Field	Type	Null	Key	Default	Extra
>	problem_id	int	NO	PRI	NULL
	ninjas_name	varchar(30)	YES		NULL
	problem_solved_date	date	YES		NULL

Now, we have to insert the data in the problems table:

INSERT INTO problems(problem_id, ninjas_name, problem_solved_date) VALUES(1,"Narayan Mishra",'2023-01-12'),(12,"Rahul",'2023-02-20'),(21,"Akash",'2023-01-19'),(54,"Narayan Mishra",'2023-01-12'),(67,"Rahul",'2023-02-11');

 

After inserting data, we can check whether data is inserted or not. 

SELECT * FROM problems;

 

This will give us the problems table:

	problem_id	ninjas_name	problem_solved_date
>	1	Narayan Mishra	2023-01-12
	12	Rahul	2023=02-20
	21	Akash	2023-01-19
	54	Narayan Mishra	2023-01-12
	67	Rahul	2023-02-11
·	NULL	NULL	NULL

Now we can apply the self join to perform our operation:

SELECT problem1.problem_id, problem2.problem_id, problem1.ninjas_name, problem1.problem_solved_date
FROM problems problem1
INNER JOIN problems problem2
ON problem1.ninjas_name = problem2.ninjas_name
AND problem1.problem_solved_date = problem2.problem_solved_date
WHERE problem1.problem_id <> problem2.problem_id;

 

This query will give us the table:

	problem_id	problem_id	ninjas_name	problem_solve_date
>	54	1	Narayan Mishra	2023-01-12
	1	54	Narayn Mishra	2023-01-12

In this query, we have used two aliases, problem1, and problem2, to join the table with itself.

Example 3

Suppose we have a table called ninja_courses that contains columns for Course_id, Course_name, and Course_cost. We want to show each course's name along with the cost of the course. You can use a self join in SQL with an ORDER BY clause to order the results by the cost of the course:

Firstly, we have to create a table with the name ninja_courses:

CREATE TABLE ninja_courses(Course_id INT PRIMARY KEY, Course_name VARCHAR(30), Course_cost INT);

 

Then we can check whether the table is created or not:

DESC ninja_courses;

 

Then we will see the table is created:

	Field	Type	Null	Key	Default	Extra
>	Course_id	int	NO	PRI	NULL
	Course_name	varchar(30)	YES		NULL
	Course_cost	int	YES		NULL

Now, we have to insert the data into the table:

INSERT INTO ninja_courses(Course_id, Course_name, Course_cost) 
VALUES(1,"Java", 17000),(2,"C++", 11000),(3,"Python", 14000),(4,"MERN", 20000);

 

Then we have to check whether the data is inserted or not:

SELECT * FROM ninja_courses;

 

This will give us a table with data:

	Course_id	Course_name	Course_cost
>	1	Java	17000
	2	C++	11000
	3	Python	14000
	4	MERN	20000
·	NULL	NULL	NULL

Now, we can apply the self join on the table ninja_courses:

 

SELECT course.Course_name, cost.Course_cost
FROM ninja_courses course
INNER JOIN ninja_courses cost
ON course.Course_cost= cost.Course_cost
ORDER BY cost.Course_cost DESC;

 

This will give us a table:

	Course_name	Course_cost
>	MERN	20000
	Java	17000
	Python	14000
	C++	11000

In this example, we have used the course and cost as aliases. We have retrieved the results in descending order.

Frequently Asked Questions

What is self join in SQL?

A self-join is a type of inner join. In this join, a table is joined with itself. It uses an alias to refer to the table with different names in the same query.

What is Equi join and self join in SQL?

The Equi join in SQL is used to join two tables using an equal(=) sign as the comparator returning data that is equal in both tables. A self-join is a type of inner join in which a table is joined with itself.

How can we use self join?

To use self join, the table must have a primary key attribute and a different column with values that can be matched up with the values in the primary key. This way you can join a table with itself.

What is self join in DBMS with example?

A self-join is a type of inner join that joins a table with itself. E.g., a table has two columns with one as the primary key and two entries with different primary keys but the same values for the other attribute. A self-join joins these two entries with the condition of the entries with the same values of the other attribute.

Conclusion

In this article, we have discussed the concept of self join in SQL. We have also discussed some examples based on this concept. You can check out our other blogs to enhance your knowledge:

• Joins
   
• Equi and Non-Equi Join in SQL
   
• TCL Commands in SQL
   
• Difference between Inner Join and Outer Join
   
• LIMIT in SQL
   

We hope this blog helped you to understand the self join in SQL. You can refer to our guided paths on the Coding Ninjas Studio platform. You can check our course to learn more about DSA, DBMS, Competitive Programming, Python, Java, JavaScript, etc.