Column and Table Aliases

In this short lesson, you are going to learn about table and column aliases.

What is an Alias?

SQL provides the ability to rename columns or to provide column names for literals or expressions returned by a query during its execution.  This new name that you assign to the column or expression is what is called an “alias”.

For example, let’s take a look at the query you wrote at the end of the previous lesson, which was probably something like this:

SELECT id, name, salary, salary * 1.20
FROM employee;

If you look at the result set you got, you will notice that the last column, which corresponds to the new salary, has the expression you used to calculate it as the column name.  I mean, the title of the column appears as “salary*1.20” or some similar expression.

Your task was to provide a list that included the current and new salaries, but by looking at this result set, it is not completely clear which one is the old and which the new salary, unless the person reading the results knows that the new salary was going to be calculated as the old salary plus a 20% increase.

And it is in this kind of situations that the ability to provide column aliases can be very useful.

Column Aliases

To provide a column alias you can simply write the alias next to the column you are renaming, or you can use the optional reserved word “AS” and then provide the column alias.

So, we could write our query this way:

SELECT id, name, salary AS old_salary, salary * 1.20 new_salary
FROM employee;

Here, for the old salary, I defined the alias using the AS keyword, but for the new salary, I did it just by writing the alias next to the column or expression I was renaming.  Both ways to do it are correct and produce the exact same results.

I usually like to do it using the AS keyword, because I think it improves readability, but they are equivalent.

It is also worth mentioning that “normal” identifiers, such as table and column names can only contain alphanumeric characters and the underscore.

I used the underscore to separate words because that is how people usually name table columns, but you can actually create columns whose names include blank spaces.  You just need to enclose the column name in double quotes, and that is something many people don’t know.  These are called “quoted identifiers”.

For example, I could create a table with a column called “two words”, but then, any time I want to use or reference that column, I have to do it using the double quotes as well, for example:

SELECT "to words"
FROM my_table;

And you can also have column aliases defined that way, so, we can write the query from the previous task like this:

SELECT id, name, salary AS “old salary”, salary * 1.20 “new salary”
FROM employee;

This result set looks better, don’t you think?

Table Aliases

And in a similar way, you can provide aliases to table names also.  By now, you probably don’t see why you would want to provide an alias for a table, but you will when we start working with more than one table in a single query.

To provide an alias for a table you write the alias next to the table name.

And you can also provide aliases inside double quotes if they include blanks.

So, I could provide an alias for the employee table this way:

SELECT id, name, salary AS “old salary”, salary * 1.20 “new salary”
FROM employee e;

Here the alias for the employees table is now a single letter, in this case, “e”.

Okay, we are done with aliases by now.

There is no task for you today, but you will have the opportunity to play with aliases in a future lesson.

See you in the next one!