Self Join in SQL

A self join in SQL helps us in joining data given in the same table. In simpler words, a self join would join a table with itself. Thus, the records taken from the given table are matched and compared to other records from the very same table in the case of self join in SQL.

In this article, we will take a look at the self join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is Self Join in SQL?
- Syntax
- Example of Self Join in SQL
Uses of Self Join in SQL
FAQs

What is Self Join in SQL?

We use the self join in SQL to join any table to itself in a way that we assume that the table is two tables. This way, we temporarily rename at least one of the tables in an SQL statement.

Syntax

Here is the basic syntax of a typical SELF JOIN:

SELECT x.name_of_column, y.name_of_column…

FROM table_1 x, table_1 y

WHERE x.common_field = y.common_field;

In this case, the WHERE clause can be any of the given expressions on the basis of your requirement.

Example of Self Join in SQL

Let us consider the following table to understand how self join works in SQL.

CLIENTS Table is given below:

ID	NAME	LOCATION	AGE	SALARY
C1	Rimona	Indore	24	2000.00
C2	Kate	MP	22	1500.00
C3	Wendy	Bhopal	25	2000.00
C4	Christina	Kota	27	6500.00
C5	Hinata	Mumbai	23	8500.00
C6	Kendall	Delhi	25	4500.00
C7	Miranda	Ahmedabad	32	10000.00

Now, in case we join this above-given table using self join, then it will be as follows:

SQL> SELECT x.ID, y.NAME, a.SALARY

FROM CUSTOMERS x, CUSTOMERS y

WHERE x.SALARY < y.SALARY;

Then this would produce the result as follows:

ID	NAME	SALARY
C2	Rimona	1500.00
C2	Wendy	1500.00
C1	Christina	2000.00
C2	Christina	1500.00
C3	Christina	2000.00
C6	Christina	4500.00
C1	Hinata	2000.00
C2	Hinata	1500.00
C3	Hinata	2000.00
C4	Hinata	6500.00
C6	Hinata	4500.00
C1	Kendall	2000.00
C2	Kendall	1500.00
C3	Kendall	2000.00
C1	Miranda	2000.00
C2	Miranda	1500.00
C3	Miranda	2000.00
C4	Miranda	6500.00
C5	Miranda	8500.00
C6	Miranda	4500.00

Uses of Self Join in SQL

We use joins in SQL to stitch the available database back together. This way, it becomes easy to read the data and then use it properly. Self join is very important in SQL in case we want to query ordered data when we compare the available rows in the very same table.

Thus, the SQL query for self join would be applied in any table to that table itself as if there are two separate tables with temporary names as an alias for the available table in the SQL statement.

FAQs

What is self join in SQL?

Why do we need joins in SQL?

We use joins in SQL to stitch the available database back together. This way, it becomes easy to read the data and then use it properly.

Why use self join in SQL?

Self join is very important in SQL in case we want to query ordered data when we compare the available rows in the very same table. Thus, the SQL query for self join would be applied in any table to that table itself as if there are two separate tables with temporary names as an alias for the available table in the SQL statement.

What is the difference between union and join?

Difference between JOIN and UNION in SQL:
JOIN – JOIN helps in the combination of data from multiple tables on the basis of a matched condition present between them. It would combine the available data into new columns.
UNION – SQL helps in the combination of the result-set of multiple SELECT statements. It would combine the available data into new rows.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,