SQL Alias: A Guide to the SQL Aliases and the SQL AS Keyword

What is an alias in SQL?

It's a name you give to a column or a table in your SQL query to make it easier to display the results or to write your query.

SQL aliases are a useful feature and are available in all major database vendors, including Oracle, SQL Server, MySQL, PostgreSQL.

There are two types of aliases in SQL: column aliases and table aliases.

SQL Column Alias

An SQL column alias is a name that you can give to a column in a query.

One of the most common ways to use it is in a SELECT query. The syntax for doing this is:

1SELECT
2column1 [AS] colname
3…

This means:

• column1 is the column name in the database. It can also be an expression or a function.
• AS is a keyword used to specify that a column alias will be used.
• colname is the new name you provide for the column.

An example of this is this SELECT query:

1SELECT
2last_name, 
3salary AS annual_salary
4FROM employee;

The results of this query would be:

The salary column is the column in the database, but it has been given a column name in the output.

SQL AS Keyword - Should I Use It If It's Optional?

The SQL AS keyword is used to define a column alias. It's also an optional keyword.

If it's optional, should you use it?

I recommend that you do.

It makes it clear that you are using a column alias.

Consider this query that does not use the AS keyword:

1SELECT
2first_name,
3last_name,
4salary annual_salary,
5hire_date,
6start_date
7FROM employee;

This query gives an alias of annual_salary to the salary column. It will run and show you the data you need.

The database knows that annual_salary is an alias, rather than a column, because there is no comma after the previous column.

This can cause two problems:

1. If you don't add a comma after a column, the second column won't be displayed.
2. Readers of your query may ask themselves if you are missing a comma by accident or adding a column alias deliberately, making it confusing for the reader.

Consider this query which is slightly different:

1SELECT
2first_name,
3last_name,
4salary annual_salary,
5hire_date
6start_date
7FROM employee;

Notice that there is no comma after hire_date. Looking at this query, it's not easy to see if the comma was forgotten, or if it was written to use an alias of start_date for the hire_date column.

The results will show this:

Notice that there is only one date column. This is the hire_date column shown as the start_date.

So, how can you avoid all of this confusion? Use the AS keyword with your column aliases.

The query above can be written as:

1SELECT
2first_name,
3last_name,
4salary AS annual_salary,
5hire_date,
6start_date
7FROM employee;

This makes it clear which columns are to be shown in the results and which columns have an alias.

That's why I recommend using the AS keyword in your column aliases.

SQL Column Alias Examples

Let's take a look at some examples of SQL column aliases.

Sample Data

This SQL can be used to set up the sample data used in this guide.

 1CREATE TABLE employee_test (
 2  first_name VARCHAR2(50),
 3  last_name VARCHAR2(50),
 4  salary NUMBER(6),
 5  hdt DATE,
 6  dept_id NUMBER(2)
 7);
 8
 9 
10
11CREATE TABLE department_test (
12  id NUMBER(2),
13  dept_name VARCHAR2(50)
14);
15 
16INSERT ALL
17INTO employee_test (first_name, last_name, salary, hdt, dept_id)
18  VALUES ('JOHN', 'SMITH', 40000, TO_DATE('01-FEB-2018', 'DD-MON-YYYY'), 1)
19INTO employee_test (first_name, last_name, salary, hdt, dept_id)
20  VALUES ('SALLY', 'ANDERSON', 60000, TO_DATE('14-SEP-2012', 'DD-MON-YYYY'), 2)
21INTO employee_test (first_name, last_name, salary, hdt, dept_id)
22  VALUES ('MARK', 'JONES', 45000, TO_DATE('20-NOV-2015', 'DD-MON-YYYY'), 1)
23SELECT * FROM dual;
24 
25INSERT ALL
26INTO department_test (id, dept_name)
27  VALUES (1, 'SALES')
28INTO department_test (id, dept_name)
29  VALUES (2, 'HR')
30SELECT * FROM dual;
31 

Example 1: Simple SELECT

Here's a simple example of a SELECT statement that uses a column alias.

1SELECT
2last_name,
3hdt AS hire_date
4FROM employee_test;

The results are:

The column shown as hire_date above is actually stored as hdt in the table.

Example 2: Expression

This example uses an expression and gives it a column alias.

1SELECT
2first_name,
3last_name,
4first_name || ' ' || last_name AS full_name
5FROM employee_test;

The results are:

The names are concatenated into a single field, which is called full_name. Without the column alias, the column name would be a lot messier.

Example 3: Function

This example uses a column alias on a function.

1SELECT
2last_name,
3hdt AS hire_date,
4ROUND(MONTHS_BETWEEN(SYSDATE, hdt),0) AS tenure_months
5FROM employee_test;

The results are:

This tenure_months column shows the number of months an employee has been working for. It uses a combination of the MONTHS_BETWEEN, ROUND, and SYSDATE functions.

