In SQL, ALIAS means another name given to objects like columns & tables. You can use column and table aliases to make sql query more readable and easier for developer too to write the query. In most of the cases, creating an alias is optional however in the cases where you are joining multiple tables then it is a good approach to create table level aliases. This also avoids error in case the same column name exist in both the tables participating in joins. Also while creating derived columns, it is highly recommended to create alias names to make it more readable & meaningful.
You can use "AS" keyword to correspond to an alias however it is optional. This means you can create an alias to columns or tables by using space or as keyword. However using keywords "as" is recommended as it makes query easier to read and understand.
Redshift ALIAS example
select t1.catname as category_name, t1.catdesc as category_description, t2.eventname as event_name from category as t1 inner join event as t2 on t1.catid = t2.catid where t1.catname = 'Plays' limit 5;
|Plays||All non-musical theatre||The Bacchae|
|Plays||All non-musical theatre||The Cherry Orchard|
|Plays||All non-musical theatre||Macbeth|
|Plays||All non-musical theatre||Look Back in Anger|
|Plays||All non-musical theatre||Young Frankenstein|
In the above example you can see we have created alias names for columns (category_name, category_description) used in SELECT statement. We have also created alias for tables (t1, t2) used in joins.
Redshift ALIAS key points
- It makes your query more readable and output result-set has more meaningful name for columns header.
- Most of the time creating alias name for columns is optional. However when you are creating a derived column make sure to give proper alias name to it. This also makes it easier to refer in subsequent steps. Also if you are creating derived table or running CTAS, giving alias to derived columns is good practice to avoid errors.
- If you want to give space in alias name then enclosed it within double quotes " ". This also works when you wish to use reserved keywords as column name.
- Alias reduces query length by allowing developer to use shorten TABLENAME in the query as alias name.
- Table alias is recommended especially during JOINS. This also avoids confusion & error if both the tables participating in JOIN has the same column name.
- Also keyword "as" is optional however it is good sql practice to use it.