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 …
- 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:
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:
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.
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:
|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:
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:
|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:
|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:
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:
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:
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:
Here’s the table after the update:
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.
6 thoughts on “SQL Alias: A Guide to the SQL Aliases and the SQL AS Keyword”
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!
why should i put a double quotes when to use alias around the table names
Help me understand this.
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.
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!