SQL Aliases (AS) – Renaming Columns and Tables

Column Aliases

A column alias renames the column header in the result set. It has no effect on the underlying data — it only changes what label appears at the top of that column when the query result is displayed.

SQL

-- Without alias: the column header is 'salary * 12' (ugly)
SELECT first_name, salary * 12
FROM employees;

-- With alias: the column header is 'annual_salary' (readable)
SELECT first_name, salary * 12 AS annual_salary
FROM employees;

-- AS is optional — this works too (but AS is recommended for clarity)
SELECT first_name, salary * 12 annual_salary
FROM employees;

-- Rename an existing column in the output
SELECT first_name AS name, department AS dept
FROM employees;

▶ Result

name     dept     annual_salary
Alice    Engineering    1020000
Bob      Marketing      744000

Multi-word Aliases

If your alias contains spaces, wrap it in double quotes (standard SQL and PostgreSQL) or backticks (MySQL):

SQL

-- Standard SQL / PostgreSQL: use double quotes for multi-word aliases
SELECT salary AS "Monthly Pay", salary * 12 AS "Annual Salary"
FROM employees;

-- MySQL: backticks also work
SELECT salary AS `Monthly Pay`, salary * 12 AS `Annual Salary`
FROM employees;

-- Best practice: use underscore_names to avoid quoting entirely
SELECT salary AS monthly_pay, salary * 12 AS annual_salary
FROM employees;

💡

Use snake_case aliases — avoid spaces

Multi-word aliases with spaces require quoting every time you reference them, both in SQL and in your application code. Stick to snake_case (underscores) for aliases to keep queries readable without any quoting. Reserve quoted aliases for reports and dashboards where the column header is shown to end users.

Table Aliases

A table alias gives a table a shorter name within the query. This is purely for convenience in simple queries, but becomes essential in joins where you need to prefix column names with the table name to avoid ambiguity.

SQL

-- Without table alias (verbose)
SELECT employees.first_name, employees.salary
FROM employees;

-- With table alias — 'e' is the alias for employees
SELECT e.first_name, e.salary
FROM employees e;

-- AS is optional for table aliases too
SELECT e.first_name, e.salary
FROM employees AS e;

-- Useful for long table names
SELECT o.id, o.total, o.order_date
FROM customer_orders AS o
WHERE o.total > 100;

Aliases in Joins – Resolving Ambiguity

Table aliases are not just convenient in joins — they are often required. When two tables have a column with the same name (e.g. both have an id column), the database cannot tell which table you mean without a table prefix. Aliases make this explicit and keep JOIN queries readable.

SQL

-- Without aliases: ambiguous — which 'id' is which?
SELECT id, name, total
FROM orders
JOIN customers ON orders.customer_id = customers.id;
-- Error: column 'id' is ambiguous (both tables have it)

-- With aliases: clear and unambiguous
SELECT o.id   AS order_id,
       c.name AS customer_name,
       o.total
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.id;

-- Three-table join — aliases keep it manageable
SELECT
    o.id         AS order_id,
    c.name       AS customer_name,
    p.name       AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders       AS o
JOIN customers    AS c  ON o.customer_id  = c.id
JOIN order_items  AS oi ON oi.order_id    = o.id
JOIN products     AS p  ON oi.product_id  = p.id
WHERE o.total > 500;

ℹ️

Convention: single-letter or short aliases for tables

In simple queries, single-letter aliases (e for employees, o for orders, c for customers) are widely used. For complex queries with many joins, slightly longer abbreviations (emp, ord, cust) can be clearer. Avoid overly long aliases — they defeat the purpose of shortening the code.

Alias Scope Rules

Column aliases defined in SELECT can only be used in certain clauses — not everywhere. Understanding this prevents a common category of SQL errors:

Clause	Can use column alias?	Reason
WHERE	No	WHERE runs before SELECT — alias not yet defined
GROUP BY	Sometimes (MySQL yes, PostgreSQL no)	Varies by database dialect
HAVING	Sometimes (MySQL yes, PostgreSQL no)	Varies by database dialect
ORDER BY	Yes — all databases	ORDER BY runs after SELECT

SQL

