A 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.
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.
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 FirstName2FROM user_info A, user_info BWHERE A.name <> B.nameAND A.city = B.cityORDER BY A.city
Unlock your potential: SQL joins series, all in one place!
To continue your exploration of SQL joins, check out our series of Answers below:
Different types of SQL joins
Understand the different types of SQL joins used for combining data from multiple tables.
What is inner join in SQL?
Learn how inner joins return matched rows from both tables in SQL.
What is outer join in SQL?
Explore outer joins and how they return unmatched rows as well.
What is a self join in SQL?
Discover how self joins allow a table to join with itself.
What is a natural join in SQL?
Learn how natural joins match columns with the same name in SQL tables.
What is a hash join in SQL?
Understand hash joins, which are used for large-scale data matching and combining.
What is the SQL CROSS JOIN?
Explore the SQL CROSS JOIN to combine every row from one table with every row from another.
What is an equi join in SQL?
Learn how equi joins match rows based on the equality of values between tables.
How to join 3 or more tables in SQL
Master the technique of joining multiple tables in a SQL query for complex data retrieval.
Free Resources