別名（Aliasing）

改進 SQL Server 中的查詢效能

Dean Smith

Founder, Atamai Analytics

什麼是別名？

在查詢中用來標識：
- 資料表
- 欄位
- 子查詢
臨時設定，只在執行查詢時生效
讓查詢更易讀
有時為必要

為何使用別名？

避免重複使用冗長的資料表或欄位名稱
清楚辨識已連接的資料表與對應欄位
標識新產生的欄位
標識子查詢
當連接的資料表有同名欄位時，避免歧義
重新命名欄位

連接資料表－欄位名稱歧義

SELECT CountryName, 
       Code2, 
       Capital, 
       Pop2017
FROM Nations
INNER JOIN Cities
  ON Capital = CityName;

-----------------------------------------------
-- 錯誤，Pop2017 欄位同時存在於 
Nations 與 Cities 資料表

Ambiguous column name 'Pop2017'.

連接資料表－表格別名

-- 表格別名；Nations 為 n，Cities 為 c
SELECT n.CountryName, 
       n.Code2, 
       n.Capital, 
       c.Pop2017 -- 城市人口
FROM Nations AS n
INNER JOIN Cities AS c
  ON n.Capital = c.CityName;

CountryName	Code2	Capital	Pop2017
United Kingdom	GB	London	346774
Canada	CA	Ottawa	874433
France	FR	Paris	10437
Reunion	RE	Saint-Denis	1067
...	...	...	...

欄位重新命名

-- 欄位別名
SELECT n.CountryName AS Country, 
       n.Code2 AS CountryCode, 
       n.Capital, 
       c.Pop2017 AS Population
FROM Nations AS n
INNER JOIN Cities AS c
  ON n.Capital = c.CityName;

Country	CountryCode	Capital	Population
United Kingdom	GB	London	346774
Canada	CA	Ottawa	874433
France	FR	Paris	10437
Reunion	RE	Saint-Denis	1067
...	...	...	...

新產生的欄位

-- 新欄位以 MaxMagnitude 為別名
SELECT Country, 
       NearestPop AS City,
       MAX(Magnitude) AS MaxMagnitude
FROM Earthquakes 
GROUP BY Country, NearestPop;

Country	City	MaxMagnitude
PE	Acar	7.1
US	Aguadilla	7.7
MX	Aguililla	7.2
PW	Airai	7.8
PG	Aitape	7.6
...	...	...

子查詢

SELECT n.CountryName AS Country, 
       n.Capital, 
       e.MaxMagnitude
FROM Nations n
INNER JOIN
        (SELECT Country, NearestPop AS City
                ,MAX(Magnitude) AS MaxMagnitude
        FROM Earthquakes 
        GROUP BY Country, NearestPop) e 
              -- 子查詢以 e 為別名
    ON n.Code2 = e.Country AND n.Capital = e.City;

Country	Capital	MaxMagnitude
Fiji	Suva	7.9
Guam	Hagatna	7.8
Peru	Lima	7.6
Turkmenistan	Ashgabat	7.3
...	...	...

一起來練習吧！

改進 SQL Server 中的查詢效能