關於 SQL SELF JOIN

說到SQL的Join, 大部分都會提到inner join, left join, right join, cross join，但實務上也經常使用self join自連結進行資料分析。其實self join就是用同一張表進行自我JOIN，是比較特別的JOIN方式，而且可以找出許多有用的資訊。

使用情境：

一、分析有階層性質的資料(hierarchical data)

找出薪水比主管多的職員

其實，真實世界不太可能分析到同事薪資狀況，不過解說self join時，此範例經常出現。畢竟公司都有薪資保密原則，分析同事薪資的機會，應該難能可貴；但如果將薪水欄位改成績效，那麼實務上發生的機率就會比較高。

employee table layout

[embed]employee sample data

寫法1：利用inner join達成self join

select e.employee_id
      ,e.name as employee_name
      ,e.salary as employee_salary
      ,e.supervisor_id
      ,s.name as supervisor_name
      ,s.salary as supervisor_salary
 from employee e
inner join employee s on e.supervisor_id = s.employee_id
  and e.salary > s.salary

寫法2：利用where條件達成self join

select e.supervisor_id
      ,s.name as supervisor_name
      ,s.salary as supervisor_salary
      ,e.employee_id
      ,e.name as employee_name
      ,e.salary as employee_salary
 from employee e, employee s
where e.supervisor_id = s.employee_id
  and e.salary > s.salary

上述無論何種方式，都是先列出該主管所管理的成員名單，基礎比較條件為e.supervisor_id(employee的主管id) = s.employee_id (主管的員工id)，結果呈現如下：

e.supervisor_id = s.employee_id query result

最後再加上主管薪水與職員薪水的比較條件，列出薪水比主管高的職員名單。查出的結果：Emily是目前薪水比主管多的職員。

Emily’s salary is higher than her supervisor

二、分析同一表中的資料(compare rows within a table)

找出同城市的員工名單

利用同一張table，比較同一row的資料內容，看是否相同並且是否符合比較條件，籍此羅列出居住在同一個地區的員工名單。

select 
      A.name as employee1
    , B.name as employee2
    , A.city
from  employee A, employee B
where A.city = B.city
  and A.name < B.name --A.name > B.name is also OK
order by A.city

上述的語法，帶入居住城市條件相同之外，還需要加列名字的先後比較條件A.name < B.name 或 A.name > B.name 皆可，主要目的在於排除因欄位A、B位置不同但實為重複的名單。

query result

最後的查詢結果，直接列出居住在同一個城市的所屬名單，最後可以得知Amber 及Jack住在Kaohsiung；Emily及Mary住在Taipei。

若不需要印出名單，只需要統計同一個城市的居住人數，則單用employee表進行group by，即可達到效果。但若需要印出詳細名單，採用self join則是一個簡便又快速的方式。

除了上述的用法外，其實自連結在實務上仍有許多其他有趣的應用，例如：客戶的地緣關係分析、客戶間的家戶或親子關係、業務與客戶之間的資料是否重疊分析…等。我自己本身就接觸過，利用SELF JOIN，找出業務與客戶的通訊資訊或行動裝置資料相同的名單分析，以做為內控加強查核之用的參考名單。

參考資料：

1. https://www.sqlservertutorial.net/sql-server-basics/sql-server-self-join/