Difference Between Synonym and Alias

Synonym vs Alias (in ORACLE databases) | Private synonyms and Public synonyms

 

In English, synonym and alias have nearly the same meanings. But in databases those are two different things. Especially in ORACLE databases, both of their usage is different. Synonyms are used to refer objects of a schema or a database from another schema. So synonym is a database object type. But aliases are coming in a different way. That means; they are not database objects. Aliases are used to refer tables, views and columns inside queries.

Synonyms

These are a type of database objects. They refer to other objects in the database. The most common usage of synonym is, to refer an object of a separate schema by using another name. But synonyms can be created to refer the objects of another database, as well (in distributed databases, using database links). Tables, views, functions, procedures, packages, sequences, materialized views, java class objects and triggers can be used as references for the synonyms. There are two types of synonyms.

1.  Private synonyms (can be used only by the user who created them.)
2.  Public synonyms (can be used by all users who have the appropriate privileges)

Here, is a simple syntax to create a synonym in a separate database,

create synonym myschema.mytable1 for userA.table1@database_link1

Since we have a synonym named mytable1 in myschema for userA.table1@database_link1 (distributed database table), we can easily refer the distributed database table using mytable1. We don’t need to use the long object name with database link everywhere.

Alias

These are just another name for a view, a table, or a column inside a query. They are not database objects. Therefore, aliases are not valid everywhere in the schema/database. They are valid inside the query only. Let us see this example,

                                    select tab1.col1 as c1,tab2.col2 as c2

                                       from user1.tab1 tab1,user1.tab2 tab2

                                       where tab1.col1 = tab2.col2

Here, c1 and c2 are column aliases, which are used for tab1.col1 and tab2.col2, and tab1 and tab2 are table aliases, which are used for user1.table1 and user2.table2. All of these aliases are valid inside this query only. 

What is the difference between Synonym and Alias (in ORACLE databases)? Synonyms are a database object type. But aliases are just a name to refer a table, view or a column inside a query. Not a database object. Synonyms can be created for tables, views, functions, procedures, packages, sequences, materialized views, java class object types and triggers. But aliases are used only for views, tables and their columns. Since synonyms are a database object, they are valid inside the schema (private synonym) or inside the database (public synonym). But aliases valid inside the query where they are being used. Each schema needs “create synonym” privilege to create synonyms. But there is no any privilege to use aliases.