Aliases can be setup for both columns and tables of a database. Aliases are helpful when the names of the columns or tables are long and reused within the same query. Note that, the alias is a temporary name and can only be used within the same query. It does not get saved permanently in the database.
Column aliases can be used to give the columns descriptive and more appropriate names which is useful for the end user. The column alias is just a name for the sql query and it is not used for query interpretation. The alias name can be followed by the AS clause, however, note that it is optional to use the AS clause.
SELECT TOP (1000) [Id] ,[Name] ,[State] ,[DateOfBirth] AS 'Date of Birth' ,[Salary] AS Wages FROM [myDatabase].[dbo].[myTable]
The above query is outputted as below
Note that the output includes the alias (Wages and Date of Birth) instead of the actual column names.
The column alias is useful when calculations are performed in the sql query. The calculation could be quite long and not meaningful to display in the output. For example, the "DateOfBirth" column is used to calculate the age of the person using the DATEDIFF function which is available in Sql Server.
SELECT Name, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age FROM myTable;
In this example the function (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) is used to calculate the age and outputted as age. The alias can also be used within the query to refer to the field. However, depending on the database the use of alias can have restrictions. In Sql Server and mySql, the alias can be used within order by clause and it can also be used within HAVING clause in mySql.
SELECT Name, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age FROM myTable ORDER BY Age DESC;
Table alias is a temporary name given to a table within a query to improve the readibility of the query specially in long queries. It is usually used when multiple tables are used within the query and the column names need to be addressed using the tablename.column format. Note that using alias for table does not rename the table.
/* Usual Query */ SELECT myTable.Name, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age FROM myTable ORDER BY Age DESC; /* Table alias */ SELECT m.Name, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age FROM myTable m ORDER BY Age DESC;
In the above query, m is used as the alias for myTable table. The AS clause is optional. When the alias is set, the alias can be used to refer to the column names, for example, the column name "Name" is referred using "m.Name". Even though
the columns need not be referred to using the table name, but it needs to be when used using table joins when both tables have the same column names. For example, when a join is done and the same column name exist as shown below.
SELECT [Name], [State] FROM myTable JOIN [location] ON myTable.State = [location].[State];
The above query can be rewritten using aliases and the error would go away.
SELECT [Name], m.[State] FROM myTable m JOIN [location] l ON m.State = l.[State];
Note that the query is much cleaner and as more columns are used within the query, the query will require less typing.