Aliases — MySQL

SQL Aliases in MySQL

Aliases give columns and tables temporary names within a query. They make output more readable, shorten verbose table names in JOIN-heavy queries, and are required in specific SQL constructs like derived tables. Understanding exactly where MySQL allows — and where it forbids — alias references will save you from confusing "Unknown column" errors.

Column Aliases with AS

A column alias renames the output column in the result set. Use the AS keyword (optional but strongly recommended for clarity). The alias exists only in the query output — it does not change the column name in the underlying table.

SQL

-- Basic column aliases
SELECT
  first_name AS given_name,
  last_name  AS family_name,
  email      AS contact_email
FROM customers;

-- AS keyword is optional — both forms are equivalent
SELECT first_name given_name, last_name family_name
FROM customers;

-- Aliasing computed expressions
SELECT
  price * quantity                    AS line_total,
  price * quantity * 0.13             AS tax_amount,
  price * quantity * 1.13             AS total_with_tax
FROM order_items;

-- Aliasing aggregate results (essential for GROUP BY reports)
SELECT
  DATE_FORMAT(created_at, '%Y-%m')  AS sale_month,
  COUNT(*)                          AS order_count,
  ROUND(SUM(total), 2)              AS gross_revenue,
  ROUND(AVG(total), 2)              AS avg_order_value
FROM orders
WHERE status = 'delivered'
GROUP BY sale_month
ORDER BY sale_month;

Note

Aliases only rename the output column. The underlying table column is unchanged after the query runs. The alias is not visible to other queries or sessions.

Quoting Aliases

Alias names that contain spaces, special characters, or match reserved SQL keywords must be quoted. MySQL accepts backtick quotes (preferred), double quotes (ANSI mode only), or single quotes (technically non-standard but MySQL accepts them).

Best practice: use lowercase snake_case aliases that need no quoting at all.

SQL

-- Alias containing a space: must be quoted
SELECT
  first_name AS `First Name`,
  last_name  AS `Last Name`,
  created_at AS `Account Created`
FROM customers;

-- Alias matching a reserved keyword: must be quoted
SELECT COUNT(*) AS `count`, SUM(total) AS `sum`
FROM orders;

-- Best practice: snake_case alias — no quoting required, works everywhere
SELECT
  first_name          AS first_name,
  last_name           AS last_name,
  created_at          AS created_at,
  COUNT(*)            AS order_count,
  ROUND(SUM(total),2) AS total_revenue
FROM customers
JOIN orders USING (customer_id)
GROUP BY customer_id, first_name, last_name, created_at;

Table Aliases

Table aliases shorten long table names — especially valuable in JOIN-heavy queries. They are required when you join a table to itself (self-join) because two references to the same table must be distinguishable.

SQL

-- Single join: alias reduces column prefix verbosity
SELECT
  c.customer_id,
  c.first_name,
  c.email,
  o.order_id,
  o.total,
  o.created_at
FROM customers AS c
JOIN orders    AS o ON c.customer_id = o.customer_id
WHERE o.total > 100;

-- Four-table join: aliases are essential for readability
SELECT
  c.first_name,
  c.last_name,
  p.name        AS product_name,
  cat.name      AS category,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM customers      AS c
JOIN orders         AS o   ON c.customer_id  = o.customer_id
JOIN order_items    AS oi  ON o.order_id     = oi.order_id
JOIN products       AS p   ON oi.product_id  = p.product_id
JOIN categories     AS cat ON p.category_id  = cat.category_id
WHERE o.status = 'delivered';

Logical Execution Order — Where Aliases Live

MySQL processes clauses in a specific logical order. Column aliases defined in SELECT can only be referenced in clauses processed after SELECT:

FROM / JOIN
WHERE
GROUP BY
HAVING (aliases available here — MySQL extension)
SELECT — aliases are defined here
DISTINCT
ORDER BY — aliases available here
LIMIT / OFFSET

Aliases in ORDER BY (Allowed)

SQL

-- ORDER BY can reference a SELECT alias — standard SQL supports this
SELECT
  product_id,
  name,
  price * 0.9 AS discounted_price
FROM products
ORDER BY discounted_price ASC;   -- valid: ORDER BY runs after SELECT

-- Multiple computed aliases sorted in different orders
SELECT
  customer_id,
  COUNT(*)             AS order_count,
  ROUND(SUM(total),2)  AS total_spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
ORDER BY total_spent DESC, order_count DESC;

Aliases in HAVING (MySQL Extension)

SQL

-- MySQL allows HAVING to reference SELECT aliases (non-standard extension)
SELECT
  customer_id,
  COUNT(*)             AS order_count,
  ROUND(SUM(total),2)  AS total_spent
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING total_spent > 500       -- alias reference: MySQL extension
   AND order_count >= 3;       -- alias reference: MySQL extension

-- Standard SQL equivalent (repeating the expressions)
HAVING SUM(total) > 500
   AND COUNT(*) >= 3;

Aliases in WHERE — NOT Allowed

WHERE is evaluated before SELECT, so column aliases defined in SELECT do not yet exist when WHERE runs. This is the most common alias-related error in MySQL.

SQL

-- ERROR: alias 'discounted_price' is not available in WHERE
SELECT price * 0.9 AS discounted_price
FROM products
WHERE discounted_price < 20;   -- Unknown column 'discounted_price'

