MySQL table aliases

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A table alias is a short name for a table, for the length of 1 query. It saves typing in a join, and there are 2 situations where a query does not work without one. Open the client with mysql -u root -p sakila before you start.

How a table alias works

SELECT ALIAS_NAME.COLUMN_NAME
FROM TABLE_NAME AS ALIAS_NAME;

Replace TABLE_NAME with the table, ALIAS_NAME with the short name you want, and COLUMN_NAME with a column of that table. From that point on the alias is the name of the table in this query, and you write ALIAS_NAME.COLUMN_NAME to name a column in it. AS is optional here as it is with a column alias, so FROM film f means the same as FROM film AS f. Writing AS makes the intent plain.

The same join, written both ways

Without aliases, every qualified column carries the full table name.

SELECT film.title, language.name
FROM film
JOIN language ON film.language_id = language.language_id
ORDER BY film.title
LIMIT 3;

+------------------+---------+
| title            | name    |
+------------------+---------+
| ACADEMY DINOSAUR | English |
| ACE GOLDFINGER   | English |
| ADAPTATION HOLES | English |
+------------------+---------+
3 rows in set (0.00 sec)

With aliases, the same query is shorter and the shape is easier to see.

SELECT f.title, l.name
FROM film AS f
JOIN language AS l ON f.language_id = l.language_id
ORDER BY f.title
LIMIT 3;

+------------------+---------+
| title            | name    |
+------------------+---------+
| ACADEMY DINOSAUR | English |
| ACE GOLDFINGER   | English |
| ADAPTATION HOLES | English |
+------------------+---------+
3 rows in set (0.00 sec)

Identical rows. An alias changes nothing about the answer.

A column that exists in both tables has to be qualified

film and language both have a language_id. Naming it bare gives the server no way to choose.

SELECT language_id
FROM film
JOIN language ON film.language_id = language.language_id
LIMIT 1;

ERROR 1052 (23000): Column 'language_id' in field list is ambiguous

ERROR 1052 means the server found more than 1 thing the name could refer to. In a join that is nearly always a column 2 tables share, and qualifying it with the table or alias you meant fixes it. A column name that only 1 table has needs no qualification, which is why title and name work bare in the queries above.

The alias replaces the table name

Once a table has an alias, the original name is gone for the rest of the query.

SELECT film.title
FROM film AS f
JOIN language AS l ON f.language_id = l.language_id
LIMIT 1;

ERROR 1054 (42S22): Unknown column 'film.title' in 'field list'

The table is still film in the database. Inside this query it answers to f and nothing else. ERROR 1054 on a name you can see in the schema is usually this.

The case that cannot be written without an alias

A query that names the same table twice has 2 things called actor and no way to tell them apart. Aliases are what make that query possible, and the Self join lesson is built on it.

A table alias is not a column alias

They use the same keyword and do different jobs. A column alias renames a column of the result and appears in the heading. A table alias renames a table inside the query and never appears in the output at all. See Column aliases for the other one.

Summary

Use TABLE_NAME AS ALIAS_NAME to give a table a short name for 1 query.
Qualify any column name that more than 1 table in the query has.
Read ERROR 1052 in a join as a column name you need to qualify.
Suspect a forgotten alias when ERROR 1054 names a column you know exists.
Expect an alias to change nothing about the result.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a table alias works

The same join, written both ways

A column that exists in both tables has to be qualified

The alias replaces the table name

The case that cannot be written without an alias

A table alias is not a column alias

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a table alias works

​The same join, written both ways

​A column that exists in both tables has to be qualified

​The alias replaces the table name

​The case that cannot be written without an alias

​A table alias is not a column alias

​Summary

​See also

How a table alias works

The same join, written both ways

A column that exists in both tables has to be qualified

The alias replaces the table name

The case that cannot be written without an alias

A table alias is not a column alias

Summary

See also