Column and Table Aliases in SQL

A table alias renames the relation for the duration of the query, which matters most when the same table appears twice. A column alias renames the output column of the projection, which matters when the expression is not a plain column or when the raw name is ambiguous.

CREATE TABLE staff (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  manager_id INTEGER
);

INSERT INTO staff (id, name, manager_id) VALUES
  (1, 'Ada Lovelace', NULL),
  (2, 'Grace Hopper', 1),
  (3, 'Alan Turing',  1),
  (4, 'Katherine Johnson', 2);

-- The table alias makes it clear which copy of id is which.
SELECT e.name AS employee, m.name AS manager
FROM staff AS e
JOIN staff AS m ON m.id = e.manager_id
ORDER BY e.name;

Three rows come back: Alan Turing managed by Ada Lovelace, Grace Hopper managed by Ada Lovelace, and Katherine Johnson managed by Grace Hopper. Ada Lovelace herself is absent because her manager_id is NULL and an inner join requires a match — the query is asking for employees who have a manager, and she does not.

A table alias is not optional in a self join. With two copies of staff in the FROM clause, an unqualified id is ambiguous and the engine rejects it. The aliases e and m are what make e.manager_id = m.id meaningful.

The visibility rules are the part that trips people up, and they follow the same stage order as the rest of the query. A column alias can be referenced in ORDER BY and in GROUP BY, because both run after the projection. It cannot be referenced in WHERE, because WHERE runs before the projection exists. A table alias, by contrast, is available everywhere the table is, including the WHERE clause.

Some teams skip the AS keyword because it is optional in most engines. Write it anyway: it is clearer, and it avoids the case where an alias starts with a digit or collides with a reserved word and the engine guesses wrong.

The trick with long table names is that a table alias shortens the expression, but it also forces you to re-qualify every column reference. Here is the same join before and after.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_lines (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (id, name, unit_price) VALUES
  (1, 'Mechanical Keyboard', 79.50),
  (2, 'USB-C Hub', 32.00);

INSERT INTO order_lines (id, order_id, product_id, quantity, unit_price) VALUES
  (1, 500, 1, 2, 79.50),
  (2, 500, 2, 3, 32.00);

-- Aliased: shorter to read, and the output columns are named for the reader.
SELECT p.name AS product,
       o.order_id AS order_id,
       o.quantity AS units,
       ROUND(o.quantity * o.unit_price, 2) AS line_total
FROM order_lines AS o
JOIN products AS p ON p.id = o.product_id
ORDER BY o.order_id, p.name;

Two rows: Mechanical Keyboard with 2 units totalling 159.00, and USB-C Hub with 3 units totalling 96.00. Note that the line total uses o.unit_price, the price captured on the order line, not p.unit_price, the current catalogue price. That distinction matters whenever the catalogue price changes after an order is placed, and it is worth naming explicitly in the alias so the next reader does not "fix" it.

Before aliasing, the same query would have needed order_lines.unit_price and products.unit_price — and because both tables have a column called unit_price, at least one of them had to be qualified anyway. The alias removes the ambiguity as a side effect.

Two habits for review. Alias the table, then qualify every column with that alias, even where it is unambiguous; consistency is worth more than brevity. And give the output columns names a BI tool can use — line_total travels better than ROUND(o.quantity * o.unit_price, 2).