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:

SELECT
column1 [AS] colname
…

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:

SELECT
last_name, 
salary AS annual_salary
FROM employee;

The results of this query would be:

LAST_NAME	ANNUAL_SALARY
SMITH	40000
ANDERSON	60000
JONES	45000

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:

SELECT
first_name,
last_name,
salary annual_salary,
hire_date,
start_date
FROM 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:

SELECT
first_name,
last_name,
salary annual_salary,
hire_date
start_date
FROM 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:

FIRST_NAME	LAST_NAME	ANNUAL_SALARY	START_DATE
…	…	…	…

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:

SELECT
first_name,
last_name,
salary AS annual_salary,
hire_date,
start_date
FROM 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.

CREATE TABLE employee_test (
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  salary NUMBER(6),
  hdt DATE,
  dept_id NUMBER(2)
);

 

CREATE TABLE department_test (
  id NUMBER(2),
  dept_name VARCHAR2(50)
);
 
INSERT ALL
INTO employee_test (first_name, last_name, salary, hdt, dept_id)
  VALUES ('JOHN', 'SMITH', 40000, TO_DATE('01-FEB-2018', 'DD-MON-YYYY'), 1)
INTO employee_test (first_name, last_name, salary, hdt, dept_id)
  VALUES ('SALLY', 'ANDERSON', 60000, TO_DATE('14-SEP-2012', 'DD-MON-YYYY'), 2)
INTO employee_test (first_name, last_name, salary, hdt, dept_id)
  VALUES ('MARK', 'JONES', 45000, TO_DATE('20-NOV-2015', 'DD-MON-YYYY'), 1)
SELECT * FROM dual;
 
INSERT ALL
INTO department_test (id, dept_name)
  VALUES (1, 'SALES')
INTO department_test (id, dept_name)
  VALUES (2, 'HR')
SELECT * FROM dual;
 

 

Example 1: Simple SELECT

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

SELECT
last_name,
hdt AS hire_date
FROM employee_test;

The results are:

LAST_NAME	HIRE_DATE
SMITH	1 Feb 2018
ANDERSON	14 Sep 2012
JONES	20 Nov 2015

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.

SELECT
first_name,
last_name,
first_name || ' ' || last_name AS full_name
FROM employee_test;

The results are:

FIRST_NAME	LAST_NAME	FULL_NAME
JOHN	SMITH	JOHN SMITH
SALLY	ANDERSON	SALLY ANDERSON
MARK	JONES	MARK JONES

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.

SELECT
last_name,
hdt AS hire_date,
ROUND(MONTHS_BETWEEN(SYSDATE, hdt),0) AS tenure_months
FROM employee_test;

The results are:

LAST_NAME	HIRE_DATE	TENURE_MONTHS
SMITH	1 Feb 2018	15
ANDERSON	14 Sep 2012	79
JONES	20 Nov 2015	41

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:

SELECT
last_name,
hire_date,
tenure_months,
CASE
  WHEN tenure_months > 24 THEN 'At least two years'
  ELSE 'Less than two years'
END AS tenure_statement
FROM (
  SELECT
  last_name,
  hdt AS hire_date,
  ROUND(MONTHS_BETWEEN(SYSDATE, hdt),0) AS tenure_months
  FROM employee_test
) s
ORDER 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:

LAST_NAME	HDT	TENURE_MONTHS	TENURE_STATEMENT
SMITH	1 Feb 2018	15	Less than two years
ANDERSON	14 Sep 2012	79	At least two years
JONES	20 Nov 2015	41	At least two years

 

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:

SELECT
columns
FROM 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:

SELECT first_name
FROM employee e;

SELECT first_name
FROM 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:

SELECT first_name
FROM employee e;

But this query will not:

SELECT first_name
FROM 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.

SELECT
e.
FROM 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.

SELECT
e.last_name,
e.salary
FROM 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:

LAST_NAME	SALARY
SMITH	40000
ANDERSON	60000
JONES	45000

 

Example 2: Using a WHERE Clause

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

SELECT
e.last_name,
e.salary
FROM employee_test e
WHERE e.salary = 40000;

The results are:

LAST_NAME	SALARY
SMITH	40000

 

Example 3: Using a Join

This example uses table aliases in a join.

SELECT
e.last_name,
e.salary,
d.dept_name
FROM employee_test e
INNER 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:

LAST_NAME	SALARY	DEPT_NAME
SMITH	40000	SALES
ANDERSON	60000	HR
JONES	45000	SALES

 

Example 4: Update Statement

SQL table aliases can also be used in UPDATE statements.

Here’s an example:

UPDATE employee_test e
SET e.salary = 42000, e.dept_id = 2
WHERE 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:

LAST_NAME	SALARY	DEPT_ID
SMITH	40000	1
ANDERSON	60000	2
JONES	45000	3

Here’s the table after the update:

LAST_NAME	SALARY	DEPT_ID
SMITH	42000	2
ANDERSON	60000	2
JONES	45000	3

 

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.