SQL Self Join: Everything You Need to Know

By /
Add A Heading

SQL (Structured Query Language) is a powerful tool used for creating, manipulating and retrieving data from databases. While working with these databases, there are many instances where we have to combine data from one or more tables. This can be easily done using the JOIN operation. The SELF JOIN is a type of JOIN that lets us compare data within a single table. This can be useful while working with hierarchical or relational data within a table.

In this article, we’ll understand how to use SELF JOIN in detail with some supporting examples.

Understanding SELF JOIN in SQL

SELF JOIN is a type of JOIN operation involving only a single table. In this operation, the table is joined with itself, which in turn helps us compare multiple rows within the table. We use different aliases to distinguish between the various instances of the same table while performing SELF JOIN. The syntax for SELF JOIN is given below.

Syntax:

SELECT column_name1, column_name2....
FROM table AS t1
JOIN table AS t2 ON t1.column = t2.column;
Code language: SQL (Structured Query Language) (sql)

Here t1 and t2 are aliases for the same table.

The SELF JOIN operation can be diagrammatically represented as follows:

SELF JOIN
SELF JOIN

SQL SELF JOIN Examples

Let’s now understand how to use SELF JOIN with some examples.

Example 1:

Consider a table Family which contains details about family members and whether they’re related to each other as parent or child.

Family Table
Family Table

We will use the SELF JOIN operation on the Family table to retrieve the names of the child and their parent. The query to do this is given below.

Query:

SELECT f1.member_name AS child, f2.member_name AS parent 
FROM Family f1 
JOIN Family f2 ON f1.parent_id = f2.member_id;Code language: SQL (Structured Query Language) (sql)

Here, Family is given two aliases f1 and f2, where f1 represents the children and f2 represents the parents of the children. The parent is linked to the child using parent_id. The output contains two columns, child and parent, which display the names of the child and their respective parent.

Output:

SELF JOIN Output
Performing SELF JOIN On Family Table

From this table we can infer the following:

  • Bob is a parent of Bella.
  • Bella is the parent of Mira and Robby.
  • Bob is the grandparent of Mira and Robby.

Example 2:

Consider a table Products containing a list of product ids, product names and the id of products related to each product on the basis of similarity.

Products Table
Products Table

Here products related to each other are indicated using related_product_id. Products without any related products are given a value of NULL. We need to display the product names along with their corresponding related product names. This can be done by performing a SELF JOIN on Products. The query for this is given below.

Query:

SELECT p1.product_name AS Product, p2.product_name AS Related 
FROM Products p1 
JOIN Products p2 ON p1.related_product_id = p2.product_id;Code language: SQL (Structured Query Language) (sql)

Here, the Products table is given two aliases p1 and p2 to represent its two instances, where p1 represents Products and p2 represents Related products. Products with no related products are not displayed after SELF JOIN.

Output:

Performing SELF JOIN On Products Table
Performing SELF JOIN On Products Table

We can make the following inferences from the above table:

  • iPhone 12 and iPhone 13 are products related to each other.
  • Samsung Galaxy S21 and Samsung note 20 are related to each other.
  • Beats Earphones have no products related to them.

Conclusion

Using SELF JOIN to retrieve data from the same table can open up various possibilities of comparing data within a table which otherwise cannot be done without it. In this article, we have explored the SELF JOIN operation in depth by understanding its syntax and its implementation using suitable examples. This can be useful to establish a hierarchy or relationships between data in a single table.

Reference