Table Aliases

In a join, the names of the tables come up again and again: in ON, and in front of each column that both tables have. Queries get long. That's why you can give a table a short name, an alias, right after its name in FROM or JOIN. This is the query from the previous exercise, with aliases:

SELECT g.title, r.stars, r.comment
FROM reviews AS r
JOIN games AS g ON r.game_id = g.id
ORDER BY g.title, r.customer_id;

r now stands for reviews and g for games in the whole query, even in SELECT, which comes before FROM: the database reads the whole query before it runs it. An alias is usually the first letter of the table's name, or a few letters, so that it's clear which table is which. For tables, AS is optional: FROM reviews r JOIN games g ON … means the same, and you'll often see it written that way.

g.title is a qualified column name: the alias of the table (or its name, when it has no alias), a dot, and the name of the column. In the result, the column is called just title. Once a table has an alias, use the alias everywhere: the table's own name doesn't work in that query anymore, and reviews.stars would end with the error no such column: reviews.stars.

Qualified names are a must when both tables have a column with the same name. order_items and games both have a price, and if you write just price, the database doesn't know which one you mean:

SELECT title, price, quantity
FROM order_items AS i
JOIN games AS g ON i.game_id = g.id;

Error on line 1: ambiguous column name: price

Ambiguous means “it could mean more than one thing”. SQLite doesn't guess, and that's good: it makes a big difference whether you get the price the customer paid (i.price) or the game's price in the catalog (g.price). The same goes for id, which orders, customers and games all have. A column that's in only one of the tables, such as title, works without the alias, but in a query with a join, it's a good habit to qualify every column. Whoever reads the query (you, a month from now) sees at once where each column comes from.

When you want the same column from both tables, rename them with AS, as you learned in lesson 2, so that the result doesn't have two columns with the same name:

SELECT o.id AS order_id, c.id AS customer_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY o.id;

Note ORDER BY o.id: just id would be ambiguous there too.