In this article we will walk you through how to use ALIASES (temporary names for columns or tables) .
Problem
You have complex column and table names that needs to be referenced frequently, or the analysis requires data from multiple tables to be combined that has similar column names, it becomes difficult to determine which column belongs to which table.
Before going through this article we strongly recommend that you go through the following articles
1. Explaining SELECT Statement
Solution
Purpose of using Alias
An Alias is useful for simplifying your queries and making the query and its result more readable. A table or column can be temporarily given a new name by using an ALIAS. It's a temporaty change that has no impact on the database's actual table name. The database does not change the name of the table.
Here are a few explanations as to why you might think about adopting an ALIAS:- Complex column and table names can be shortened or given a more appropriate name. This clarifies and simplifies the query.
- ALIASES are in handy when using JOIN/UNION operations or aggregate methods like COUNT() and SUM ().
- If you're using a self-join, an ALIAS can be also helpful.
- It is a good idea to prefix all column names with an ALIAS when working with multiple tables so you can accurately identify which column belongs to which table.
In short,
- COLUMN ALIASES are used to make column headings in your result set easier to read.
- TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a Joins.
Syntax
The syntax of table/dataset alias is as follows −
Select column1, column2,....
from table_name / dataset name [AS] Alias_name
WHERE [condition]
The syntax of column alias is as follows −
Select column_name [AS] Alias_name
from table_name /dataset name
WHERE [condition]
column_name: The actual name of the column you want to alias.
table_name: The actual name of the table that you want to alias.
alias_name: The name that will be given temporarily.
NOTE : Important points to remember
- AS is optional when used with column / table ALIAS
- The alias name must be enclosed in quotes if it contains spaces.
- When you are aliasing a column name, spaces are permitted. Nevertheless, it is generally not a good idea to use spaces when aliasing a table name.
- The alias name is only valid within the specific SQL query.
Let's learn to use to alias a column name using an example
In this example, we will consider the courses dataset with the following data:
| Course Name | ID | No of lessons |
| SQL Basics | 1 | 12 |
| SQL Pro | 2 | 15 |
| Laravel | 3 | 10 |
| Python | 4 | 7 |
| Excel Basics | 5 | 5 |
| Excel Pro | 6 | 9 |
Let's demonstrate how to alias a column, write the following SQL statement:
Select "Course Name","No of lessons" from courses
The output should look like as follows :
| Course Name | No of lessons |
| SQL Basics | 12 |
| SQL Pro | 15 |
| Laravel | 10 |
| Python | 7 |
| Excel Basics | 5 |
| Excel Pro | 9 |
Let's use an alias for the columns.So the query should be rewritten as
Select "Course Name" Course_name ,"No of lessons" AS "Total lessons" from courses
Now the output should look like as follows :
| course_name | Total lessons |
| SQL Basics | 12 |
| SQL Pro | 15 |
| Laravel | 10 |
| Python | 7 |
| Excel Basics | 5 |
| Excel Pro | 9 |
NOTE :
The changes in column headings
AS is optional in column alias.
In this example, we've aliased the Course Name field as course_name, "No of lessons" As "Total lessons". As a result, course_name will display as the heading for the first column, Total lessons will display as the heading for the second column when the result set is returned. Because our second alias_name include spaces, we are required to enclose the alias_name in quotes.
Example - How to Alias a Table Name
When you alias a table, you either want to shorten the table name to make the SQL statement shorter and simpler to read, or you intend to put the same table name more than once in the FROM clause or WHERE clause.
Let's demonstrate how to alias a table name using an example, write the following SQL statement:
Select "Course Name" ,"No of lessons" as "Total lessons" from courses AS c
where c."No of lessons" >9
The output should like as follows :
| Course Name | Total lessons |
| SQL Basics | 12 |
| SQL Pro | 15 |
| Laravel | 10 |
NOTE :
AS is optional in table alias
In this example, we've created an alias for the courses table/dataset . Now within this SQL statement, we can refer to the courses table/datasets with a shortened name as c.
When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.
Table ALIAS is handy while joining tables / datasets. This will be further discussed in Joins articls.
Let's practice an example using Impact Cloud Dashboard
Navigate to SQL Query Editor from SQL Lab.
Lets demonstrate the column and table alias using the below SELECT statement
Select "Course Name","No of lessons" as "Total lessons" from gc_demo.courses AS C
where c."No of lessons" >9
NOTE : courses dataset resides in gc_demo. So, in Impact Cloud the dataset is identified as gc_demo.courses
The output would like as follows :
TO DO :
1. Execute all the examples learnt above with SELECT Statement to get familiar with Impact Cloud.
2. Adapt the SELECT Statement with ALIAS to suit your requirement.
Congratulations !!! You have learnt how to create an alias for Column / Dataset.