Using Database Aliases

Jul 10, 2023 by Robert Gravelle

SQL supports the use of aliases to give a table or a column a temporary name. Not only can they save on typing, but aliases can also make your queries more readable and understandable. In today’s blog, we’ll learn how to incorporate aliases into our queries using Navicat Premium 16.2.

As mentioned in the introduction, both table and column names may be aliased. Here is the syntax for each:

Alias Column Syntax

SELECT 
  column_name [AS] alias_name,
  column_name AS 'Alias Name' -- for names with spaces
FROM table_name;

Alias Table Syntax

SELECT column_name(s)
FROM table_name [AS] alias_name;

Two points to consider regarding aliases:

• An alias is usually preceded by the AS keyword, but it is optional.
• An alias only exists for the duration of that query.

Here’s a query against the Sakila Sample Database that fetches information about all copies of a particular film:

SELECT *
FROM film f 
  INNER JOIN inventory i ON i.film_id = f.film_id
WHERE i.store_id = 1 AND f.title = "Academy Dinosaur";

In the above query, since both the film and inventory tables contain a film_id column, they must be fully qualified, i.e., prefixed by the table name. In this case, aliases may be employed to shorten the statement.

Here is the query in Navicat along with the results:

In the case of column names, abbreviations are often utilized to keep column names short when designing database tables. For example:

• “so_no” for “sales order number”.
• “qty” stands “quantity”

Here, column aliases may be employed to make the column contents more intuitive. Here’s an example:

SELECT
	inv_no AS invoice_no,
	amount,
	due_date AS 'Due date',
	cust_no 'Customer No'
FROM
	invoices;

You can also assign column aliases to expressions, as seen below:

The above query selects both the current and future price of products after applying a price increase.

Since column aliases are assigned in the SELECT clause, you can only reference the aliases in the clauses that are evaluated after the SELECT clause. Hence, you cannot include aliases in the WHERE clause; doing so will result in an error:

This happens because the database evaluates the WHERE clause before the SELECT clause. Therefore, at the time it evaluates the WHERE clause, the database doesn’t have the information of the NewPrice column alias.

It is however permissible to use column aliases in the ORDER BY clause because it is evaluated after the SELECT clause:

The database evaluates the clauses of the query in the following order:

FROM > SELECT > ORDER BY

In Navicat, once a table alias has been defined, it will come up in the auto-complete list.

That makes using aliases even more time saving!

In today’s blog, we learned how to incorporate aliases into our queries using Navicat Premium 16.2. Aliases are an easy way to make your queries more readable and understandable, which is important because code isn’t just about execution; it’s also a communication mechanism.