FROM
FROM names the tables a query reads; its aliases are visible to every other clause, but select-list aliases are not visible to WHERE.
FROM tbl_name [[AS] alias_name] [, tbl_name ...] | join_clause | (subquery) AS alias_nameFROM supplies the rows. Every other clause — the select list, WHERE, GROUP BY, HAVING, ORDER BY — can only refer to columns that FROM has made available. A table reference is a table name, optionally qualified as db_name.tbl_name, or a view, or a derived table, or a join of several of these.
Aliases, long and short
orders AS o and orders o are the same table reference; AS is optional here exactly as it is in the select list. Once a table is aliased, use the alias — it is the name the rest of the statement knows the table by, and a locking clause such as FOR UPDATE ... OF accepts only the alias when one was given. Aliases are mandatory in two places: a self-join, where the same table appears twice and an unqualified column name would be ambiguous, and a derived table, because every table in a FROM clause must have a name.
Which way names flow
A table alias declared in FROM is visible to WHERE, and to the select list, GROUP BY, HAVING and ORDER BY. A column alias declared in the select list is not visible to WHERE, because WHERE decides which rows exist before the select list is computed. So WHERE o.total > 100 is fine while WHERE net_total > 100, naming a select-list alias, is error 1054. The visibility runs one way only.
Example
-- Longhand: AS on every table alias.
SELECT o.id, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid'; -- the FROM alias is in scope here
-- Shorthand: identical statement, AS omitted. Both are valid MySQL; pick one
-- and be consistent, because mixed styles hide missing commas.
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid';When to use it
- Joining two tables and keeping every column reference unambiguous.
- Filtering on an aggregate by wrapping the aggregation in a named derived table.
- Writing a migration script that works regardless of the session's default database.
More examples
A self-join, where aliases are not optional
Any hierarchy stored as a parent id column — employees and managers, categories and parents, comments and replies — needs this shape. The aliases are what turn one table into two logical sources.
-- The same table appears twice, so "id" and "name" would be ambiguous without
-- aliases. e and m are two independent row streams over one table.
SELECT e.name AS employee,
m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY manager, employee;Naming a derived table
Wrapping a query in a derived table is the standard way to filter on an aggregate or a computed alias that WHERE could not see in the inner query. Note that the alias is required whether or not you ever write s.
-- ERROR 1248 (42000): Every derived table must have its own alias
-- SELECT * FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id);
-- Correct: give it a name, and its columns become ordinary columns of that name.
SELECT s.customer_id, s.spend
FROM (SELECT customer_id, SUM(total) AS spend
FROM orders
WHERE status = 'paid'
GROUP BY customer_id) AS s
WHERE s.spend > 1000 -- spend IS in scope: it is a column of s now
ORDER BY s.spend DESC;Qualifying across databases
Useful when an analytics or archive schema sits beside the application schema on the same server. It also makes migration scripts unambiguous, since they no longer depend on whoever ran USE last.
-- A session has one default database, but a statement is not limited to it.
-- Qualified names read the same regardless of which database you connected to.
SELECT b.title, a.name AS author, COUNT(v.id) AS views
FROM catalog.books AS b
JOIN catalog.authors AS a ON a.id = b.author_id
LEFT JOIN analytics.book_views AS v ON v.book_id = b.id
GROUP BY b.id, b.title, a.name;
Discussion