Self Join in SQL | Best Explanation with Examples

Apakah itu Self-Join dalam SQL?

Sambungan sendiri dalam SQL ialah jenis sambung yang mana jadual dicantumkan dengan dirinya sendiri. Ia berguna apabila anda ingin membandingkan baris dalam jadual yang sama atau mendapatkan semula data berkaitan daripada set data yang sama. Penyertaan diri sering digunakan untuk memodelkan perhubungan hierarki (seperti struktur pekerja-pengurus) atau untuk mencari gabungan dalam satu set (seperti kemungkinan perlawanan antara pasukan).

---

Takrif:

Sambungan sendiri ialah sambung biasa di mana jadual dicantumkan dengan dirinya sendiri menggunakan alias yang berbeza. Ia pada asasnya digunakan untuk membandingkan baris jadual dengan baris lain dalam jadual yang sama.

Sintaks:

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.common_column = b.common_column;

Penjelasan:

• nama_jadual a: Mencipta alias (a) untuk jadual.
• nama_jadual b: Mencipta alias lain (b) untuk jadual yang sama.
• PADA a.common_column = b.common_column: Syarat untuk menyertai dua alias berdasarkan lajur biasa.

---

1. Sertai Sendiri Contoh: Senario Pekerja dan Pengurus

Senario:

Anda mempunyai jadual Pekerja dan anda perlu mengetahui pekerja mana yang melaporkan kepada pengurus mana. Setiap baris dalam jadual mengandungi butiran pekerja dan lajur ManagerID memegang ID Pekerja pengurus.

Contoh Penciptaan Jadual dan Sisipan Data:

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    EmployeeName VARCHAR2(50),
    ManagerID NUMBER
);

-- Insert sample data
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) 
VALUES (1, 'John', NULL);
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) 
VALUES (2, 'Mike', 1);
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) 
VALUES (3, 'Sarah', 1);
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) 
VALUES (4, 'Kate', 2);
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) 
VALUES (5, 'Tom', 2);


-- Commit the changes
COMMIT;

---

Pertanyaan Sertai Sendiri dalam Oracle:

SELECT e1.EmployeeName AS Employee, 
       e2.EmployeeName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

Penjelasan:

• e1 ialah alias mewakili pekerja.
• e2 ialah alias lain yang mewakili pengurus.

LEFT JOIN membantu merangkumi semua pekerja, malah mereka yang tidak mempunyai pengurus (ManagerID is NULL).

Output:

Employee	Manager
John	NULL
Mike	John
Sarah	John
Kate	Mike
Tom	Mike

---

2. Contoh Sertai Sendiri: Perlawanan IPL (Setiap Pasukan Bertanding Menentang Setiap Pasukan Lain Sekali)

Senario:

Anda mempunyai senarai pasukan IPL dan anda ingin menjana senarai perlawanan di mana setiap pasukan bermain menentang setiap pasukan lain sekali.

Contoh Penciptaan Jadual dan Sisipan Data:

-- Create the Teams table
CREATE TABLE Teams (
    TeamID NUMBER PRIMARY KEY,
    TeamName VARCHAR2(100)
);

-- Insert sample data
INSERT INTO Teams (TeamID, TeamName) 
VALUES (1, 'Mumbai Indians');
INSERT INTO Teams (TeamID, TeamName) 
VALUES (2, 'Chennai Super Kings');
INSERT INTO Teams (TeamID, TeamName) 
VALUES (3, 'Royal Challengers Bangalore');
INSERT INTO Teams (TeamID, TeamName) 
VALUES (4, 'Kolkata Knight Riders');

-- Commit the changes
COMMIT;

Pertanyaan Sertai Sendiri dalam Oracle:

SELECT t1.TeamName AS Team1, 
       t2.TeamName AS Team2
FROM Teams t1
JOIN Teams t2 ON t1.TeamID < t2.TeamID;

Penjelasan:

• t1 dan t244 ialah alias untuk jadual Pasukan.

Syarat t1.TeamID < t2.TeamID memastikan setiap gandingan perlawanan disenaraikan sekali sahaja (mengelakkan pendua seperti Pasukan A lwn. Pasukan B dan Pasukan B lwn. Pasukan A).

Output:

Team1	Team2
Mumbai Indians	Chennai Super Kings
Mumbai Indians	Royal Challengers Bangalore
Mumbai Indians	Kolkata Knight Riders
Chennai Super Kings	Royal Challengers Bangalore
Chennai Super Kings	Kolkata Knight Riders
Royal Challengers Bangalore	Kolkata Knight Riders

