A SQL Server alias is the user friendly name. SQL aliases are used to temporarily rename a database table or a column heading in a table.
A derived table is a table expression that appears in the FROM clause of a query. You can apply derived tables when the use of column aliases is not possible because another clause is processed by the SQL translator before the alias name is known.
Example 1 shows an attempt to use a column alias where another clause is processed before the alias name is known.
Scenario: Get all existing groups of months from the enter_date column of the works_on table:
SELECT MONTH(Enter_Date) as Enter_Month FROM WORKS_ON
GROUP BY Enter_Month
Error: Invalid column name ‘Enter_Month’.
The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name enter_month is not known at the time the grouping is processed.
By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause, as shown in Example 2.
Example2:
SELECT Enter_Month FROM (SELECT MONTH(Enter_Date) AS Enter_Month
FROM WORKS_ON ) AS m
GROUP BY Enter_Month;
Why we cannot use column alias in where clause but we can use it in order by clause of select statement in SQL server?
In SQL server order of execution of different clauses of a select statement is following order:
The WHERE clause executes before the SELECT clause so WHERE clause has no knowledge about column alias of SELECT clause while ORDER BY clause executes after the SELECT clause so it know about column alias of SELECT clause.
Sometimes you want to change the column names to make them more readable, SQL aliases are used to change the names of tables or columns headings, it is used as follows:
Syntax for Column Alias
SELECT column_name AS new_name
FROM TableName ;
Syntax for Table Alias
SELECT column_name
FROM TableName AS new_name ;
Note: The use of the AS keyword is optional but its use is recommended.
Syntax for Column Alias
SELECT column_name AS new_name
FROM TableName ;
Syntax for Table Alias
SELECT column_name
FROM TableName AS new_name ;
Note: The use of the AS keyword is optional but its use is recommended.
Usage:
- The columns after a concatenation or computing are not named, it is recommended to rename these columns.
- This is particularly useful when you use Joins between tables and it is required in Self-Joins.
Example 1: SELECT
[CardType] AS [Card Type]
,[CardNumber] AS [Card Number]
FROM [Sales].[CreditCard];
Example 2:
The columns after a concatenation or computing are not named, it is recommended to rename these columns:
Without Alias SELECT [Title] ,[FirstName] ,[LastName] ,[FirstName] +' '+ [LastName] FROM [Person].[Person] ;
Here now with the application of the alias:SELECT [Title] ,[FirstName] ,[LastName] ,[FirstName] +' '+ [LastName] AS [Full Name] FROM [Person].[Person];
Aliases on a Table
This can help to have shorter names, simpler. This is particularly useful when you use Joinsbetween tables and sometimes it is required in Self-Joins.
Example 2:
The columns after a concatenation or computing are not named, it is recommended to rename these columns:
Without Alias SELECT [Title] ,[FirstName] ,[LastName] ,[FirstName] +' '+ [LastName] FROM [Person].[Person] ;
With Alias
Here now with the application of the alias:SELECT [Title] ,[FirstName] ,[LastName] ,[FirstName] +' '+ [LastName] AS [Full Name] FROM [Person].[Person];
Aliases on a Table
This can help to have shorter names, simpler. This is particularly useful when you use Joinsbetween tables and sometimes it is required in Self-Joins.
Examples:
Example 1 shows an attempt to use a column alias where another clause is processed before the alias name is known.
Scenario: Get all existing groups of months from the enter_date column of the works_on table:
SELECT MONTH(Enter_Date) as Enter_Month FROM WORKS_ON
GROUP BY Enter_Month
Error: Invalid column name ‘Enter_Month’.
The reason for the error message is that the GROUP BY clause is processed before the corresponding SELECT list, and the alias name enter_month is not known at the time the grouping is processed.
By using a derived table that contains the preceding query (without the GROUP BY clause), you can solve this problem, because the FROM clause is executed before the GROUP BY clause, as shown in Example 2.
Example2:
SELECT Enter_Month FROM (SELECT MONTH(Enter_Date) AS Enter_Month
FROM WORKS_ON ) AS m
GROUP BY Enter_Month;
The WHERE clause executes before the SELECT clause so WHERE clause has no knowledge about column alias of SELECT clause while ORDER BY clause executes after the SELECT clause so it know about column alias of SELECT clause.
