Aliases (AS) in SQL: table and column aliases for beginners

An alias is a short pseudonym for a table or a column in a query. SQL lets you rename anything on the fly in the output and give a table a short handle so it's easier to reference.

Two flavours:

• Column alias — renames the result column. SELECT salary AS pay → the output column header is "pay".
• Table alias — a short name for a table inside this query. FROM employees e → instead of employees.name you write e.name.

Keyword: AS. Optional.

Why aliases matter

1. Readability. e.name is shorter than employees.name. On a five-table query that's huge.
2. Mandatory when ambiguous. If two tables share a column name (say id), the database can't tell which one you mean without a prefix.
3. Clean output. If the result is LOWER(CONCAT(first_name, ' ', last_name)), the column gets an auto-generated ugly name. AS full_name fixes it.
4. Self-joins. When a table joins itself (e.g. employee-manager hierarchy), aliases are the only way.

Column alias

SELECT
  first_name AS given_name,
  last_name  AS surname,
  salary * 12 AS annual_salary
FROM employees;

What happens:

• first_name → output column "given_name".
• last_name → "surname".
• Computed salary * 12 → "annual_salary". Without an alias it would be ?column? (PostgreSQL) or salary * 12 (MySQL) — ugly.

AS is optional: salary * 12 annual_salary works the same. For readability, keep AS.

Real example with employees:

id	first_name	last_name	salary
1	Anna	Ivanova	4000
2	Boris	Petrov	2700
3	Vera	Sidorova	5000

Query:

SELECT
  first_name AS given_name,
  salary * 12 AS annual_salary
FROM employees;

Result:

given_name	annual_salary
Anna	48000
Boris	32400
Vera	60000

Table alias

SELECT e.first_name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

Each table got a one-letter name. Shorter than employees.first_name and departments.name.

Good practice: first letters of the table name. users u, orders o, products p. If two tables start with the same letter — users u, user_logs ul. Make the alias mnemonic, not t1, t2.

AS for table aliases is also optional and most styles drop it:

-- equivalent
FROM employees AS e
FROM employees e

When aliases are mandatory

In a JOIN where both tables share a column name, you have to prefix references — and a short alias is the practical way.

Example: a user has id, an order has id.

-- Doesn't work — id is ambiguous
SELECT id FROM users JOIN orders ON users.id = orders.user_id;

-- Works
SELECT u.id, o.id
FROM users u
JOIN orders o ON u.id = o.user_id;

Self-join — aliases or bust

When a table joins itself, the database can't distinguish copies without aliases. Example: employees with a manager_id referencing another employee in the same table.

employees:

id	name	manager_id
1	Anna	NULL
2	Boris	1
3	Vera	1
4	Grisha	2
5	Denis	2

Goal: pair every employee with their manager.

SELECT
  emp.name AS employee,
  mgr.name AS manager
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

Result:

employee	manager
Anna	NULL
Boris	Anna
Vera	Anna
Grisha	Boris
Denis	Boris

The table is one, but it appears twice in the query — as emp and as mgr. Without aliases, the database can't tell which name you mean.

Subqueries in FROM — alias required

A subquery in FROM must have an alias:

SELECT t.country, t.cnt
FROM (
  SELECT country, COUNT(*) AS cnt
  FROM users
  GROUP BY country
) t
WHERE t.cnt > 10;

A subquery is a "temporary table". Without a name you can't reference it. Both PostgreSQL and MySQL error out: "every derived table must have its own alias".

Aliases and execution order

Important gotcha: a column alias cannot be used in WHERE.

-- DOESN'T WORK
SELECT salary * 12 AS annual
FROM employees
WHERE annual > 1000000;

Why: SQL evaluates WHERE before SELECT. At the time WHERE runs, annual doesn't exist yet. Workarounds:

-- Option 1: repeat the expression
WHERE salary * 12 > 1000000

-- Option 2: subquery or CTE
SELECT * FROM (
  SELECT salary * 12 AS annual FROM employees
) t WHERE t.annual > 1000000;

In ORDER BY aliases do work — ORDER BY runs after SELECT:

SELECT salary * 12 AS annual
FROM employees
ORDER BY annual DESC;  -- OK

Bigger example: a social network

users:

id	name
1	Anna
2	Boris

posts:

id	author_id	title	likes
10	1	My first post	12
11	1	Cute cats	87
12	2	Borscht recipe	45

comments:

id	post_id	author_id	text
20	10	2	Awesome!
21	11	1	Thanks for the likes!
22	11	2	Cool :)

Goal: show "post author — post title — comment author — comment text".

SELECT
  pa.name AS post_author,
  p.title AS post_title,
  ca.name AS comment_author,
  c.text  AS comment_text
FROM posts p
JOIN users pa   ON pa.id = p.author_id
JOIN comments c ON c.post_id = p.id
JOIN users ca   ON ca.id = c.author_id
ORDER BY p.id, c.id;

Result:

post_author	post_title	comment_author	comment_text
Anna	My first post	Boris	Awesome!
Anna	Cute cats	Anna	Thanks for the likes!
Anna	Cute cats	Boris	Cool :)

Mandatory aliases here:

• users shows up twice (as post author and as comment author) → need different aliases pa and ca.
• Column name exists in both pa and ca → ambiguous without a prefix.
• Column aliases (AS post_author, AS comment_author) make the output readable.

This query simply isn't writable without aliases.

Common beginner mistakes

1. Using a column alias in WHERE. Doesn't work (see execution order). ORDER BY — yes, WHERE — no.

2. Forgetting the alias on a subquery in FROM. "Every derived table must have its own alias" error.

3. One-letter aliases everywhere. With five tables a, b, c, d, e, a week later you can't remember which is which. Use mnemonic short names: users u, orders o, payments p.

4. Quoted or spaced aliases. Want spaces or a keyword in your alias? PostgreSQL needs double quotes: AS "My Column". Just don't — use snake_case.

5. Defining a table alias and still writing the full name. Pointless: FROM employees e ... WHERE employees.id = 1 — alias is set but not used. You'll get "invalid reference to FROM-clause entry for table 'employees'".

6. Alias clashes with a reserved word. AS user, AS order may cause issues in some DBs. Avoid or quote.

Quick recap

• An alias is a pseudonym for a table or column, optionally introduced with AS.
• AS is optional in both contexts.
• Column aliases are visible in SELECT, ORDER BY, GROUP BY, not in WHERE or HAVING (execution order).
• Table aliases are mandatory when JOINing tables with shared column names.
• Self-joins are impossible without aliases.
• A subquery in FROM must have an alias.

Try it yourself

On the example tables:

1. List posts with author and number of comments. Use aliases for brevity.
2. Self-join on employees: pair every manager with the employees who report to them.
3. Subquery in FROM: compute the average rating across posts that have more than 50 likes. The inner query filters, the outer one averages.

Aliases are a technique that dramatically improves readability. Make it a habit to give tables short names in JOINs.