SQL join a table to itself
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?
When would we use a SQL self-join?
How do we differentiate between the two instances of the same table in a SQL self-join?
What are some common scenarios where SQL self-joins are useful?
- 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?
How do we avoid infinite loops in a SQL self-join?
Can we perform different types of joins in a SQL self-join?
How can we optimize performance when using a SQL self-join?
Example:
Sample table: company
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
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
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql/joins/join-a-table-to-itself.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics