EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Aliases
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle Aliases

By Priya PedamkarPriya Pedamkar

Oracle Aliases

Introduction to Oracle Aliases

Oracle has Aliases for all the components associated with the database, like the columns and tables. Alias is a temporary name given to the column or the table in a query, for the user’s convenience in readability for minimizing the query length. When an alias is applied for a column or a table, the alias name is added to the select query in upper case typically, while placing ‘AS’ as an alias keyword between the actual name and the Alias name.

Types of Alias in Oracle

Here are two types of oracle Aliases which are explained below.

1. Column Alias

  • Column Alias renames a column heading in a Query.
  • The column Alias is specified in the SELECT list by declaring the Alias after the column name by using the space separator.
  • Alias heading appears in UPPER casing by default.
  • The Alias name should be declared in Double Quote if it is against the specification of naming conventions of Oracle (like Alias name consisting space etc.).
  • The AS keyword can be used between the column name and the Alias name.
  • An Alias name effectively renames the SELECT list item for the duration of that Query only.
  • An Alias cannot be used, anywhere in the SELECT list for the operational purpose.

2. Table Alias

  • Table Alias renames the original name of the table in a SQL statement.
  • Table Aliases are very useful when working with self joins.
  • Table Alias is applied for the current SQL statement only.
  • It is an important source when implementing standards of MERGE statements, correlated Queries, and Analytical Functions.

Syntax of Oracle Alias

Here is the syntax of Alias which are given below with basic example:

SELECT Col_1 Column_1, Col_2 "Column 2", ..., Col_n Column_N
FROM TableName Table_Name WHERE condition(s);

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Description: Here is the explanation of the above syntax given below.

  • Col_1/2/n: The column(s) or calculation as per your requirement. Column_1, Column 2 and Column_N are the Column Alias name respectively.
  • TableName: As per your requirement Table_Name is the ALAS name of TableName.
  • WHERE: It’s optional, depends on your requirements.

Example #1: Without Alias name,

Query:

SELECT Empno, Ename, Job, Deptno FROM Emp WHERE Deptno=10

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,354 ratings)

Output:

Oracle Aliases eg1

Example #2: With Alias name,

Query:

SELECT Empno Emp_id, Ename name, Job Designation, Deptno Department
FROM Emp WHERE Deptno=10;

Output:

Oracle Aliases eg2

Explanation: The above two examples (without and with Alias name) clearly shows that applying ALAS name is a good practice to hide the original name of columns and make it more readable.

Examples to Implement Oracle Aliases

In this section, we’ll see the implementation of oracle Alias and its behavior. For that, we will use the below sample table (Emp) with 14 records to understand the oracle Alias behavior.

Query:

SELECT * from Emp;

Output:

Oracle Aliases eg

1. AS Keyword Between Column Name and ALIAS

Query:

SELECT Empno AS emp_id, Ename AS name, job AS Designation, Deptno FROM Emp;

Output:

Oracle Aliases eg1

The AS keyword can be used for column Alias name but it’s optional in ORACLE. The AS keyword can be applied only for column(s) Alias name, not for Table(s) Alias name. We’ll get an error if we apply AS keyword for Table Alias name. Example below.

Query:

SELECT Empno AS emp_id, Ename AS name, job AS Designation, Deptno FROM Emp AS E;

Output:

Oracle Aliases eg1.1

2. ALIAS Name without Any Space

Query:

SELECT Empno emp_id, Ename name, job Designation, Deptno FROM Emp;

Output:

Oracle Aliases eg2

In this example, column Alias names don’t keep any space, so don’t need to enclose the ALAIS names with a double-quote.

3. ALIAS Name with Space

Query:

SQL> SELECT Empno Employee ID, Ename First Name, Job Designation, Deptno FROM Emp WHERE Job ='SALESMAN';

Output:

Oracle Aliases eg3

In this example, Output throws an Error because Alias names contain space. If Alias name contains space then Alias name must be enclosed with Double Quote (” “) to prevent the error.

4. ALIAS Name Enclosed with Double Quote

Query:

SQL> SELECT Empno "employee id", Ename "first name", Job designation, Deptno FROM Emp WHERE Job ='SALESMAN';

Output:

Oracle Aliases eg4

In this example, there are two important points.

  • Output is not throwing an error even if the Alias name contains space because it is enclosed with Double Quote.
  • The SELECT statement supplied all column Alias names are in lower case only but the first two column ALIAS names (in output) are in lower case but the third one (DESIGNATION) is in upper case. WHY?

Because the ALAIS name doesn’t change the case if it is enclosed with Double Quote. In the above example, the first two ALAIS names are enclosed with Double Quote but the third one is not enclosed with, so by default third column name is in upper case but the first two are in lower case.

5. ALIAS Name Makes Column Name More Readable

Query:

SELECT Ename ||'''s Salary is fixed as '|| Sal ||' monthly costing annually at '|| Sal*12 FROM Emp;

Output:

Oracle Aliases eg5

In this example, it’s very difficult to understand the column name or column data because the column name tells about the column data and makes it easy to understand.

Query:

SELECT Ename ||'''s Salary is fixed as '|| Sal ||' monthly costing annually at '|| Sal*12 "Employee and Salary" FROM Emp;?

Output:

Oracle Aliases eg5.2

Here, column Alias name “Employee and Salary” makes it more readable and easier to understand.

6. ALIAS Name Behavior in WHERE Clause

Column Alias name cannot be used in the WHERE clause directly. It will throw an error, see the example below

Query:

SELECT Empno, Ename, Job Designation FROM Emp WHERE Designation = 'CLERK';

Output:

eg6

In this example, Designation is an Alias name of Job which is invalid in WHERE clause but the original column can be passed in WHERE clause. The column Alias name in WHERE clause can be used in two cases, given below with an example.

  • Sub-Query
  • Common Table Expression(CTE)

Query:

SELECT * FROM
(
SELECT Empno AS employee, Deptno AS department, Sal AS salary
FROM Emp
)
WHERE employee = 7369;

Output:

eg6.1

Here Employee is an Alias name of Empno column which is getting used in WHERE clause.

7. Table ALAIS Name when Working with Self Join

Query:

SELECT Ename, Job FROM Emp, Emp;

Output:

eg 7

The above SELECT statement is performing Self Join and output throwing an error because there are two tables with the same name that’s why getting an error. The table Alias name is the best way to avoid this ambiguous situation. Example below.

Query:

SELECT E1.Ename Employee, E2.Ename Manager FROM Emp E1, Emp E2 WHERE E1.MGR= E2.Empno;

Output:

eg 7

In the above example, Table Alias name (E1 & E2) used to avoid the ambiguous situation and got the result without any error.

Points to Remember:

  • Use Table Alias whenever performing joins, it makes easy and short the Query.
  • ‘AS’ keyword can be used for column Alias, but not for Table Alias.
  • AS keyword can be used in lower case as well.
  • In Oracle, space can be used for column or table Alias name instead of AS keyword.

Conclusion

Oracle Alias is nothing but a temporary name of a column or table. To Hide original column(s) name or make column(s) of the result set more understandable or keep the query short, Oracle Alias (Column or Table) can be used.

Recommended Articles

This is a guide to Oracle Aliases. Here we discuss the syntax, types and different examples of Oracle Aliases along with the code implementation. You can also go through our suggested articles to learn more –

  1. How does a trigger work in oracle?
  2. Oracle Versions
  3. How does Cross Join work in Oracle?
  4. How does the HAVING Clause work in Oracle?
  5. Complete Guide to Oracle Operators
  6. PostgreSQL Cross Join | Examples
  7. Complete Guide to SQL Cross Join
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more