Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
join
self

What is a self join in SQL?

Educative Answers Team

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.

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.city

RELATED TAGS

sql
join
self
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring