SQL - 自連接：初学者的友好指南

您好，有抱負的SQL愛好者！我很興奮能成為您進入SQL自連接世界的導遊。作為一個教了年輛計算機科學的老師，我見過無數學生在掌握這個概念時的「頓悟」瞬間。那麼，讓我們一起潛入水中，創造一點SQL魔法吧！

自連接是什麼？

在我們深入細節之前，讓我們從一個簡單的比喻開始。想像你在一個家族聚會上，你想創建一個所有父母-子女關係的列表。你有一個家族成員的大表，但你需要將表中的人與他們的父母連接起來。這就是SQL中的自連接所做的事情！

自連接是當一個表與自己連接時發生的。就像表在照鏡子並與自己的倒影連接。這聽起來有點令人困惑，對吧？別擔心，這一切很快都會變得清晰！

為什麼使用自連接？

當你在單個表中具有層次結構或遞歸數據時，自連接非常有用。想想以下情況：

1. 員工-經理關係
2. 家族樹
3. 制造業中的部件和子部件
4. 論壇中的帖子回覆

SQL自連接的實際應用

讓我們創造一個簡單的例子來說明自連接是如何工作的。我們將使用一個員工表來進行演示。

首先，讓我們創建我們的表：

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);

INSERT INTO employees (employee_id, name, manager_id)
VALUES
(1, 'John Doe', NULL),
(2, 'Jane Smith', 1),
(3, 'Bob Johnson', 1),
(4, 'Alice Brown', 2),
(5, 'Charlie Davis', 2);

現在，讓我們假設我們想列出每個員工以及他們經理的名字。這就是自連接派上用場的地方：

SELECT
e1.name AS employee,
e2.name AS manager
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.employee_id;

讓我們分解這個查詢：

1. 我們從employees表中選擇兩次，給它別名e1和e2。
2. e1代表員工，e2代表潛在的經理。
3. 我們在條件e1的manager_id與e2的employee_id匹配時將這些「兩個」表連接。
4. 我們使用LEFT JOIN來確保我們得到所有員工，即使那些沒有經理的員工。

結果可能會像這樣：

員工	經理
John Doe	NULL
Jane Smith	John Doe
Bob Johnson	John Doe
Alice Brown	Jane Smith
Charlie Davis	Jane Smith

這不是很棒嗎？僅僅使用一個查詢，我們就繪出了我們小公司的整個管理結構！

自連接與ORDER BY子句

現在，讓我們通過對結果進行排序來為我們的查詢增加一點趣味。我們可能想要按字母順序列出員工：

SELECT
e1.name AS employee,
e2.name AS manager
FROM
employees e1
LEFT JOIN
employees e2 ON e1.manager_id = e2.employee_id
ORDER BY
e1.name ASC;

這個查詢與我們之前的查詢完全相同，只是在最後添加了ORDER BY子句。

結果現在會像這樣：

員工	經理
Alice Brown	Jane Smith
Bob Johnson	John Doe
Charlie Davis	Jane Smith
Jane Smith	John Doe
John Doe	NULL

好多了！現在我们可以輕鬆地找到任何員工及其經理。

高級自連接技巧

準備好升級了嗎？讓我們嘗試一點更複雜的東西。如果我們想要找到有相同經理的員工會怎麼樣？

SELECT
e1.name AS employee1,
e2.name AS employee2,
m.name AS shared_manager
FROM
employees e1
JOIN
employees e2 ON e1.manager_id = e2.manager_id AND e1.employee_id < e2.employee_id
JOIN
employees m ON e1.manager_id = m.employee_id;

這個查詢可能看起來令人生畏，但讓我們分解它：

1. 我們將employees表與自己連接兩次（e1和e2）來比較員工。
2. 條件e1.employee_id < e2.employee_id確保我們不會得到重複的對（如「Alice和Bob」和「Bob和Alice」）。
3. 我們第三次連接（m）以獲得經理的名字。

結果可能會像這樣：

employee1	employee2	shared_manager
Jane Smith	Bob Johnson	John Doe
Alice Brown	Charlie Davis	Jane Smith

就是这样！我們找到了有相同經理的員工。

結論

自連接可能一開始看起來有點棘手，但它們是您SQL工具包中非常強大的工具。它們讓您可以有效地查詢層次數據，並在單個表中發現關係。

記住，熟能生巧！嘗試創建自己的表並試著使用不同的自連接查詢。在您知道之前，您將會像專家一樣進行自連接！

快樂查詢，未來的SQL大師！並且記住，在數據庫的世界裡，與自己對話——我是說，與自己自連接——是完全沒問題的！

Credits: Image by storyset