---

3. Self-Join Example: IPL Matches (Every Team Plays Against Every Other Team Twice)

Scenario:

You want to generate a list where each IPL team plays against every other team twice (once as the home team, and once as the away team).

Self-Join Query in Oracle:

SELECT t1.TeamName AS Team1, 
       t2.TeamName AS Team2
FROM Teams t1
JOIN Teams t2 ON t1.TeamID != t2.TeamID;

Explanation:

• t1 and t2 are aliases for the Teams table.

The condition t1.TeamID != t2.TeamID ensures that all possible match-ups are listed, including both Team A vs. Team B and Team B vs. Team A.

Output:

Team1	Team2
Mumbai Indians	Chennai Super Kings
Mumbai Indians	Royal Challengers Bangalore
Mumbai Indians	Kolkata Knight Riders
Chennai Super Kings	Mumbai Indians
Chennai Super Kings	Royal Challengers Bangalore
Chennai Super Kings	Kolkata Knight Riders
Royal Challengers Bangalore	Mumbai Indians
Royal Challengers Bangalore	Chennai Super Kings
Royal Challengers Bangalore	Kolkata Knight Riders
Kolkata Knight Riders	Mumbai Indians
Kolkata Knight Riders	Chennai Super Kings
Kolkata Knight Riders	Royal Challengers Bangalore

---

Finding Duplicate Customer Records - Additional Example

Scenario:
You have a Customers table where each customer should have a unique combination of FirstName, LastName, and DateOfBirth. However, there may be accidental duplicates, and you want to identify them using a self-join.

Sample Table Creation and Data Insertion:

-- Create the Customers table
CREATE TABLE Customers (
    CustomerID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    DateOfBirth DATE
);

-- Insert sample data (including duplicates)
INSERT INTO Customers (CustomerID, FirstName, LastName, DateOfBirth) VALUES (1, 'John', 'Doe', TO_DATE('1990-01-01', 'YYYY-MM-DD'));
INSERT INTO Customers (CustomerID, FirstName, LastName, DateOfBirth) VALUES (2, 'Jane', 'Smith', TO_DATE('1992-02-02', 'YYYY-MM-DD'));
INSERT INTO Customers (CustomerID, FirstName, LastName, DateOfBirth) VALUES (3, 'John', 'Doe', TO_DATE('1990-01-01', 'YYYY-MM-DD'));
INSERT INTO Customers (CustomerID, FirstName, LastName, DateOfBirth) VALUES (4, 'Alice', 'Johnson', TO_DATE('1995-03-03', 'YYYY-MM-DD'));
INSERT INTO Customers (CustomerID, FirstName, LastName, DateOfBirth) VALUES (5, 'John', 'Doe', TO_DATE('1990-01-01', 'YYYY-MM-DD'));

-- Commit the changes
COMMIT;

Self-Join Query to Find Duplicates:

SELECT c1.CustomerID AS DuplicateRecordID1, 
       c2.CustomerID AS DuplicateRecordID2, 
       c1.FirstName, 
       c1.LastName, 
       c1.DateOfBirth
FROM Customers c1
JOIN Customers c2 ON c1.FirstName = c2.FirstName
                 AND c1.LastName = c2.LastName
                 AND c1.DateOfBirth = c2.DateOfBirth
                 AND c1.CustomerID < c2.CustomerID;

Explanation:

• c1 and c2 are aliases for the same Customers table.
• The condition c1.FirstName = c2.FirstName AND c1.LastName = c2.LastName AND c1.DateOfBirth = c2.DateOfBirth checks for matching values across multiple columns, indicating a duplicate.
• c1.CustomerID < c2.CustomerID ensures that each duplicate pair is shown only once, avoiding repetition like Customer A vs. Customer B and Customer B vs. Customer A.

Output:

RecordID1	RecordID2	FirstName	LastName	DateOfBirth
1	3	John	Doe	1990-01-01
1	5	John	Doe	1990-01-01
3	5	John	Doe	1990-01-01

---

Conclusion:

• A self-join allows you to connect rows from the same table by creating multiple aliases. It is useful in scenarios where data needs to be compared within the same dataset. In the above examples:
• The employee-manager example shows how to use self-joins for hierarchical data.
• The IPL match-ups illustrate how to generate combinations within a single dataset, whether for a single match per pair or double matches (home and away games).
• These scenarios demonstrate the flexibility and power of self-joins in SQL.

The above is the detailed content of Self Join in SQL | Best Explanation with Examples. For more information, please follow other related articles on the PHP Chinese website!