SQL Aliases

An alias is a temporary, query-scoped name you give to a column, an expression, or a table so the result set — or the query itself — is easier to read and write. Aliases don’t change anything in the underlying database; they only affect how a column is labeled in the output, or how a table is referred to within that single query. You’ll use them constantly: to give a calculated column a friendly name, to shorten long table names in multi-table joins, and to make aggregate results self-documenting instead of showing raw expressions like COUNT(*) as a column header.

Overview: What Aliases Are and How They Work

SQL has two kinds of aliases. A column alias renames an output column — the actual column, or the result of an expression like price * quantity or COUNT(*) — for the purposes of display and, in some cases, later clauses in the same query. A table alias gives a table (or a subquery, or the result of a join) a short, temporary name that you then use everywhere else in the query instead of the full table name.

It’s important to understand that a column alias is a label attached at the very last moment the query engine builds the output row — it does not create a new column in the table, it doesn’t persist between queries, and other sessions or later statements never see it. A table alias, by contrast, actually changes how the engine resolves column references while it plans and executes the query: once you write FROM employees AS e, the original name employees is no longer available elsewhere in that query (in most databases) — you must use e.

The keyword AS is optional in SQLite, MySQL, and PostgreSQL — SELECT salary total_pay FROM employees works exactly like SELECT salary AS total_pay FROM employees. SQL Server also supports AS but additionally allows total_pay = salary. Despite AS being optional, writing it explicitly is strongly recommended: it makes the alias visually obvious and prevents a very common bug where a missing comma between two column names accidentally turns the second column into an unintended alias for the first.

Syntax

-- Column alias
SELECT column_name AS alias_name
FROM table_name;

-- Table alias
SELECT alias_name.column_name
FROM table_name AS alias_name;

-- Alias containing spaces or a reserved word must be quoted
SELECT column_name AS "Alias With Spaces"
FROM table_name;

Part	Meaning
AS	Optional keyword introducing the alias; recommended for readability
alias_name	The new name; must be quoted (with double quotes in standard SQL/SQLite, backticks in MySQL, or square brackets in SQL Server) if it contains spaces or is a reserved keyword
Column alias scope	Visible in ORDER BY (and, as a SQLite/MySQL/PostgreSQL extension, in GROUP BY); not visible in WHERE or HAVING
Table alias scope	Replaces the original table name for the rest of that query — used to qualify columns and required for self-joins

Examples

Example 1: Basic column aliases

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  salary REAL
);

INSERT INTO employees (id, first_name, last_name, salary) VALUES
  (1, 'Ana', 'Silva', 72000),
  (2, 'Marco', 'Lee', 85000),
  (3, 'Priya', 'Nair', 67000);

SELECT first_name AS "First Name",
       last_name AS "Last Name",
       salary AS annual_salary,
       salary / 12 AS monthly_salary
FROM employees;

Result:

First Name	Last Name	annual_salary	monthly_salary
Ana	Silva	72000.0	6000.0
Marco	Lee	85000.0	≈7083.33
Priya	Nair	67000.0	≈5583.33

Notice that first_name needed no quoting because it has no spaces, but "First Name" does. The expression salary / 12 has no name of its own, so without an alias its header would just be the literal expression text — monthly_salary makes it self-explanatory.

Example 2: Table aliases in a join

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name TEXT
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL
);

INSERT INTO customers (customer_id, customer_name) VALUES
  (1, 'Acme Corp'),
  (2, 'Globex');

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 250.00),
  (102, 1, 99.50),
  (103, 2, 430.00);

SELECT c.customer_name AS customer,
       o.order_id AS order_id,
       o.amount AS order_amount
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Result:

customer	order_id	order_amount
Acme Corp	101	250.0
Acme Corp	102	99.5
Globex	103	430.0

Here c and o are table aliases. Without them you’d have to write customers.customer_name and orders.order_id everywhere — tedious, and mandatory anyway once a join needs to disambiguate identically-named columns like customer_id in both tables.

Example 3: Aliasing aggregate results

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT,
  price REAL,
  category TEXT
);

INSERT INTO products (product_id, product_name, price, category) VALUES
  (1, 'Widget', 9.99, 'Hardware'),
  (2, 'Gadget', 19.99, 'Electronics'),
  (3, 'Gizmo', 14.99, 'Hardware'),
  (4, 'Cable', 4.99, 'Hardware');

SELECT category AS product_category,
       COUNT(*) AS num_products,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

Result:

product_category	num_products	avg_price
Electronics	1	19.99
Hardware	3	9.99

Aggregate expressions like COUNT(*) and AVG(price) are unreadable as column headers without an alias. This example also shows that ORDER BY avg_price works — SQLite, MySQL, and PostgreSQL all let ORDER BY reference a SELECT alias, because sorting happens after column names are assigned.

