SQL > SQL JOIN > Self Join

Earlier we have examined the different types of joins in SQL such as inner join, outer join, left outer join, and cross join. In all the examples, we joined on two different tables. Can we write a SQL query that joins a table to itself? The answer is a definitive YES. When we join a table to itself, it's called a self join.

A SQL self join joins a table to itself using two different aliases, allowing you to compare rows within the same table — commonly used for calculating rankings, finding hierarchies, or detecting related records.

We join a table to itself when we want to find information that is contained in the table, but cannot be easily retrieved via a simple SELECT statement. For example, to find the rank of a row of data in a table, we used self join to find the rank of each row in a table. In that particular example, we have a table, Total_Sales, which has two columns: Name and Sales. To find the rank of each person based on the amount of sales, we use the following SQL: 

SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Please notice that in this case, we joined the table to itself. We are really treating this as though there are two different tables, even though these two tables have the same structure, same data, and same name.

Frequently Asked Questions

What is a self join in SQL?
A self join is a SQL join where a table is joined to itself. Table aliases are used to distinguish the two instances of the same table, allowing you to compare rows within the same table.
When would you use a self join?
A self join is useful when you want to find data that relates to other rows in the same table, such as calculating rankings, finding employees who report to the same manager, or identifying duplicate entries.
How do you write a self join in SQL?
Give the same table two different aliases in the FROM clause, then use the aliases to reference rows from each 'copy' of the table. For example: SELECT a1.Name, COUNT(a2.Sales) FROM Total_Sales a1, Total_Sales a2 WHERE a1.Sales <= a2.Sales GROUP BY a1.Name.
What is the difference between a self join and an inner join?
An inner join combines rows from two different tables. A self join joins a table to itself using aliases, effectively treating one table as two separate tables.

Related Topics

Next: SQL UNION ALL

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact