SQL Aliases Explained with Examples

Put two tables in one query and every column has to be written with its table name in front of it. Add a total to the select list, and the engine picks the heading over that column for you. AS solves both: it gives a column in the result the heading you want, and it gives a table a short name that stands in for it through the rest of the statement. The name exists while that one query runs and changes nothing in the database.

All the examples use these three tables:

CREATE TABLE departments (
    DeptID   INT PRIMARY KEY,
    DeptName VARCHAR(30)
);

CREATE TABLE employees (
    EmpID     INT PRIMARY KEY,
    FirstName VARCHAR(30),
    LastName  VARCHAR(30),
    DeptID    INT REFERENCES departments(DeptID),
    ManagerID INT REFERENCES employees(EmpID)
);

CREATE TABLE orders (
    OrderID INT PRIMARY KEY,
    EmpID   INT REFERENCES employees(EmpID),
    Product VARCHAR(30),
    Amount  DECIMAL(8,2)
);

INSERT INTO departments VALUES
    (101, 'HR'),
    (102, 'Finance');

INSERT INTO employees VALUES
    (1, 'Alice', 'Moore', 101, NULL),
    (2, 'Bob',   'Lane',  102, 1),
    (3, 'Carol', 'Diaz',  102, 1);

INSERT INTO orders VALUES
    (1, 2, 'Laptop',  900.00),
    (2, 3, 'Book',     50.00),
    (3, 2, 'Monitor', 150.00);

What an SQL alias is

There are two of them, and they are written in different clauses. A column alias goes in the SELECT list, after the column or the expression it renames:

SELECT column_name AS alias_name
FROM table_name;

A table alias goes in the FROM clause, after the table it renames:

SELECT column_name
FROM table_name AS alias_name;

Four situations are where aliases stop being decoration. A query over several tables needs table aliases, or every column reference carries a long table name. An aggregate needs a column alias, because the engine names that column for you when you leave it out. A subquery in the FROM clause needs a table alias, since the outer query has no other way to refer to it. And a self-join is impossible without them, because the two copies of the table need different names. Each of those has a section below.

Column aliases

AS renames the column in the result grid. The column in the table keeps the name it was created with:

SELECT FirstName AS First, LastName AS Last
FROM employees;

Nothing was written to the database, and a second query against employees still finds FirstName. The rename lasts exactly as long as the result set.

Table aliases

A table alias is the short name you then use to qualify columns:

SELECT e.EmpID, e.FirstName
FROM employees AS e;

Once a table has an alias, the alias replaces the table name for the rest of the statement: after FROM employees AS e, writing employees.FirstName is an error, because that item is now called e[4]. The payoff arrives with the second table, where every column has to say which table it came from:

SELECT e.FirstName, d.DeptName
FROM employees AS e
INNER JOIN departments AS d ON e.DeptID = d.DeptID;

Aliases with aggregate functions

An aggregate function produces a column that no table declares, so without an alias the heading over it is one the engine picks. PostgreSQL uses the function name where it can, and falls back on a generated name such as ?column? where it cannot[4]:

SELECT COUNT(*) AS OrderCount, SUM(Amount) AS TotalSales
FROM orders;

Those two headings are the names your application reads the values back by, so an aggregate left without an alias leaves the calling code depending on whatever the engine decided to call that column.

Aliases in subqueries

A subquery in the FROM clause produces a table that exists only inside the statement, and the outer query needs a name for it:

SELECT SUM(o.Amount) AS TotalOverFifty
FROM (SELECT Amount FROM orders WHERE Amount > 50) AS o;

The inner query keeps the laptop and the monitor and drops the 50.00 book, and the outer query adds up what is left. Write the AS o even when your engine lets you skip it: the SQL standard requires an alias on a subquery in FROM, and PostgreSQL documents omitting it as its own departure from the standard[4].

Aliases in JOINs and self-joins

In an ordinary join, aliases shorten the query. Two letters carry the same meaning as two table names:

SELECT e.FirstName, o.Product, o.Amount
FROM employees AS e
JOIN orders AS o ON e.EmpID = o.EmpID
ORDER BY o.OrderID;

In a self-join, aliases are the only thing that makes the query possible. A table joined to itself appears twice in the FROM clause, and the engine has no way to tell the two copies apart until you name them. That is also the sharpest case of a column name that is not unique: both copies have a column called FirstName, and only the alias in front of it says which one you mean.

SELECT e.FirstName AS Employee, m.FirstName AS Manager
FROM employees AS e
INNER JOIN employees AS m ON e.ManagerID = m.EmpID;

Alice is absent from the result because her ManagerID is NULL, and NULL matches nothing in a join condition. The SQL joins article covers the outer join that keeps her.

Quoting aliases with spaces, case or reserved words

An alias that contains a space, needs a specific capitalization, or reuses a reserved word has to be written as a delimited identifier. The delimiter is the one part of alias syntax that is not the same in every engine.

PostgreSQL and the SQL standard use double quotes. A delimited identifier is always an identifier and never a key word, so "select" names a column called select. Quoting also makes the alias case-sensitive, whereas unquoted names are folded to lower case[1].

MySQL quotes identifiers with backticks. Double quotation marks work as identifier quotes only when the ANSI_QUOTES SQL mode is enabled[2]; without it, MySQL reads a double-quoted alias as a string literal.

SQL Server accepts brackets and double quotes. Brackets always work, regardless of session settings. Double-quote delimiters require SET QUOTED_IDENTIFIER ON[3], which is the default for most connections.