How It Works Step by Step (Under the Hood)

The reason aliases behave the way they do comes down to the logical order in which a SQL engine processes a query — which is different from the order you type the clauses in:

1. FROM / JOIN — tables are identified and, if aliased, given their temporary names first. From this point on, only the alias exists in the engine’s view of the query.
2. WHERE — rows are filtered using the original column expressions. Column aliases from SELECT do not exist yet, because SELECT hasn’t run.
3. GROUP BY — remaining rows are collected into groups.
4. HAVING — groups are filtered, again generally before aliases are assigned in the strict standard, though SQLite is lenient here as an extension.
5. SELECT — the output columns, and their aliases, are finally computed and named.
6. ORDER BY — sorting happens last (before LIMIT), by which point the aliases from step 5 exist, so they can be referenced.
7. LIMIT / OFFSET — the row count is trimmed.

This single fact — that aliases are born in step 5, but WHERE runs in step 2 and HAVING in step 4 — explains almost every alias-related error you’ll hit, and it’s why ORDER BY is the one place aliases are always safe to use.

Common Mistakes

Mistake 1: Referencing a column alias in WHERE

CREATE TABLE employees (id INTEGER, name TEXT, salary REAL);
INSERT INTO employees VALUES (1,'Ana',72000),(2,'Marco',85000),(3,'Priya',65000);

SELECT salary AS annual_salary
FROM employees
WHERE annual_salary > 70000;

This fails with an error like no such column: annual_salary. As shown in the step-by-step order above, WHERE is evaluated before SELECT assigns the alias, so annual_salary simply doesn’t exist yet at that point in execution. The fix is to repeat the original expression in WHERE (or filter in an outer query wrapped around a subquery/CTE that already has the alias):

SELECT salary AS annual_salary
FROM employees
WHERE salary > 70000;

This returns Ana (72000) and Marco (85000); Priya is excluded because 65000 is not greater than 70000.

Mistake 2: Using a reserved word as an unquoted alias

CREATE TABLE employees (id INTEGER, department TEXT, salary REAL);
INSERT INTO employees VALUES (1,'Engineering',72000),(2,'Engineering',85000),(3,'Sales',60000);

SELECT department, COUNT(*) AS order
FROM employees
GROUP BY department;

This raises a syntax error, because ORDER is a reserved SQL keyword (it’s what introduces ORDER BY) and cannot be used unquoted as an identifier. The safest fix is simply to avoid reserved words in alias names entirely:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department;

This returns Engineering with a count of 2 and Sales with a count of 1. If you truly need a reserved word as an alias, quoting it (AS "order" in standard SQL/SQLite, or AS \`order\` in MySQL) works, but a descriptive non-reserved name is almost always clearer anyway.

Best Practices

• Always write AS explicitly, even though it’s optional — it prevents accidental aliases caused by a missing comma and makes queries easier for teammates to scan.
• Use descriptive, snake_case or readable aliases for calculated and aggregate columns (avg_price, not a1) so the output is self-documenting for anyone reading a report or exported CSV.
• Prefer short, meaningful table aliases (e for employees, o for orders) in joins, and use them consistently to qualify every column once you have more than one table — it removes ambiguity and lets the query engine resolve columns unambiguously.
• Avoid SQL reserved words as alias names; if you must use one, quote it consistently with your database’s quoting style.
• Remember aliases don’t exist in WHERE or (in strict-standard databases) HAVING — repeat the expression there instead of the alias.
• Always give self-joins distinct table aliases — a self-join is impossible to write without them, since the same table name can’t appear twice unqualified.
• Don’t rely on alias support in GROUP BY/HAVING if you need your SQL to be portable — it’s a common but non-standard extension; check the target database if portability matters.

Practice Exercises

1. Using an employees table with columns id, name, and salary, write a query that selects name and aliases salary * 0.1 as bonus.
2. Write a query that joins a customers table to itself (using two different table aliases) to find pairs of customers who share the same country. Hint: alias the table twice, e.g. AS c1 and AS c2, and join on matching country with c1.customer_id < c2.customer_id to avoid duplicate pairs.
3. Given an orders table with customer_id and amount, write a query that groups by customer_id, aliases COUNT(*) as order_count and SUM(amount) as total_spent, and sorts the result by total_spent descending using the alias.

Summary

• A column alias renames an output column or expression for display purposes only; it doesn’t modify the underlying table.
• A table alias replaces a table’s name for the rest of the query, and is required for self-joins.
• AS is optional but should always be written for clarity.
• Aliases with spaces or reserved words must be quoted.
• Because of SQL’s logical processing order, aliases are unavailable in WHERE and standard HAVING, but are always available in ORDER BY, since sorting happens after SELECT assigns the names.
• Many databases (including SQLite) extend the standard by also allowing aliases in GROUP BY — useful, but not universally portable.