Aliasing (Azure Synapse Analytics, Parallel Data Warehouse)
Applies to: Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric
Aliasing allows the temporary substitution of a short and easy-to-remember string in place of a table or column name in Azure Synapse Analytics or Analytics Platform System (PDW) SQL queries. Table aliases are often used in JOIN queries because the JOIN syntax requires fully qualified object names when referencing columns.
Aliases must be single words conforming to object naming rules. For more information, see "Object Naming Rules" in the Analytics Platform System (PDW) product documentation. Aliases cannot contain blank spaces and cannot be enclosed in either single or double quotes.
Syntax
object_source [ AS ] alias
Arguments
object_source
The name of the source table or column.
AS
An optional alias preposition. When working with range variable aliasing, the AS keyword is prohibited.
alias The desired temporary reference name for the table or column. Any valid object name can be used. For more information, see "Object Naming Rules" in the Analytics Platform System (PDW) product documentation.
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
The following example shows a query with multiple joins. Both table and column aliasing are demonstrated in this example.
Column Aliasing: Both columns and expressions involving columns in the select list are aliased in this example.
SalesTerritoryRegion AS SalesTR
demonstrates a simple column alias.Sum(SalesAmountQuota) AS TotalSales
demonstratesTable Aliasing:
dbo.DimSalesTerritory AS st
shows creation of thest
alias for thedbo.DimSalesTerritory
table.
-- Uses AdventureWorks
SELECT LastName, SUM(SalesAmountQuota) AS TotalSales, SalesTerritoryRegion AS SalesTR,
RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SUM(SalesAmountQuota) DESC ) AS RankResult
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory AS st ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE SalesPersonFlag = 1 AND SalesTerritoryRegion != N'NA'
GROUP BY LastName, SalesTerritoryRegion;
The AS keyword can be excluded, as shown below, but is often included for readability.
-- Uses AdventureWorks
SELECT LastName, SUM(SalesAmountQuota) TotalSales, SalesTerritoryRegion SalesTR,
RANK() OVER (PARTITION BY SalesTerritoryRegion ORDER BY SUM(SalesAmountQuota) DESC ) RankResult
FROM dbo.DimEmployee e
INNER JOIN dbo.FactSalesQuota sq ON e.EmployeeKey = sq.EmployeeKey
INNER JOIN dbo.DimSalesTerritory st ON e.SalesTerritoryKey = st.SalesTerritoryKey
WHERE SalesPersonFlag = 1 AND SalesTerritoryRegion != N'NA'
GROUP BY LastName, SalesTerritoryRegion;
Next steps
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for