-- PostgreSQL, and SQL Server with QUOTED_IDENTIFIER ON
SELECT FirstName AS "First Name"
FROM employees;

-- MySQL, default settings
SELECT FirstName AS `First Name`
FROM employees;

-- SQL Server, valid whatever the session settings
SELECT FirstName AS [First Name]
FROM employees;

Quote only when you have to. An alias written in plain lower-case letters, digits and underscores needs no delimiter on any engine, and it is the only form that survives a copy from one database to the next unchanged.

Where an alias can be referenced in SELECT, WHERE, GROUP BY, HAVING and ORDER BY

A table alias and a column alias do not have the same reach. A table alias is introduced in the FROM clause and then replaces the table name for the rest of the statement, so it is legal in SELECT, WHERE, GROUP BY, HAVING and ORDER BY alike.

A column alias is different. It is created in the SELECT list, and the SELECT list is evaluated after the rows have been filtered and after a GROUP BY has grouped them. All three engines reject this statement:

SELECT FirstName AS First, LastName AS Last
FROM employees
WHERE First = 'Alice';

First has no value yet while WHERE is deciding which rows to keep, which is the reason the MySQL manual gives for the restriction[5]. Write the expression out again and the same query runs:

SELECT FirstName AS First, LastName AS Last
FROM employees
WHERE FirstName = 'Alice';

Past WHERE, the engines diverge.

PostgreSQL treats an alias as an output column name, and an output column name can be used in ORDER BY and GROUP BY but not in WHERE or HAVING, where the expression has to be written out again[4].

MySQL is the most permissive of the three: its manual says you can use the alias in GROUP BY, ORDER BY, or HAVING clauses[5] to refer to the column, and rules it out only in WHERE.

SQL Server is the strictest: a column alias can be used in an ORDER BY clause, but not in a WHERE, GROUP BY, or HAVING clause[6].

ORDER BY is the row all three agree on, so an alias defined in the SELECT list can always be reused there:

SELECT FirstName AS First, LastName AS Last
FROM employees
ORDER BY Last;

Repeating the expression, the way the WHERE FirstName query above does, is the shortest workaround on all three engines. Where the expression is long enough that repeating it hurts, move it into a subquery or a common table expression and filter on the alias in the outer query. To filter on an aggregate itself, use a HAVING clause.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Aliases in the DbSchema Query Builder and SQL Editor

Aliases only pay off if they are consistent, and consistency is hard to hold in your head once a query reaches four tables. DbSchema's Query Builder writes the statement for you: pick the tables on the canvas, tick the columns, and the preview pane shows the generated SELECT with the table aliases it assigned, beside the grid of rows the query returns.

For a statement you wrote by hand, run it in the SQL Editor. The result grid takes its column headings from your aliases, so a mistyped or duplicated alias appears in the output instead of staying buried in the query text. Neither one writes to the database, and a Query Builder you keep is stored in the design model file, so it reopens with the diagram. The SQL Editor is in the free Community Edition; the Query Builder is a Pro feature, and it is the one that saves the typing on a query with four tables in it.

Common mistakes

Leaving out AS is legal in a column alias, and that is exactly what makes a missing comma dangerous. The engine reads the second column name as an alias for the first, and the query runs:

SELECT FirstName LastName
FROM employees;

One column came back, headed LastName, holding first names. Writing AS every time turns that silent result into a syntax error you see immediately.

The second mistake is mixing the table name and its alias in one statement, which fails for the reason the table alias section gives: the alias has replaced the name. The third is reusing one alias for two items in the same FROM clause, which leaves every qualified column ambiguous and gets the statement rejected.

Practice questions

Four queries to write against the three tables above:

1. Return every order with its product and its amount, with the amount column headed Total.
2. Return each employee's first name next to their department name, giving each table a one-letter alias.
3. Group orders by EmpID to count the orders per employee, alias that count as OrderCount, and sort on the alias.
4. Return each employee's first name next to their manager's first name, aliasing employees twice.

The third one is the interesting one, because the alias in ORDER BY is the reference every engine in the table above accepts.

The SQL Editor that turns those aliases into grid headings is part of the free Community Edition. The Query Builder that writes the aliased SELECT for you is part of Pro. Get DbSchema from https://dbschema.com/download.html, point it at a database you already have, and paste in the longest query you currently avoid reading.

FAQs

Can I use an SQL alias outside the query it is defined in?

Not directly, but a view carries one: create a view whose SELECT list holds the alias, and that name becomes a column name of the view for every query that reads it.

Can I use numerical values as aliases?

On PostgreSQL[1] and SQL Server[3] an identifier cannot begin with a digit, so an alias that does has to be quoted every time you use it. MySQL allows an unquoted identifier to begin with a digit, and requires quoting only when the alias is nothing but digits[2]. A bare number in ORDER BY is read as the position of an output column on every engine, not as an alias.

Can two tables in the same query have the same alias?

No, every item in one FROM clause needs a distinct alias, which is precisely why a self-join names the same table twice with two different letters.

Sources

1. PostgreSQL Documentation: Lexical Structure
2. MySQL 8.4 Reference Manual: Schema Object Names
3. SQL Server: Database Identifiers
4. PostgreSQL Documentation: SELECT
5. MySQL 8.4 Reference Manual: Problems with Column Aliases
6. SQL Server: SELECT Clause (Transact-SQL)