SQL 中的自連線 |最好的例子解釋

什麼是 SQL 中的自連線？

SQL 中的自聯結是一種資料表與其自身聯結的聯結型別。當您想要比較同一表中的行或從同一資料集中檢索相關資料時，它非常有用。自連結通常用於建模層級關係（如員工-經理結構）或尋找集合內的組合（如團隊之間可能的匹配）。

---

定義：

自連接是一種常規連接，其中表使用不同的別名與其自身連接。它本質上用於將表的行與同一表中的其他行進行比較。

文法：

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

說明：

• table_name a：為表格建立別名 (a)。
• table_name b：為同一個表建立另一個別名 (b)。
• ON a.common_column = b.common_column：根據公共欄位連接兩個別名的條件。

---

1.自加入範例：員工與經理場景

場景：

您有一個員工表，您需要找出哪個員工向哪個經理報告。表中的每一行都包含員工的詳細信息，ManagerID 欄位保存經理的 EmployeeID。

範例表格建立與資料插入：

-- 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;

---

Oracle 中的自連線查詢：

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

說明：

• e1 是代表員工的別名。
• e2 是代表管理者的另一個別名。

LEFT JOIN 有助於包含所有員工，甚至包括那些沒有經理的員工（ManagerID 為 NULL）。

輸出：

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

---

2.自加入範例：IPL 比賽（每個團隊與其他團隊比賽一次）

場景：

您有一個 IPL 球隊列表，並且您想要產生一個比賽列表，其中每支球隊都與其他球隊交手一次。

範例表格建立與資料插入：

-- 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;

Oracle 中的自連線查詢：

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

說明：

• t1 和 t244 是 Teams 表的別名。

條件 t1.TeamID

輸出：

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.

以上是SQL 中的自連線 |最好的例子解釋的詳細內容。更多資訊請關注PHP中文網其他相關文章！