SQL SELF JOIN
Posted on
< Previous Next >
A SELFÂ Join is a type of SQL join that is used to join a table with itself. This is done by creating two or more copies of the same table within a query and then joining them together using a join condition.
SELF JOIN Syntax
SELECT t1.column_name, t2.column_name
FROM table_name t1
INNER JOIN table_name t2
ON t1.join_column = t2.join_column;
For example,
SELECT s1.name, s2.name, s1.address
FROM student s1
INNER JOIN student s2 ON s1.address = s2.address AND s1.id <> s2.id;
OUTPUT
In this example,
Let's say we want to retrieve a list of all students who share the same address. We can use a self join to achieve this by joining the student table with itself on the address column.
We create two copies of the student table by aliasing them as s1 and s2. We then join these tables together on the address column, and add a condition to exclude any rows where the student IDs are the same (i.e. we don't want to include a student as sharing the same address with themselves).
The result of this query will be a table that contains the names of all students who share the same address, along with their common address. Each row in the result set represents a pair of students who share the same address.
Self joins can be useful when working with hierarchical or recursive data, such as an organization chart where each employee has a manager who is also an employee. By joining a table with itself using a foreign key/primary key relationship, you can retrieve information about the parent-child relationships between the data.
< Previous Next >