SELF JOIN
Sql tutorial · PySpark.in
Self Join
A SELF JOIN is a join in which a table is joined with itself. This is useful when you want to compare rows within the same table.
Since the same table is used twice, we give it different aliases so SQL can treat them as two separate copies.
In simple words:
SELF JOIN compares rows from the same table.
Syntax:
```
hide
sql
SELECT t1.c1, t2.c2
FROM table1 AS t1
JOIN table1 AS t2
ON t1.c1 = t2.cn;
```
Note: We can use any JOIN clause in self-join.
Example: Get pairs of students who registered for the same course.
Suppose the student_course table contain
```
sql
DROP TABLE IF EXISTS student_course;
-- Create table
CREATE TABLE student_course (
enrollment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER
);
-- Insert data
INSERT INTO student_course (enrollment_id, student_id, course_id) VALUES
(101, 1, 15),
(102, 2, 15),
(103, 3, 12),
(104, 4, 15),
(105, 5, 12);
-- Get student pairs who registered for the same course
SELECT sc1.student_id AS student_id1,
sc2.student_id AS student_id2,
sc1.course_id
FROM student_course AS sc1
INNER JOIN student_course AS sc2
ON sc1.course_id = sc2.course_id
WHERE sc1.student_id < sc2.student_id;
```
How It Works
Step 1: Same table used twice
```
hide
student_course AS sc1
student_course AS sc2
```
SQL treats sc1 and sc2 as two separate copies of the same table.
Step 2: Match rows with the same course
```
hide
ON sc1.course_id = sc2.course_id
```
This matches students who registered for the same course.
Step 3: Avoid duplicate pairs
```
hide
WHERE sc1.student_id < sc2.student_id
```
This prevents duplicate combinations such as:
- (1, 2) and (2, 1)
It also avoids matching a student with themselves.
Important Point
A SELF JOIN is commonly used when:
- comparing rows within the same table
- finding relationships between records in one table
- working with hierarchical data such as employees and managers
Joins Summary
Join type | Use case |
|---|---|
Natural Join | Joins based on common columns |
Inner Join | Joins based on a given condition |
Left Join | All rows from left table and matched rows from right table |
Right Join | All rows from right table and matched rows from left table |
Full Join | All rows from both the tables |
Cross Join | All possible combinations |
More Sql tutorials
All tutorials · Try the free PySpark compiler · Practice challenges