MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.

When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.

Example

Suppose we have a simple table statement like this:

TABLE t1;

Result:

+--------+-------+
| c1     | c2    |
+--------+-------+
| Black  | Dog   |
| Blue   | Fish  |
| Green  | Cow   |
+--------+-------+

In this case, the table’s column names are c1 and c2. The result of the TABLE statement simply presents these column names as they are named in the table.

We can rename these columns by placing the TABLE statement into a subquery, then applying aliases to the columns. Below are two ways to do this.

Option 1

Here’s one way to rename the columns:

SELECT * FROM (TABLE t1) AS t( Color, Animal );

Result:

+--------+--------+
| Color  | Animal |
+--------+--------+
| Black  | Dog    |
| Blue   | Fish   |
| Green  | Cow    |
+--------+--------+

We can see that the columns have been renamed to Color and Animal respectively.

Option 2

Another way to apply the aliases is like this:

SELECT 
    c1 AS Color,
    c2 AS Animal
FROM (TABLE t1) AS t;

Result:

+--------+--------+
| Color  | Animal |
+--------+--------+
| Black  | Dog    |
| Blue   | Fish   |
| Green  | Cow    |
+--------+--------+

This method is slightly more explicit, due to the fact that we single out each column in the SELECT list and rename it there.