Oracle Table and Column Alias

Column Alias and Table Alias are used in Oracle for better readability of queries and for better understanding of the data displayed, just by looking at the columns.

While working with relational databases we often find that the column names are given using some naming convention and might not make much sense. We can replace these generic names with better readable names that make more sense.

Lets apply column alias to this table 1:

SELECT id, 
first_name AS "TV Series Characters", age 
FROM table1;

Result:

As we can see, we changed the name of second column as per our wish. We can also use column and table alias when we perform operation like joining tables, merging columns etc. or when using expressions or functions.

Lets have a look at all those examples, one by one.

Oracle Column Alias Examples

We will be using Employees table from HR schema for these examples.

1. Substituting the column name using AS

The AS is a keyword used between actual column_name and column alias. Lets see this query:

SELECT employee_id, 
first_name AS "Employee Name" 
FROM employees;

Result:

We can also skip AS and directly enter the column alias.

SELECT employee_id, 
first_name "Employee Name" 
FROM employees;

2. Using Column Alias with and without Quotation marks

If we want to replace the name of column with a name that does not contain any spaces or special character between them, then we don’t need quotation marks.

Lets see this query:

SELECT employee_id, 
first_name AS forename, last_name AS surname
FROM employees;

Result:

As we can see, we don’t need quotation marks while mentioning column alias which don’t contain special character.

Oracle by default capitalized the column alias if we don’t use quotation marks.

Let’s have a look at another query which requires us to use quotation marks while mentioning column alias.

SELECT employee_id, 
first_name AS "First Name", last_name AS "Surname"
FROM employees;

Result:

Since the first column alias contained a space, we used quotation marks in this query. If the column alias contains any special characters and we don’t give quotation marks, then we might get this error:

ORA-00923: FROM keyword not found where expected

3. Using Column Alias for Expressions

When we perform any operations on columns, we may want to name it separately for better understanding.

Suppose we want to perform arithmetic operations on salary column to find the annual salary of employees, we will use this query to substitute a column alias in place of arithmetic calculation. Have a look:

SELECT employee_id, 
first_name, last_name, salary*12 "ANNUAL SALARY" 
FROM employees;

Result:

As we can see, the query returned Annual Salary instead of Salary*12 which helps us to understand what the column is about.

Lets look at one more example of concatenation.

Here we join two column using CONCAT or || operator and rename the new column as something that makes sense to us. Check this out

SELECT 
first_name || ' ' || last_name AS "Full Name of Employee" 
FROM 
employees;

Result:

We perform concatenation on first_name and last_name columns and then rename the news column as ” Full Name of Employee”.

4. Using Column Alias with ORDER BY Clause

SELECT 
employee_id, first_name, last_name, salary*12 "ANNUAL SALARY"
FROM employees
ORDER BY "ANNUAL SALARY" ASC;

5. Using Table Alias in Oracle

In relational databases, we work with multiple tables at once which are connected to one other. While performing operation on multiple tables at once, we need to specify the table name as well as column name each time, which gets a bit tiring. Instead of that, we use table alias.

We give table alias the same way we give column alias,

table_name AS "table_alias"
table_name table_alias

While calling a column from table, we use the following syntax:

table_name.column_name

Instead of table name, we can use table alias:

table_alias.column_name

Using this syntax is very useful when we join two tables using join query, lets have a look at one example:

SELECT
e.first_name Employee,
d.department_name Department
FROM<br>DEPARTMENTS d, employees e
WHERE D.department_id = E.department_id;

Result:

In this article, we learned how to use Column Alias.