Aliasing

Improving Query Performance in SQL Server

Dean Smith

Founder, Atamai Analytics

What is aliasing?

Used in queries to identify:
- Tables
- Columns
- Sub-queries
Temporary, only applied when the query is run
Makes the query easier to read
May be required

Why use aliasing?

Avoid repetitive use of long table or column names
Easily identify joined tables and associated columns
Identify new columns
Identify sub-queries
Avoid ambiguity when columns from joined tables share the same name
Rename columns

Joined tables - ambiguous column name

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

-----------------------------------------------
-- ERROR, Pop2017 column is in 
both the Nations and Cities tables

Ambiguous column name 'Pop2017'.

Joined tables - aliasing table names

-- Alias tables; Nations as n and Cities as c
SELECT n.CountryName, 
       n.Code2, 
       n.Capital, 
       c.Pop2017 -- City population
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
...	...	...	...

Renamed columns

-- Alias columns; 
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
...	...	...	...

New columns

-- New colunm aliased as 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
...	...	...

Sub-queries

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 
              -- Sub-query aliased as 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
...	...	...

Let's practice

Improving Query Performance in SQL Server