The SQL alias is a nice little feature of SQL that allows you to write more concise code and create column names when no column name exists.
There are two SQL alias types; column aliases, and table aliases. In this article, I provide an overview of both.
What is an Alias in SQL?
In SQL, an alias is a feature that allows us to temporarily assign a different name to a column or table in our SQL queries. This allows us to reduce the amount of code in our queries, which can be particularly beneficial in complex queries.
It also allows us to provide column names to client applications where no column name exists (for example, when using a calculated field).
Benefits of SQL Aliases
Some of the main benefits of SQL aliases include:
- Allows you to provide more readable names to the column headers when they’re presented in the results
- Allows client applications to refer to a calculated field by name where no column name exists
- Allows you to reduce code and make your queries more concise
- Can be used as an obfuscation technique to protect the names of the underlying columns of a query
I should point out that assigning an alias does not actually rename the column or table. It simply provides an alternative name that can be used to refer to it.
Alias Syntax
To create an alias in SQL, simply follow the column or table name with your chosen alias. You can optionally use the AS keyword between the column/table name and your alias.
Like this for columns:
SELECT Column1 AS Alias1
...
or
SELECT Column1 Alias1
...Or like this for tables:
...
FROM Table1 AS Alias1
...
or
...
FROM Table1 Alias1
...The following examples will illustrate this better.
The Column Alias
Probably the most commonly used alias is the column alias. The column alias allows you to provide a temporary name for your columns.
It also allows you to provide a column name in places where no column name exists.
The following two examples show the same query written with, and without column aliases.
Without Column Alias
Here’s a simple SQL query that doesn’t use column aliases.
SELECT
f_name,
l_name
FROM customers;Result:
+----------+----------+ | f_name | l_name | |----------+----------| | Homer | Connery | | Bart | Pitt | | Nancy | Simpson | | Boris | Trump | +----------+----------+
In this case, I didn’t provide any column aliases, and so the actual underlying column names were presented as column headers in the results.
With Column Alias
Here’s the same query, except this time I use column aliases.
SELECT
f_name AS FirstName,
l_name AS LastName
FROM customers;Result:
+-------------+------------+ | FirstName | LastName | |-------------+------------| | Homer | Connery | | Bart | Pitt | | Nancy | Simpson | | Boris | Trump | +-------------+------------+
Notice that the column aliases were used as column headers in the results.
Column Aliases on Calculated Fields
Column aliases can also be used on calculated fields where no column name exists. I don’t mean on computed columns, where there is a column name, but on fields where the value is derived from an expression other than a simple column’s value.
“How could there be no column name?” you may ask.
Well, there are many occasions where you may find that there’s no column name being returned in a query. Have you ever seen (No column name) as a column header of your query results?
There are lots of situations where this can occur.
Without a Column Alias
Here’s an example of a query that doesn’t return a column header.
SELECT
f_name + ' ' + l_name
FROM customers;Result:
+--------------------+ | (No column name) | |--------------------| | Homer Connery | | Bart Pitt | | Nancy Simpson | | Boris Trump | +--------------------+
This example concatenates each customer’s first name and last name and presents the result as one column. Only problem is that the DBMS doesn’t know what to call the column.
This is a perfect opportunity for an alias!
With a Column Alias
Here’s the same example, except I now assign a column alias to the result.
SELECT
f_name + ' ' + l_name AS FullName
FROM customers;Result:
+---------------+ | FullName | |---------------| | Homer Connery | | Bart Pitt | | Nancy Simpson | | Boris Trump | +---------------+
The good thing about this is that, any client application can now take these results and refer to the calculated field by its alias.
Note that the above example uses the SQL Server string concatenation operator (+). In DB2, Oracle, PostgreSQL, and SQLite, you’ll need to use ||. And in MySQL and MariaDB, use the CONCAT() function. While this has nothing to do with SQL aliases, I just thought I’d mention it 🙂
Aliases with Spaces
It’s also possible to create aliases with spaces.
When you do this, surround the alias with double quotes. In some DBMSs, you can optionally use other characters (such as square brackets [] in SQL Server).
SELECT
f_name + ' ' + l_name AS "Full Name"
FROM customers;Result:
+---------------+ | Full Name | |---------------| | Homer Connery | | Bart Pitt | | Nancy Simpson | | Boris Trump | +---------------+
Bear in mind that it’s generally not considered good practice to have spaces in aliases. Spaces can cause all sorts of problems for client applications, and for that reason, you should generally avoid including spaces in your column aliases.
Omitting the AS Keyword
As mentioned, the AS keyword is optional. Therefore, we could rewrite any of the previous examples without the AS keyword.
Here’s one example.
SELECT
f_name FirstName,
l_name LastName
FROM customers;Result:
+-------------+------------+ | FirstName | LastName | |-------------+------------| | Homer | Connery | | Bart | Pitt | | Nancy | Simpson | | Boris | Trump | +-------------+------------+
Although you can omit the AS keyword, some SQL professionals prefer to always include it, for readability reasons.
Whichever syntax you prefer to use, I would recommend keeping it consistent. If you choose to omit the AS keyword, then omit it everywhere. If you choose to include it, then include it everywhere.
The Table Alias
The table alias is similar to the column alias, but as the name suggests, the table alias is for tables.
The table alias is also known as the correlation name.
The table alias is often used when performing joins. It can be especially useful in complex queries, because it can help keep the code more concise and more readable.
Below are two examples; one without a table alias, and one with a table alias.
Example Without a Table Alias
Here’s a basic example of a query that does a left join between two tables.
SELECT
PetTypes.PetType,
COUNT(Pets.PetTypeId) AS Count
FROM Pets
LEFT JOIN PetTypes
ON Pets.PetTypeId = PetTypes.PetTypeId
GROUP BY PetTypes.PetType
ORDER BY Count DESC, PetTypes.PetType;Result:
+-----------+---------+ | PetType | Count | |-----------+---------| | Dog | 4 | | Cat | 3 | | Bird | 1 | +-----------+---------+
Note that we spell out the name of each table where ever we need to reference it.
Example With a Table Alias
Here’s the same example, except with table aliases.
SELECT
pt.PetType,
COUNT(p.PetTypeId) AS Count
FROM Pets AS p
LEFT JOIN PetTypes AS pt
ON p.PetTypeId = pt.PetTypeId
GROUP BY pt.PetType
ORDER BY Count DESC, pt.PetType;Result:
+-----------+---------+ | PetType | Count | |-----------+---------| | Dog | 4 | | Cat | 3 | | Bird | 1 | +-----------+---------+
As with column aliases, the AS keyword is optional with table aliases. In Oracle, the AS keyword isn’t even supported with table aliases (although it is supported with column aliases).
Therefore, we could rewrite our example to the following.
SELECT
pt.PetType,
COUNT(p.PetTypeId) AS Count
FROM Pets p
LEFT JOIN PetTypes pt
ON p.PetTypeId = pt.PetTypeId
GROUP BY pt.PetType
ORDER BY Count DESC, pt.PetType;Result:
+-----------+---------+ | PetType | Count | |-----------+---------| | Dog | 4 | | Cat | 3 | | Bird | 1 | +-----------+---------+
Either way, same result.