SQL Aliases Explained with Examples

An SQL alias is a temporary name you give a column or a table for the duration of one query. You write it with the AS keyword: SELECT first_name AS First renames the output column, and FROM Customers AS c renames the table for the rest of the statement. The alias lives only inside that query and changes nothing in the database itself.

What is an SQL alias?

SQL Aliases are temporary names assigned to a table or a column for the purpose of a particular SQL query. They are useful for providing a shorthand method of referencing columns or tables, especially if their names are long or cumbersome.

Syntax:

For a column:

SELECT column_name AS alias_name
FROM table_name;

For a table:

SELECT column_name
FROM table_name AS alias_name;

Benefits of SQL Aliases

1. Readability: Makes SQL statements more readable especially when using joins and multiple tables.
2. Efficiency: Reduces the amount of text you need to write, especially with complex queries.
3. Avoiding Conflicts: Helpful when two tables have columns with the same names.

When to use an alias

1. Complex Queries: When working with multi-table queries or complex SQL queries.
2. Aggregated Data: When using aggregate functions like SUM, COUNT, etc.
3. Subqueries: With subqueries in the FROM clause.
4. Self-Joins: When a table is joined to itself.

Column aliases

Column aliases are used to rename a column's header in the result set.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

Example:

Suppose we have a table named Students:

Query:

SELECT FirstName AS First, LastName AS Last
FROM Students;

Result:

Here, "FirstName" is displayed as First and LastName as Last.

Table aliases

Table aliases are used to provide a table with a temporary name, which can make queries shorter.

Syntax:

SELECT column_name
FROM table_name AS alias_name;

Example:

Suppose we have a table named Orders:

Query:

SELECT o.OrderID, o.ProductName
FROM Orders AS o;

Result:

Here, the table Orders is referenced as o in the query.

Aliases are particularly beneficial when dealing with complex queries involving multiple tables.

Example:

Given two tables Employees and Departments.

Employees:

Departments:

Query:

SELECT e.Name, d.DeptName
FROM Employees AS e
INNER JOIN Departments AS d ON e.DeptID = d.DeptID;

Result:

Here, we join the two tables using their respective aliases.

Aliases with aggregate functions

When using aggregate functions, aliases can provide a clearer column name for the result rather than a system-generated default.

Example:

Given a table Sales:

Query:

SELECT SUM(Amount) AS TotalSales
FROM Sales;

Result:

Here, the sum of all sales is displayed under the column TotalSales.

Aliases in subqueries

Aliases are crucial when using subqueries, especially in the FROM clause, as the outer query needs a name to reference the derived table.

Example:

Suppose we have a table Products:

Query:

SELECT AVG(s.Price) AS AveragePrice
FROM (SELECT Price FROM Products WHERE Price > 50) AS s;

Result:

The sub-query fetches products with a price greater than 50, and the outer query calculates the average of those prices.

Aliases in JOINs and self-joins

Using aliases makes JOIN operations much more concise and readable. In self-joins, where a table is joined with itself, aliases are absolutely essential to distinguish between the two instances of the table.

Example:

Given a table Employees:

Query:

SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees AS e1
INNER JOIN Employees AS e2 ON e1.ManagerID = e2.EmpID;

Result:

Here, Bob's manager is Alice.

Using aliases can make JOIN operations more readable.

Example:

Given two tables Users and Orders.

Users:

Orders:

Query:

SELECT u.UserName, o.Product
FROM Users AS u
JOIN Orders AS o ON u.UserID = o.UserID;

Result:

Here, we fetched products ordered by each user.

Aliases for non-unique column names

When two tables have columns with the same name, aliases can help differentiate them.

Example:

Given two tables Teachers and Students, both having a column "Name".

Using the alias:

SELECT t.Name AS TeacherName, s.Name AS StudentName
FROM Teachers AS t
INNER JOIN Students AS s ON t.ClassID = s.ClassID;

This way, we can clearly differentiate between teacher names and student names in the result set.

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 Students;

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

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

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: SELECT, WHERE, GROUP BY, HAVING, 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: given FROM foo AS f, the remainder of the SELECT must refer to that item as f, not foo[4]. A table alias is therefore 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. That is why a column alias in WHERE fails on every engine: the value it names is not known while WHERE is still deciding which rows to keep[5]. Past WHERE, the engines diverge.

PostgreSQL treats an alias as an output column name. Output columns can be referenced by name or ordinal number in the GROUP BY clause[4], and an ORDER BY expression can be the name or ordinal number of an output column too. WHERE and HAVING cannot see the alias.

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[6], but not in a WHERE, GROUP BY, or HAVING clause.

Two workarounds are portable. Repeat the expression instead of the alias in the clause that rejects it, or move the aliased expression into a subquery or a common table expression and filter it 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

Keep alias-heavy queries clear in DbSchema

As queries grow, aliases only improve readability if they are consistent and meaningful. DbSchema's visual Query Editor builds the SELECT for you: pick the tables, tick the columns, and the preview pane shows the generated SQL with the table aliases it assigned, beside the result grid once the query runs.

For a statement you wrote by hand, run it in the SQL Editor. The result grid takes its column headers from your aliases, so a mistyped or duplicated alias shows up in the output instead of staying buried in the query text. The SQL Editor is part of the free Community Edition; the visual Query Editor is a Pro feature.

Common mistakes

1. Forgetting the AS Keyword: While the AS keyword is optional in many databases, it's a good practice to use it for clarity.
2. Not Using Aliases in Joins: This can lead to confusion when tables have columns with the same name.
3. Inconsistent Alias Usage: Once you set an alias, ensure you use it consistently throughout the query.

FAQs

Q1: Can I use SQL aliases outside the query they are defined in?

• Answer: No, aliases are temporary and can only be used within the query they are defined in.

Q2: Can I use numerical values as aliases?

• Answer: Yes, but it's not recommended as it can lead to confusion. Always aim for meaningful alias names.

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

• Answer: No, aliases within a query must be unique.

Practice questions

1. Given a table Products, write a query to fetch the total price of all products using an alias TotalPrice.
2. Write a query to fetch the first name and last name from a table Persons as a single column alias FullName.
3. Using a table Books, write a query to fetch the average price of all books with a price greater than 50 using a sub-query and an alias AvgPriceAbove50.
4. Given two tables Authors and Books, write a query using aliases to fetch the name of the author and the title of the book they wrote.

To try these aliases against your own schema, download DbSchema and run the queries in the SQL Editor, which is part of the free Community Edition; the visual Query Editor that generates the aliased SELECT for you is in Pro.

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)