在本教學中，您將了解如何使用連線語句將表連線到表自身，即，在同一張表上自己連線自己。
在之前的教學中，已經學習了如何使用INNER JOIN，LEFT JOIN 或 CROSS JOIN子句將表連線到其他表。 但是，有一個特殊情況，需要將表自身連線，這被稱為自連線。
當您想將表中行與同一表中的其他行組合時，可以使用自連線。要執行自聯接操作必須使用表別名來幫助MySQL在單個查詢中區分左表與同一張表的右表。
我們來看看範例資料庫(yiibaidb)中的employees表，其表結構如下所示 -
要獲得整個組織結構，可以使用employeeNumber和reportsTo列將employees表連線自身。employees表有兩個角色：一個是經理，另一個是直接報告者(即，下屬員工)。
SELECT
CONCAT(m.lastname, ', ', m.firstname) AS 'Manager',
CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
employees e
INNER JOIN
employees m ON m.employeeNumber = e.reportsto
ORDER BY manager;
執行上面查詢，得到以下結果 -
+--------------------+--------------------+
| Manager | Direct report |
+--------------------+--------------------+
| Bondur, Gerard | Jones, Barry |
| Bondur, Gerard | Castillo, Pamela |
| Bondur, Gerard | Bondur, Loui |
| Bondur, Gerard | Bott, Larry |
| Bondur, Gerard | Gerard, Martin |
| Bondur, Gerard | Hernandez, Gerard |
| Bow, Anthony | Vanauf, George |
| Bow, Anthony | Patterson, Steve |
| Bow, Anthony | Thompson, Leslie |
| Bow, Anthony | Tseng, Foon Yue |
| Bow, Anthony | Firrelli, Julie |
| Bow, Anthony | Jennings, Leslie |
| Murphy, Diane | Firrelli, Jeff |
| Murphy, Diane | Patterson, Mary |
| Nishi, Mami | Kato, Yoshimi |
| Patterson, Mary | Bow, Anthony |
| Patterson, Mary | Patterson, William |
| Patterson, Mary | Bondur, Gerard |
| Patterson, Mary | Nishi, Mami |
| Patterson, William | Marsh, Peter |
| Patterson, William | King, Tom |
| Patterson, William | Fixter, Andy |
+--------------------+--------------------+
22 rows in set
在上述輸出中，只能看到有經理的員工。 但是，由於INNER JOIN子句，所以看不到總經理。總經理是沒有任何經理的員工，或者他的經理人是NULL。
我們將上述查詢中的INNER JOIN子句更改為LEFT JOIN子句，以包括總經理。 如果管理員名稱為NULL，則還需要使用IFNULL函式來顯示總經理。
SELECT
IFNULL(CONCAT(m.lastname, ', ', m.firstname),
'Top Manager') AS 'Manager',
CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROM
employees e
LEFT JOIN
employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC;
執行上面查詢，得到以下結果 -
+--------------------+--------------------+
| Manager | Direct report |
+--------------------+--------------------+
| Top Manager | Murphy, Diane |
| Patterson, William | Fixter, Andy |
| Patterson, William | Marsh, Peter |
| Patterson, William | King, Tom |
| Patterson, Mary | Bondur, Gerard |
| Patterson, Mary | Nishi, Mami |
| Patterson, Mary | Bow, Anthony |
| Patterson, Mary | Patterson, William |
| Nishi, Mami | Kato, Yoshimi |
| Murphy, Diane | Patterson, Mary |
| Murphy, Diane | Firrelli, Jeff |
| Bow, Anthony | Tseng, Foon Yue |
| Bow, Anthony | Firrelli, Julie |
| Bow, Anthony | Jennings, Leslie |
| Bow, Anthony | Vanauf, George |
| Bow, Anthony | Patterson, Steve |
| Bow, Anthony | Thompson, Leslie |
| Bondur, Gerard | Bott, Larry |
| Bondur, Gerard | Gerard, Martin |
| Bondur, Gerard | Hernandez, Gerard |
| Bondur, Gerard | Jones, Barry |
| Bondur, Gerard | Castillo, Pamela |
| Bondur, Gerard | Bondur, Loui |
+--------------------+--------------------+
23 rows in set
通過使用MySQL自連線，可以通過將customers表連線自身來顯示位於同一個城市的客戶列表。參考以下查詢語句 -
SELECT
c1.city, c1.customerName, c2.customerName
FROM
customers c1
INNER JOIN
customers c2 ON c1.city = c2.city
AND c1.customername > c2.customerName
ORDER BY c1.city;
執行上面查詢語句，得到以下結果 -
+---------------+------------------------------+--------------------------------+
| city | customerName | customerName |
+---------------+------------------------------+--------------------------------+
| Auckland | Kelly's Gift Shop | Down Under Souveniers, Inc |
| Auckland | Kelly's Gift Shop | GiftsForHim.com |
| Auckland | GiftsForHim.com | Down Under Souveniers, Inc |
| Boston | Gifts4AllAges.com | Diecast Collectables |
| Brickhaven | Collectables For Less Inc. | Auto-Moto Classics Inc. |
| Brickhaven | Online Mini Collectables | Auto-Moto Classics Inc. |
| Brickhaven | Online Mini Collectables | Collectables For Less Inc. |
| Cambridge | Martas Replicas Co. | Cambridge Collectables Co. |
| Frankfurt | Messner Shopping Network | Blauer See Auto, Co. |
| Glendale | Gift Ideas Corp. | Boards & Toys Co. |
| Lisboa | Porto Imports Co. | Lisboa Souveniers, Inc |
| London | Stylish Desk Decors, Co. | Double Decker Gift Stores, Ltd |
| Madrid | Corrida Auto Replicas, Ltd | Anton Designs, Ltd. |
| Madrid | Corrida Auto Replicas, Ltd | ANG Resellers |
| Madrid | CAF Imports | Anton Designs, Ltd. |
| Madrid | CAF Imports | ANG Resellers |
| Madrid | Euro+ Shopping Channel | Anton Designs, Ltd. |
| Madrid | Euro+ Shopping Channel | ANG Resellers |
| Madrid | Corrida Auto Replicas, Ltd | CAF Imports |
| Madrid | Euro+ Shopping Channel | CAF Imports |
| Madrid | Anton Designs, Ltd. | ANG Resellers |
| Madrid | Euro+ Shopping Channel | Corrida Auto Replicas, Ltd |
| Nantes | La Rochelle Gifts | Atelier graphique |
| New Bedford | Mini Creations Ltd. | FunGiftIdeas.com |
| New Haven | Super Scale Inc. | American Souvenirs Inc |
| NYC | Muscle Machine Inc | Classic Legends Inc. |
| NYC | Vitachrome Inc. | Land of Toys Inc. |
| NYC | Vitachrome Inc. | Microscale Inc. |
| NYC | Land of Toys Inc. | Classic Legends Inc. |
| NYC | Muscle Machine Inc | Land of Toys Inc. |
| NYC | Muscle Machine Inc | Microscale Inc. |
| NYC | Vitachrome Inc. | Muscle Machine Inc |
| NYC | Microscale Inc. | Classic Legends Inc. |
| NYC | Vitachrome Inc. | Classic Legends Inc. |
| NYC | Microscale Inc. | Land of Toys Inc. |
| Paris | Lyon Souveniers | La Corne D'abondance, Co. |
| Paris | Lyon Souveniers | Auto Canal+ Petit |
| Paris | La Corne D'abondance, Co. | Auto Canal+ Petit |
| Philadelphia | Motor Mint Distributors Inc. | Classic Gift Ideas, Inc |
| San Francisco | Mini Wheels Co. | Corporate Gift Ideas Co. |
| Singapore | Dragon Souveniers, Ltd. | Asian Shopping Network, Co |
| Singapore | Handji Gifts& Co | Dragon Souveniers, Ltd. |
| Singapore | Handji Gifts& Co | Asian Shopping Network, Co |
+---------------+------------------------------+--------------------------------+
43 rows in set
我們通過以下連線條件連線了customers表：
c1.city = c2.city 以確保兩個表的客戶都是來自相同的城市。c.customerName> c2.customerName以確保不要得到相同的客戶。在本教學中，我們向您介紹了MySQL自連線，可以通過使用INNER JOIN或LEFT JOIN子句將一個表連線到自身。