What is the difference between a table alias and a column alias, and when do I need each one?
A table alias gives the table a short name for the rest of the query, and a column alias gives a column a different name in the answer. They solve different problems.
You need a table alias in two situations. The first one is when the same table appears more than once in a query, which happens most often when a table refers to itself. The second one is when the table name is long and the query would otherwise be unreadable.
CREATE TABLE staff (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
manager_id INTEGER REFERENCES staff(id)
);
-- A table that refers to itself needs two names for it.
SELECT
member.name AS member_name,
manager.name AS manager_name
FROM staff AS member
JOIN staff AS manager ON manager.id = member.manager_id;
So without the aliases, the second table in that query would be the same table as the first one, and the engine would have no way of telling which column of which copy you meant. The aliases are not there for looks, they are there to make the query possible.
A column alias is the AS word. It renames a column in the answer, and it is useful for three things. It gives a long expression a short name. It gives a computed column a readable name. And it lets you give a name to a column that has an awkward name in the table, such as one with spaces in it.
-- A column alias for a computed column.
SELECT
id,
quantity * unit_price AS line_total,
ROUND(quantity * unit_price * 0.2, 2) AS tax_at_twenty_percent
FROM sales_lines;
So when do you need each one? The rule is that you need a table alias whenever the same table appears twice, and you want a column alias whenever the answer has a column whose name is hard to read or hard to use. The two never replace each other, and they go in different places.
The next question in this topic asks you to rewrite a join with both of them.