How to use column alias and table alias in MySQL?

In MySQL, you we can use aliases in SQL statement, including: column aliases, table aliases, and derived table aliases.

Column aliases

In many cases, the readability of the column names of a table is not good and difficult to understand. But, you can customize column names in the result set by using column aliases,.

Column alias syntax

The following shows how to use column aliases:

SELECT column_name AS `alias`
FROM table_name;

Column alias usage instructions:

The AS keyword is followed by the alias of the column, and it is optional.
If an column alias when contains spaces, you should use ` quote the column alias, for example: `alias`.
In addition to specifying aliases for columns, you can also specify aliases for expressions, for example: SELECT NOW() `Current Time` FROM dual.

Column alias example

In the following examples, we will use the actor table from Sakila sample database as a demonstration.

Select rows without column alias

SELECT
      first_name,
      last_name,
      CONCAT(first_name, ', ', last_name)
FROM
      actor
LIMIT 5;

  +------------+--------------+-------------------------------------+
  | first_name | last_name    | CONCAT(first_name, ', ', last_name) |
  +------------+--------------+-------------------------------------+
  | PENELOPE   | GUINESS      | PENELOPE, GUINESS                   |
  | NICK       | WAHLBERG     | NICK, WAHLBERG                      |
  | ED         | CHASE        | ED, CHASE                           |
  | JENNIFER   | DAVIS        | JENNIFER, DAVIS                     |
  | JOHNNY     | LOLLOBRIGIDA | JOHNNY, LOLLOBRIGIDA                |
  +------------+--------------+-------------------------------------+
  5 rows in set (0.00 sec)

This column name CONCAT(first_name, ', ', last_name) is poorly readable and difficult to understand.

Select rows with column alias

SELECT
      first_name `First Name`,
      last_name `Last Name`,
      CONCAT(first_name, ', ', last_name) `Full Name`
FROM
      actor
LIMIT 5;

+------------+--------------+----------------------+
| First Name | Last Name    | Full Name            |
+------------+--------------+----------------------+
| PENELOPE   | GUINESS      | PENELOPE, GUINESS    |
| NICK       | WAHLBERG     | NICK, WAHLBERG       |
| ED         | CHASE        | ED, CHASE            |
| JENNIFER   | DAVIS        | JENNIFER, DAVIS      |
| JOHNNY     | LOLLOBRIGIDA | JOHNNY, LOLLOBRIGIDA |
+------------+--------------+----------------------+
5 rows in set (0.00 sec)

In this example, we have specified aliases for the following columns:

The first_name column has a alias First Name.
The last_name column has a alias Last Name.
The CONCAT(first_name, ', ', last_name) expression has a alias Full Name.

The column names in the result set are more readable than the above example.

Table alias

You can also assign aliases to tables, as follows:

table_name AS alias

The AS keyword is optional, so you can omit it.

If there are multiple tables in a SQL statement, it is important to use table aliases, especially when multiple tables contain the same column name. Without specified table alias, you can use table_name.column_name to reference columns for each table. When you specify an alias, you can alias.column_name.

Let’s look at an example from the MySQL EXIST section:

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

In the above example, we use film.language_id and language.language_id to cite the language_id column from film and language tables .

Let us specify a alias for each table, as follows:

SELECT *
FROM language l
WHERE EXISTS(
    SELECT *
    FROM film f
    WHERE f.language_id = l.language_id
  );

Derived table alias

A derived table is a table generated by an SELECT statement. Using a derived table is just like using a table. A derived tables are usually generated by a subquery, for example:

SELECT
    AVG(t.customer_total) customer_avg
FROM
    (SELECT
        customer_id, SUM(amount) customer_total
    FROM
        payment
    GROUP BY customer_id
    HAVING customer_total > 180) t;

+--------------+
| customer_avg |
+--------------+
| 201.585000 |
+--------------+
1 row in set (0.02 sec)

In this example, here is the derived table:

(SELECT
    customer_id, SUM(amount) customer_total
FROM
    payment
GROUP BY customer_id
HAVING customer_total > 180)

this derived table has a alias named t.

A derived table must have a alias. Because, all tables in the FROM clause must have a name.

Conclusion

In this article, you learned column aliases, table aliases and derived table aliases in MySQL. The following are the main points of this chapter:

Aliases can improve the readability of SQL statements.
A alias is after the AS keyword, but the AS keyword is optional.
When a alias contains spaces, you must use ` quote it.
The derived table must be assigned an alias.