Difference Between Alias and Synonym in SQL

In SQL, two keywords are used to create an alternative name which are alias and synonym. Although both of these have similar functions, each of these keywords has its purpose. This means that the Alias has different usage from Synonym and vice versa.

This article aims to clarify the differences between the alias and the synonym and guide on when and how to use each effectively.

Alias in SQL is used to create a temporary name for a table or a column of a table without affecting the original name. To define an alias in SQL, we use the keyword AS. However, this keyword is not mandatory. So, it is also possible to write the alias name immediately without using the AS keyword. As mentioned before, this name is temporary and will be valid only for the duration of that query.

Alias is used when we are dealing with a complex query that has tables or columns with similar names. Alias is also commonly used when we are using aggregate functions such as SUM(), AVG(), COUNT(), etc. Moreover, it is beneficial to use aliases when the names of tables or columns are lengthy or meaningless. By using the alias, the query that we have can be more efficient, readable, and meaningful. Therefore, it will be easier to understand.

To learn more about SQL ALIAS, click here.

Syntax:

SELECT column_name AS alias_name

FROM table_name;

or

SELECT column_name alias_name

FROM table_name;

Example

SELECT w3wiki AS GFG

FROM GFG_Table;

or

SELECT w3wiki GFG

FROM GFG_Table;

The synonym in SQL provides us with an alternative name for a database object. This alternative name will refer to the respective original object. We cannot have synonyms with the same name, which means when defining a synonym we have to make sure that the name of that particular synonym is unique. Moreover, the alternative name that is provided by using a synonym is permanent.

The synonym cannot be accessed by other databases, that is why we can say that the synonym is database-independent. The synonym provides a level of abstraction that will allow users to work with these objects without knowing the details. Backward compatibility can also be provided when we are using the synonym. The lengthy and confusing object names of a database can be simplified with the help of the synonym. To define the synonym in SQL, the keyword SYNONYM is used.

To learn more about SQL SYNONYM, click here.

Syntax:

CREATE SYNONYM [synonym_name]

FOR [base_object_name];

Example

CREATE SYNONYM GFG

FOR dbo.w3wiki;

Now after defining the synonym, we can access it by using the synonym name.

SELECT * FROM GFG;

Even though the alias and synonym in SQL have some similarities, the alias and synonym have different purposes and usages. For a better understanding, we can see the list of differences between them below:

Both alias and synonym are used for making alternative names. However, the alternative name created by an alias is for views, tables, or columns of the table. This alternative name is temporary and only valid inside the query where it is being used. It is defined by using the keyword AS.
On the other hand, the alternative name created by synonym is for views, tables, triggers, etc. This alternative name is permanent and valid inside the schema or the database. It is defined by using the keyword SYNONYM.