SQL 中的自连接 |最好的例子解释

Qu'est-ce qu'une auto-jointure en SQL ?

Une auto-jointure en SQL est un type de jointure dans lequel une table est jointe à elle-même. Ceci est utile lorsque vous souhaitez comparer des lignes dans la même table ou récupérer des données associées à partir du même ensemble de données. Les auto-jointures sont souvent utilisées pour modéliser des relations hiérarchiques (comme les structures employé-manager) ou pour trouver des combinaisons au sein d'un ensemble (comme des confrontations possibles entre équipes).

---

Définition :

Une auto-jointure est une jointure régulière où la table est jointe à elle-même en utilisant différents alias. Il est essentiellement utilisé pour comparer les lignes d'un tableau à d'autres lignes du même tableau.

Syntaxe :

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

Explication :

• table_name a : crée un alias (a) pour la table.
• table_name b : crée un autre alias (b) pour la même table.
• ON a.common_column = b.common_column : La condition pour joindre les deux alias en fonction des colonnes communes.

---

1. Exemple d'auto-adhésion : scénario d'employé et de gestionnaire

Scénario :

Vous disposez d'une table Employés et vous devez savoir quel employé relève de quel responsable. Chaque ligne du tableau contient les détails des employés et la colonne ManagerID contient l'EmployeeID du manager.

Création d'un exemple de tableau et insertion de données :

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

---

Requête d'auto-jointure dans Oracle :

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

Explication :

• e1 est un alias représentant les employés.
• e2 est un autre alias représentant les managers.

LEFT JOIN permet d'inclure tous les employés, même ceux qui n'ont pas de manager (ManagerID est NULL).

Sortie :

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

---

2. Exemple d'auto-adhésion : matchs IPL (chaque équipe joue une fois contre chaque autre équipe)

Scénario :

Vous avez une liste d'équipes IPL et vous souhaitez générer une liste de matchs où chaque équipe joue une fois contre toutes les autres équipes.

Création d'un exemple de tableau et insertion de données :

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

Requête d'auto-jointure dans Oracle :

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

Explication :

• t1 et t244 sont des alias pour la table Teams.

La condition t1.TeamID < t2.TeamID garantit que chaque paire de matchs n'est répertoriée qu'une seule fois (en évitant les doublons comme l'équipe A contre l'équipe B et l'équipe B contre l'équipe A).

Sortie :

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中文网其他相关文章！