SQL join a table to itself

Last update on January 10 2025 07:34:19 (UTC/GMT +8 hours)

Join a table to itself

A SELF JOIN is another type of join in SQL which is used to join a table to itself, especially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

In this join, the participating table appears twice after the FROM clause and is followed by aliases for the tables that qualify column names in the join condition

In this join, those rows are returned from the table which are satisfying the conditions.

Some important questions regarding SQL joining a table to itself

What is a self-join in SQL?

A self-join is a type of join operation where a table is joined with itself. This allows comparing rows within the same table based on specific criteria.

When would we use a SQL self-join?

Self-joins are commonly used when we need to compare rows within the same table, such as in hierarchical data structures like organizational charts or threaded discussions.

How do we differentiate between the two instances of the same table in a SQL self-join?

Table aliases are used to differentiate between the two instances of the same table in a self-join. Each instance is assigned a unique alias, which is used to reference columns from that instance throughout the query.

What are some common scenarios where SQL self-joins are useful?

Self-joins are useful in scenarios such as:

Finding employees who report to the same manager.

Identifying adjacent records in a time-series dataset.

Analyzing relationships between forum posts or comments in threaded discussions.

What are the join conditions typically used in a SQL self-join?

Join conditions in a self-join typically involve comparing values in columns within the same table. Common join conditions include comparing primary and foreign key relationships or comparing related columns that establish hierarchical relationships.

How do we avoid infinite loops in a SQL self-join?

To avoid infinite loops, ensure that the join conditions are properly defined and limit the scope of the join appropriately. It's important to have well-defined join conditions that establish a clear relationship between rows in the table.

Can we perform different types of joins in a SQL self-join?

Yes, we can perform different types of joins in a self-join, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN. The choice of join type depends on the specific requirements of your query and the desired result set.

How can we optimize performance when using a SQL self-join?

Optimizing performance in a self-join involves ensuring that our tables are properly indexed on the columns used for joining, which helps improve query execution time. Additionally, limiting the number of rows involved in the join and optimizing the query execution plan can further enhance performance.

Example:

Sample table: company

+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

To get 'company_name' and 'company_city' from 'company' table which is entitled as alias 'a' and 'company_name' form 'company' table which is entitled as alias 'b' after an SELF JOINING with a table itself, the following SQL statement can be used:

SQL Code:


-- Selecting specific columns: 'company_name' from table 'a' and 'b', and 'company_city' from table 'a'
SELECT a.company_name, b.company_name, a.company_city
-- Specifying the tables 'a' and 'b' in the FROM clause using a cross join (Cartesian product)
FROM company a, company b
-- Filtering the result to include only rows where the 'company_city' values are equal for both 'a' and 'b'
WHERE a.company_city = b.company_city;

Explanation:

This SQL query retrieves data from the 'company' table twice, aliased as 'a' and 'b'.

It selects specific columns: 'company_name' from table 'a' (aliased as 'a.company_name'), 'company_name' from table 'b' (aliased as 'b.company_name'), and 'company_city' from table 'a' (aliased as 'a.company_city').

The tables 'a' and 'b' are specified in the FROM clause using a cross join (Cartesian product), meaning every row from 'a' is combined with every row from 'b', resulting in all possible combinations of rows.

The WHERE clause filters the result to include only rows where the 'company_city' values are equal for both 'a' and 'b', effectively finding pairs of companies located in the same city.

This query is useful for identifying companies that are located in the same city, by comparing each company with every other company in the table. However, it may produce a large result set if there are many rows in the 'company' table.

Output:

COMPANY_NAME              COMPANY_NAME              COMPANY_CITY
------------------------- ------------------------- -------------
Order All                 Order All                 Boston
Foodies.                  Jack Hill Ltd             London
Jack Hill Ltd             Jack Hill Ltd             London
Akas Foods                Akas Foods                Delhi
Foodies.                  Foodies.                  London
Jack Hill Ltd             Foodies.                  London
sip-n-Bite.               sip-n-Bite.               New York

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL FULL OUTER JOIN
Next: SQL SELF JOIN