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:
- If you don’t add a comma after a column, the second column won’t be displayed.
- 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.
Thanks Ben for putting all these material all in once place.
Believe me your website is like a SQL Bible for me.
It is so so helpful.
Thanks Nitin, glad you like it!
HI Ben
why should i put a double quotes when to use alias around the table names
Help me understand this.
Hi Omar,
Double quotes around table names and aliases are used to enforce the capitalisation of a table name, or to include spaces.
Oracle stores table names as all caps, so if you select from a table called “Customer” with double quotes, it’s different to selecting from CUSTOMER.
Also, using double quotes with aliases will show the aliases or column headings exactly as typed. It gives a more readable column heading if you’re displaying it to the users. E.g. AS “First Name” may be preferable to AS FIRST_NAME.
Hope that helps.
Ben
In the subquery part there’s a stray “s” in the second to last line. Why is that?
Ah, that “s” is the name given to the subquery. I can probably explain that better in the article. Thanks for letting me know!
I appreciate the informative article. For those interested in this topic, I suggest exploring this article (https://blog.devart.com/alias-for-columns-in-sql-query.html), which describes Improving query efficiency and clarity via SQL aliases. It is a beneficial addition.