-- Fix 1: repeat the expression in WHERE
SELECT price * 0.9 AS discounted_price
FROM products
WHERE price * 0.9 < 20;

-- Fix 2: wrap in a subquery (alias becomes available in outer query)
SELECT *
FROM (
  SELECT product_id, name, price * 0.9 AS discounted_price
  FROM products
) AS priced
WHERE discounted_price < 20;

-- Fix 3: use a CTE (cleaner for complex expressions)
WITH discounted AS (
  SELECT product_id, name, price * 0.9 AS discounted_price
  FROM products
)
SELECT * FROM discounted WHERE discounted_price < 20;

Warning

The alias-in-WHERE restriction is part of the SQL standard. Even though MySQL is lenient with aliases in HAVING, it strictly follows the standard for WHERE. Always repeat the expression in WHERE or use a subquery/CTE.

Derived Table Aliases — Required

When you write a subquery in the FROM clause (a derived table), MySQL requires an alias. Without one, the query fails with "Every derived table must have its own alias."

SQL

-- ERROR: missing alias on derived table
SELECT * FROM (
  SELECT customer_id, COUNT(*) AS cnt FROM orders GROUP BY customer_id
);
-- Error: Every derived table must have its own alias

-- Correct: alias the derived table
SELECT *
FROM (
  SELECT customer_id, COUNT(*) AS cnt
  FROM orders
  GROUP BY customer_id
) AS order_counts   -- required alias
WHERE cnt >= 5;

-- Accessing derived table columns through the alias
SELECT
  c.first_name,
  c.email,
  oc.order_count,
  oc.total_spent
FROM customers AS c
JOIN (
  SELECT customer_id,
         COUNT(*)         AS order_count,
         ROUND(SUM(total),2) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS oc ON c.customer_id = oc.customer_id
ORDER BY oc.total_spent DESC
LIMIT 20;

Self-Referencing Alias Pitfall

You cannot reference an alias defined in one SELECT expression within another expression in the same SELECT list. Aliases are only available to clauses that execute after the full SELECT list is evaluated (ORDER BY, HAVING).

SQL

-- ERROR: cannot use 'subtotal' alias to compute 'tax' in the same SELECT
SELECT
  quantity * unit_price       AS subtotal,
  subtotal * 0.13             AS tax,     -- ERROR: Unknown column 'subtotal'
  subtotal + subtotal * 0.13  AS total    -- ERROR
FROM order_items;

-- Fix: use a subquery to expose the alias before computing from it
SELECT
  subtotal,
  ROUND(subtotal * 0.13, 2)          AS tax,
  ROUND(subtotal * 1.13, 2)          AS total_with_tax
FROM (
  SELECT
    order_item_id,
    order_id,
    quantity,
    unit_price,
    quantity * unit_price AS subtotal
  FROM order_items
) AS base;

Aliases in GROUP BY (MySQL Extension)

SQL

-- MySQL allows GROUP BY to reference SELECT aliases (non-standard)
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS sale_month,
  COUNT(*)                          AS total_orders,
  ROUND(SUM(total), 2)              AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY sale_month    -- MySQL extension: references alias from SELECT
ORDER BY sale_month;

-- Standard SQL requires repeating the expression
GROUP BY DATE_FORMAT(created_at, '%Y-%m')  -- standard version

Practical Multi-Table Report Example

SQL

-- Monthly category sales report with all aliases used consistently
SELECT
  DATE_FORMAT(o.created_at, '%Y-%m')       AS month,
  cat.name                                  AS category,
  COUNT(DISTINCT o.order_id)                AS orders_placed,
  SUM(oi.quantity)                          AS units_sold,
  ROUND(SUM(oi.quantity * oi.unit_price),2) AS revenue,
  ROUND(AVG(oi.unit_price), 2)              AS avg_unit_price
FROM orders          AS o
JOIN order_items     AS oi  ON o.order_id    = oi.order_id
JOIN products        AS p   ON oi.product_id = p.product_id
JOIN categories      AS cat ON p.category_id = cat.category_id
WHERE o.status = 'delivered'
  AND o.created_at >= '2024-01-01'
GROUP BY month, cat.category_id, cat.name
HAVING revenue > 1000
ORDER BY month, revenue DESC;

Alias Rules Quick Reference

Clause	Can reference SELECT alias?	Notes
WHERE	No	Processed before SELECT — repeat the expression or use a subquery
GROUP BY	Yes (MySQL extension)	Standard SQL requires repeating the expression
HAVING	Yes (MySQL extension)	Standard SQL requires repeating the expression
ORDER BY	Yes (standard SQL)	Aliases work in all SQL engines
JOIN ON	No	Processed as part of FROM, before SELECT
Derived table body	No	Inner query does not see outer-query aliases
Sibling SELECT expressions	No	Aliases are not available within the same SELECT list

Always write AS explicitly — it signals intent and prevents misreads
Use lowercase snake_case alias names to avoid quoting
Give every derived table (subquery in FROM) a meaningful alias
Repeat expressions in WHERE rather than relying on aliases
Use a CTE when you need to filter on a computed alias multiple times
Self-join table aliases must be different from each other and from any other table alias in the query