Trusted answers to developer questions
What is a self join in SQL?
Educative Answers TeamA JOIN clause is used to combine rows from two or more tables based on a related column between them.
A self JOIN is a regular join, but the table is joined with itself – this is extremely useful for comparisons within a table.
Joining a table with itself means that each row of the table is combined with itself and with every other row of the table.
Syntax
SELECT column_names
FROM table1 T1, table1 T2
WHERE condition;
Aliases for the actual table names are used to distinguish column names from one another since both of the tables have the same name. T1 and T2 are aliases for the same table.
Example
The following table has been created:
1. User Info
| First Name | Last Name | City |
|---|---|---|
| John | Doe | Lahore |
| Sam | Smith | Karachi |
| Shawn | Magen | Lahore |
| Homer | Simpson | Lahore |
| Bart | Green | Karachi |
To match customers that are from the same city, we have used the following SQL query that self joins the table:
SELECT A.City,
B.name AS FirstName1,
A.name AS FirstName2
FROM user_info A, user_info B
WHERE A.name <> B.name
AND A.city = B.city
ORDER BY A.cityRELATED TAGS
sql
join
self
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES
View all Courses
Keep Exploring
Related Courses
