Dec 20, 2022 by Robert Gravelle

Aliases temporarily rename a table or a column in such a way that does not affect the underlying table(s) or view(s). As a feature of SQL that is supported by most, if not all, relational database management systems, aliases are a great way to both simplify your queries and/or customize the column headers in your result sets. In this blog, we'll do both, using Navicat Premium 16.

Renaming Columns

A lot of database designers use abbreviations for the table column names to keep them short, for example:

• emp_no for "Employee Number"
• qty for "quantity"

The full value of abbreviated column names are not always intuitive to those who view the query results. To remedy that, you can use column aliases that give columns more descriptive names in the result set.

The syntax for column aliases is:

column_name [AS] alias_name

Note that the AS keyword is optional.

You can include whitespace in your aliases, by enclosing them within single (or double) quotes like this:

column_name AS 'Alias Name'

Here's an example query that includes a few column aliases:

Using Aliases for Expressions

You've probably noticed that, if a query contains expressions, the entire expression is utilized as the column header. For example:

Assigning a column alias to the expression makes it much more palatable:

Table Aliases

Table aliases follow the same rules as column aliases, but their purpose is different, as table aliases don't appear anywhere in the query results. In their case, the idea is to use a shorter name in order to associate columns with their table in a way that shortens your queries.

The basic syntax of a table alias is as follows:

SELECT column1, column2....
FROM table_name [AS] alias_name
WHERE [condition];

A column that is associated to its table is called a qualified column name. Columns need to be qualified when two columns with the same name appear in the same SELECT statement. In fact, we saw an example of qualified column names in the column alias example above. Here's another query that contains two actor_id columns - one from the actors table and another from the film_actor table:

Although the above query is perfectly functional, we can shorten it by employing table aliases:

Notice that unambiguous columns, i.e., those which only appear in one table, do not need to be qualified.

Another way that table aliases are helpful comes into play when using modern database tools like Navicat. Thanks to the auto-suggest feature, typing the table alias causes a drop-down of suggestions. In the case of table aliases, the drop-down will contain all the table columns:

This greatly accelerates query writing, which is an important part of professional database development.

Conclusion

This blog provided an overview of column and table aliases, along with some practical examples in Navicat Premium 16.

If you're interested in learning more about Navicat Premium 16, you can try the full unrestricted version out for 14 days completely free of charge!