Example 4: Subquery

Another common use of a column alias is in a subquery. If you're performing complicated logic but want to do further operations on it, it's often a good idea to enclose it in a subquery.

This will allow you to:

• GROUP BY the column alias
• ORDER BY the column alias
• Use the column alias more than once without having to write the expression again

Here's an example:

 1SELECT
 2last_name,
 3hire_date,
 4tenure_months,
 5CASE
 6  WHEN tenure_months > 24 THEN 'At least two years'
 7  ELSE 'Less than two years'
 8END AS tenure_statement
 9FROM (
10  SELECT
11  last_name,
12  hdt AS hire_date,
13  ROUND(MONTHS_BETWEEN(SYSDATE, hdt),0) AS tenure_months
14  FROM employee_test
15) s
16ORDER BY tenure_months ASC;

This query uses the tenure_months, calculated from the subquery, in the outer query as both a column to display and an input into the CASE statement. Without the subquery and column alias, the query would be a lot messier.

The results are:

SQL Table Alias

What is an SQL table alias?

It's a short name you give to a table when you use it in an SQL query.

Table aliases can help your queries by:

• Improving the readability of queries
• Speeding up the autocomplete feature in your SQL IDE.
• Allowing you to use two instances of the same table (for queries using a self join, for example)

The syntax of a table alias is:

1SELECT
2columns
3FROM table [AS] alias_name;

The alias_name is the name given to your table to be used in the query.

The alias you use will often be a short one. Most of the aliases I use and see in other people's code are one to three letters long. This makes it easier to type and it's still easy to identify in a query.

Some examples are:

• employee e
• employee emp
• product p
• product pr
• customer c
• customer cust
• bill_usage_line_items bli

Should You Use the SQL AS Keyword with Table Aliases?

The AS keyword is optional when using it with a table alias.

This means the following two queries will work:

1SELECT first_name
2FROM employee e;
3
4SELECT first_name
5FROM employee AS e;

Which should you use?

Earlier I mentioned that for column aliases, you should use AS because it makes it clear that a column alias is used.

For table aliases, I also recommend using the AS keyword. However, the impact of not using the AS keyword is not as significant. If you don't use the AS keyword, there is no risk of tables being excluded. You'll likely get a syntax error when you run the query, instead of a column missing in your output.

Also, on Oracle SQL, you'll get an error if you add the AS keyword to the table alias.

So, this query will work on Oracle:

1SELECT first_name
2FROM employee e;

But this query will not:

1SELECT first_name
2FROM employee AS e;

Autocomplete with Table Aliases

Using a table alias also makes it easier to use the autocomplete feature of most IDEs.

In an IDE, to add a column into the SELECT statement, you'll need to type out the full name of the column.

Using a table alias, you can enter the table alias and then a period, and then a list of available columns will appear form that table. It makes it much easier to write your query.

For example, entering your query like this will cause the list of columns to appear after you enter the "e." into your SELECT clause.

1SELECT
2e.
3FROM employee e;

SQL Table Alias Examples

Let's take a look at some examples of the SQL table alias.

Example 1: Simple Query

Here's a simple example of a table alias. We're using the sample data from earlier in the article.

1SELECT
2e.last_name,
3e.salary
4FROM employee_test e;

Notice that the table aliases have been used as a prefix to the column names in the SELECT clause. This is optional in many cases but is often required (if there are two tables with the same column name). It also helps to clarify which table a column exists in.

The results are:

Example 2: Using a WHERE Clause

This example demonstrates how to use a table alias as part of the WHERE clause.

1SELECT
2e.last_name,
3e.salary
4FROM employee_test e
5WHERE e.salary = 40000;

The results are:

Example 3: Using a Join

This example uses table aliases in a join.

1SELECT
2e.last_name,
3e.salary,
4d.dept_name
5FROM employee_test e
6INNER JOIN department_test d ON e.dept_id = d.id;

Notice that both the employee_test and department_test tables have table aliases ("e" and "d"). They are used in the join condition and in the SELECT clause.

The results of this query are:

Example 4: Update Statement

SQL table aliases can also be used in UPDATE statements.

Here's an example:

1UPDATE employee_test e
2SET e.salary = 42000, e.dept_id = 2
3WHERE e.last_name = 'SMITH';

This query gives the table alias "e" to the employee_test table, and all of the columns within the query are prefixed with the table alias.

Here's the table before the update:

Here's the table after the update:

Conclusion

SQL allows the use of both column aliases and table aliases. This is done using the SQL AS keyword, which is an optional keyword in many SQL statements including SELECT, UPDATE, and DELETE.

Column aliases allow you to use a different and often simpler name for a column in your query.

Table aliases allow you to name your table for use in other parts of your query, such as the SELECT or WHERE clauses.