MySQL Aliases (AS)

MySQL Aliases: Temporary Names for Columns and Tables

A MySQL alias assigns a temporary, readable name to a column or table for the duration of a single query. Aliases are created with the AS keyword and never rename the underlying schema in your database.

Column Aliases vs. Table Aliases

• Column Aliases: Give calculated expressions like SUM(total_amount) or CONCAT() clean JSON-friendly key names in your result set.
• Table Aliases: Shorten long table names (such as demo_customers AS c) so multi-table joins and self-joins stay concise and unambiguous.

SELECT c.first_name AS customer_name, COUNT(o.id) AS total_orders
FROM demo_customers AS c
LEFT JOIN demo_orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.first_name;

Practical SQL Example

This query combines table aliases (o, c) and descriptive column aliases to summarize customer spending:

SELECT
    c.id AS customer_id,
    c.email AS customer_email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0.00) AS lifetime_spend
FROM demo_customers AS c
LEFT JOIN demo_orders AS o
    ON o.customer_id = c.id
   AND o.status = 'paid'
GROUP BY c.id, c.email
ORDER BY lifetime_spend DESC;

Common Mistakes and Best Practices

• Where Column Aliases Work (and Fail): Because MySQL evaluates WHERE before SELECT, you cannot use a SELECT column alias inside WHERE. However, MySQL allows column aliases inside GROUP BY, HAVING, and ORDER BY.
• Quote Aliases Only When Required: Avoid spaces in aliases so you never need quotes; write AS total_revenue instead of AS "Total Revenue".
• Keep AS for Columns: Writing AS before column aliases (price * qty AS line_total) prevents bugs if you accidentally forget a comma between two column names.

Frequently Asked Questions

Is the AS keyword mandatory for aliases in MySQL?

No, AS is optional for both column and table aliases, though keeping AS for columns makes missing-comma bugs obvious.

Why does WHERE lifetime_spend > 100 throw an "Unknown column" error?

SQL evaluates WHERE before SELECT computes aliases, so move aggregate filters to HAVING or repeat the raw expression in WHERE.

Are table aliases required in self-joins?

Yes, when joining a table to itself you must give each reference a distinct alias so MySQL can tell the left and right rows apart.