-- WRONG: cannot use alias in WHERE (runs before SELECT)
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 900000;   -- Error in PostgreSQL / SQL Server

-- CORRECT: repeat the expression in WHERE
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 900000;

-- CORRECT: use a subquery or CTE to reference alias in WHERE
SELECT *
FROM (
    SELECT first_name, salary * 12 AS annual_salary
    FROM employees
) AS derived
WHERE annual_salary > 900000;

-- CORRECT: alias IS usable in ORDER BY (all databases)
SELECT first_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;  -- works everywhere

Aliases: Renaming Columns and Tables On the Fly

An alias gives a column or table a temporary name for the duration of a query, using AS (optional for tables). It cleans up output and makes multi-table queries readable.

SELECT price * quantity AS total,          -- column alias → header "total"
       c.name AS customer
FROM orders o                              -- table alias (AS optional)
JOIN customers c ON c.id = o.customer_id;  -- short aliases keep it readable

Alias on	Purpose
column	rename computed/ugly columns for output
table	shorten names, enable self-joins

Table aliases aren't just for looks — sometimes they're required: a self-join (joining a table to itself) is impossible without two different aliases to tell the copies apart. The scope gotcha: a column alias defined in SELECT can be used in ORDER BY and GROUP BY (which run after SELECT) but not in WHERE (which runs before) — WHERE total > 100 fails because total doesn't exist yet at that stage; repeat the expression instead. Quoting: if an alias contains spaces or special characters, wrap it in double quotes: AS "Total Sales". Keep table aliases short but meaningful (c for customers) so joins stay legible.

🏋️ Practical Exercise

Alias a column with AS.
Alias a table for shorter references.
Use a column alias in the output.
Alias an aggregate result.
Use a table alias in a join.

🔥 Challenge Exercise

Write a query that joins two tables using short table aliases and renames an aggregate column with AS for a readable result. Explain the difference between column and table aliases and where alias names can (and cannot) be referenced.

📋 Summary

Column alias: SELECT expression AS alias_name — renames the column header in the output. AS is optional but recommended.
Use double quotes (PostgreSQL) or backticks (MySQL) for multi-word aliases. Prefer snake_case to avoid quoting.
Table alias: FROM table_name AS t — shortens the table reference within the query.
Table aliases are essential in joins to resolve ambiguous column names (e.g. both tables have an id column).
Column aliases are available in ORDER BY but not in WHERE (WHERE runs before SELECT). Repeat the expression in WHERE, or use a subquery.

Interview Questions

What is a column alias?
What is a table alias?
Is the AS keyword required?
Can you use a column alias in the WHERE clause? Why or why not?
Where can column aliases be referenced (SELECT, ORDER BY, GROUP BY)?

FAQ

Is AS required for aliases? +

No — AS is optional in both column and table aliases. SELECT salary * 12 annual_salary and SELECT salary * 12 AS annual_salary are identical. However, always using AS is strongly recommended for readability. It makes the alias explicit and prevents confusion between a missing comma and an accidental alias (e.g. SELECT first_name last_name selects only first_name and aliases it last_name, rather than selecting two columns — a subtle bug).

Can I reuse an alias in the same SELECT list? +

No — you cannot reference a column alias defined earlier in the same SELECT list. Each expression in SELECT is evaluated independently. For example, SELECT salary * 12 AS annual, annual / 12 AS monthly will fail because annual is not yet a known column when annual / 12 is evaluated. Repeat the expression: SELECT salary * 12 AS annual, salary AS monthly.

Do aliases affect the actual table or column names? +

No — aliases are temporary and exist only within the scope of the single query. The underlying table and column names are completely unchanged. An alias defined in one query has no effect on any other query, and it is never stored in the database. It is purely a presentation-layer label applied to the result set.

Can I use a column alias in GROUP BY? +

It depends on the database. MySQL and MariaDB allow using column aliases in GROUP BY. PostgreSQL and SQL Server do not — they require you to repeat the expression. Standard SQL (and the safest portable approach) is to repeat the expression: SELECT YEAR(order_date) AS year, COUNT(*) FROM orders GROUP BY YEAR(order_date). Alternatively, wrap the query in a subquery and GROUP BY the alias in the outer query.