A synonym in SQL is an alternative name used to reference a database object such as a table, view, procedure, function, or another supported object. Instead of repeatedly typing a long schema or object name, a synonym can provide a shorter and easier name.
For example, suppose a company has a table named customer_transaction_history_2026. If developers frequently access this table, using a shorter synonym such as customer_txn can make SQL statements easier to read and maintain.
SQL synonyms are particularly useful in large database environments where objects may belong to different schemas, databases, or servers. They can also make applications less dependent on the physical names of database objects.
However, synonym support is different across database systems. Oracle Database and Microsoft SQL Server provide dedicated synonym features, while MySQL does not provide Oracle-style SQL synonyms. MySQL users generally rely on aliases, views, or other database design techniques instead.
This guide explains 30 practical synonym examples and concepts, along with syntax, advantages, limitations, and differences between synonyms, aliases, and views.
What Is a Synonym in SQL?
A SQL synonym is a database object that provides an alternative name for another database object.
For example, in a supported database system, you might create:
CREATE SYNONYM emp FOR employees;
After creating the synonym, you can reference the object by its synonym:
SELECT * FROM emp;
Instead of:
SELECT * FROM employees;
The synonym does not contain a second copy of the table or its data. It simply provides another name or reference for the underlying object.
30 Synonyms in SQL: Examples and Explanations
The following examples demonstrate common ways SQL synonyms can be used. Syntax can vary depending on the database platform.
1. Synonym for an Employee Table
CREATE SYNONYM emp FOR employees;
Here, emp is an alternative name for the employees table.
You can then use:
SELECT * FROM emp;
This is useful when the original object name is longer than necessary.
2. Synonym for a Customer Table
CREATE SYNONYM cust FOR customers;
Now this query can be used:
SELECT * FROM cust;
The synonym makes repeated references to the customer table shorter.
3. Synonym for a Sales Table
CREATE SYNONYM sales FOR sales_transactions;
You can query:
SELECT * FROM sales;
This can make application SQL easier to read.
4. Synonym for a Table in Another Schema
CREATE SYNONYM emp FOR hr.employees;
Here, employees belongs to the hr schema, while emp provides a simpler reference.
You can use:
SELECT * FROM emp;
This is especially useful when users frequently access objects owned by another schema.
5. Synonym for a Department Table
CREATE SYNONYM dept FOR company.departments;
The synonym dept represents the company.departments object.
6. Synonym for a Long Table Name
Suppose a table is called:
customer_transaction_history_2026
A shorter synonym could be:
CREATE SYNONYM cust_txn
FOR customer_transaction_history_2026;
Then:
SELECT * FROM cust_txn;
This reduces repetitive typing.
7. Synonym for a View
In database systems that support synonyms for views, you can create an alternative name for a view:
CREATE SYNONYM employee_report
FOR employee_summary_view;
The synonym allows users or applications to reference the view with another name.
8. Synonym for a Stored Procedure
Some database systems support synonyms for procedures.
For example:
CREATE SYNONYM get_emp
FOR get_employee_details;
The synonym can provide a shorter reference to the procedure.
Support for specific object types depends on the database platform.
9. Synonym for a Function
Where supported, a synonym can provide an alternative name for a database function.
CREATE SYNONYM calc_total
FOR calculate_order_total;
This can simplify references to a frequently used database object.
10. Private Synonym
A private synonym is generally available within the schema or user context where it is created.
For example:
CREATE SYNONYM my_emp FOR hr.employees;
A private synonym can help an individual schema use a shorter object name without creating a database-wide name.
11. Public Synonym
Oracle supports public synonyms that can be referenced by multiple users, subject to the required permissions.
Example:
CREATE PUBLIC SYNONYM employees
FOR hr.employees;
Public synonyms should be planned carefully because they introduce names into a wider database namespace.
12. Synonym for a Schema Object
A synonym can hide the schema-qualified name from regular queries.
Instead of:
SELECT * FROM hr.employees;
you may use:
SELECT * FROM emp;
after creating the appropriate synonym.
13. Synonym for a Remote Object
Some database systems allow synonyms to reference remote database objects.
A conceptual example is:
CREATE SYNONYM remote_emp
FOR hr.employees@remote_db;
The exact syntax and requirements depend on the database platform and its database-link functionality.
14. Synonym for an Application Object
Suppose an application frequently accesses:
production.customer_orders
A synonym can provide a simpler reference:
CREATE SYNONYM orders FOR production.customer_orders;
Application queries can then use:
SELECT * FROM orders;
15. Synonym for a Reporting Object
Reporting systems often use views or reporting tables with long names.
For example:
CREATE SYNONYM monthly_report
FOR monthly_customer_sales_report;
The shorter name can make reporting queries easier to manage.
16. Synonym for an Inventory Table
CREATE SYNONYM inventory
FOR warehouse_product_inventory;
Then:
SELECT * FROM inventory;
This provides a convenient reference to the original object.
17. Synonym for an Orders Table
CREATE SYNONYM orders
FOR customer_orders;
Now an application can use:
SELECT * FROM orders;
instead of repeatedly typing the longer object name.
18. Synonym for a Payroll Object
CREATE SYNONYM payroll
FOR employee_payroll;
This can simplify references to a payroll-related object where synonyms are supported.
19. Synonym for a Product Table
CREATE SYNONYM products
FOR company_product_catalog;
Then:
SELECT * FROM products;
The synonym gives users a simple name for the catalog object.
20. Synonym for a Long Schema-and-Object Reference
Without a synonym, a query might look like:
SELECT * FROM finance_2026.customer_transaction_archive;
A synonym could provide:
CREATE SYNONYM txn_archive
FOR finance_2026.customer_transaction_archive;
The query becomes:
SELECT * FROM txn_archive;
21. Synonym in Oracle Database
Oracle provides dedicated support for synonyms.
A basic Oracle example is:
CREATE SYNONYM emp FOR hr.employees;
You can then query:
SELECT * FROM emp;
Oracle supports both private and public synonyms.
22. Public Synonym in Oracle
An Oracle administrator can create a public synonym:
CREATE PUBLIC SYNONYM emp
FOR hr.employees;
The availability of the underlying object still depends on the permissions required to access it.
23. Synonym in SQL Server
Microsoft SQL Server also supports synonyms.
Example:
CREATE SYNONYM emp
FOR dbo.employees;
The synonym can then be referenced as:
SELECT * FROM emp;
SQL Server synonyms can also be useful for references involving different databases or servers when the supported naming requirements are satisfied.
24. Dropping a SQL Server Synonym
A SQL Server synonym can be removed with:
DROP SYNONYM emp;
Dropping a synonym removes the synonym object, not the underlying table.
25. MySQL Query Alias Instead of a Synonym
MySQL does not provide a direct CREATE SYNONYM feature equivalent to Oracle or SQL Server.
For a query-level shortcut, you can use an alias:
SELECT *
FROM employees AS emp;
Here, emp is only an alias for that query. It is not a permanent synonym.
26. MySQL View as an Alternative
For a persistent database object, MySQL users can create a view:
CREATE VIEW employee_list AS
SELECT *
FROM employees;
The view can then be queried:
SELECT * FROM employee_list;
A view and a synonym are not the same thing. A view defines a stored query, while a synonym is an alternative reference to another object where supported.
27. Synonym for Simplifying Application Code
Consider an application that repeatedly references:
SELECT *
FROM company_database.customer_transaction_history;
A synonym can potentially simplify the reference:
SELECT *
FROM customer_transactions;
This can make application SQL more readable and reduce repeated long object names.
28. Synonym During Database Migration
Synonyms can sometimes help applications during database restructuring.
For example, if an application traditionally references a particular object name, a synonym may allow the application to use a stable name while the underlying object is located in another schema or database structure.
The exact behavior depends on the database platform and how the migration is designed.
29. Synonym for Simplifying Cross-Schema Access
Suppose a reporting user frequently accesses:
SELECT * FROM sales_data.monthly_sales;
A synonym could provide:
CREATE SYNONYM monthly_sales
FOR sales_data.monthly_sales;
Then the user can reference:
SELECT * FROM monthly_sales;
Appropriate permissions on the underlying object are still important.
30. Synonym for a Complex Database Environment
Large enterprise databases may contain many schemas and objects. A synonym can give applications or users a consistent, shorter object name.
For example:
CREATE SYNONYM customer_data
FOR enterprise.production.customer_information;
Instead of repeatedly using the longer qualified name, supported queries can reference:
SELECT * FROM customer_data;
This can improve readability in environments where synonym use is part of the database design.
Basic SQL Synonym Syntax
The general syntax on systems that support synonyms looks like:
CREATE SYNONYM synonym_name
FOR object_name;
For example:
CREATE SYNONYM emp
FOR employees;
The exact syntax can differ between database systems, so always check the documentation for the platform you are using.
Oracle Synonym Syntax
A basic Oracle synonym can be created with:
CREATE SYNONYM emp
FOR hr.employees;
A public synonym uses:
CREATE PUBLIC SYNONYM emp
FOR hr.employees;
To remove a private synonym:
DROP SYNONYM emp;
To remove a public synonym, the appropriate Oracle syntax and privileges are required.
SQL Server Synonym Syntax
SQL Server uses:
CREATE SYNONYM emp
FOR dbo.employees;
To remove it:
DROP SYNONYM emp;
SQL Server also supports synonyms for certain references involving other databases and servers.
Does MySQL Support Synonyms?
MySQL does not support an Oracle-style CREATE SYNONYM statement.
Instead, developers may use query aliases:
SELECT *
FROM employees AS emp;
or persistent views:
CREATE VIEW employee_list AS
SELECT *
FROM employees;
These approaches serve different purposes and should not be treated as exact replacements for SQL synonyms.
Types of SQL Synonyms
Private Synonym
A private synonym is associated with the schema or user context where it is created. It is useful when an alternative name is needed without making that name broadly available.
Public Synonym
A public synonym is available more broadly within a database environment, depending on the platform and permissions. Oracle provides public synonyms.
Public synonyms should be used carefully because naming conflicts and security considerations can arise.
How Do SQL Synonyms Work?
A synonym provides another name for an existing database object.
For example:
CREATE SYNONYM emp
FOR hr.employees;
When a supported query uses:
SELECT * FROM emp;
the database resolves emp to the referenced object.
The synonym does not duplicate the table’s rows. The original database object remains the source of the data.
Synonym vs Alias
A synonym and an alias may look similar, but they are different.
| Feature | Synonym | Alias |
|---|---|---|
| Database object | Yes, where supported | No |
| Usually persistent | Yes | No |
| Query-level scope | No | Yes |
| Can simplify object names | Yes | Yes |
| Requires database support | Yes | Generally supported in queries |
| Example | CREATE SYNONYM emp... | FROM employees AS emp |
For example, this alias exists only for the query:
SELECT *
FROM employees AS emp;
A synonym is created as a database object:
CREATE SYNONYM emp
FOR employees;
Synonym vs View
Synonyms and views are also different.
A synonym provides an alternative name for an existing object.
A view is a database object based on a stored query.
Example synonym:
CREATE SYNONYM emp
FOR employees;
Example view:
CREATE VIEW active_employees AS
SELECT *
FROM employees
WHERE status = 'Active';
The view can define filtering, joins, selected columns, and other query logic. A synonym does not create a new query definition in this way.
Advantages of SQL Synonyms
SQL synonyms can provide several practical benefits.
Shorter Object Names
Long schema and object names can make SQL statements difficult to read. Synonyms can provide shorter references.
Better Readability
A well-designed synonym can make frequently used queries easier to understand.
Easier Application References
Applications may use a simple object name rather than repeatedly specifying a complex schema-qualified name.
Schema Abstraction
Synonyms can hide some details about where the underlying object is located.
Cross-Database References
On platforms that support them, synonyms can simplify references to objects in other databases or servers.
Easier Database Restructuring
In certain designs, synonyms can help maintain a consistent object reference while database structures change.
Limitations of SQL Synonyms
Synonyms are not always the right solution.
Database Support Varies
Oracle and SQL Server support synonyms, but MySQL does not provide the same feature.
Underlying Object Still Matters
A synonym does not create a copy of the original object. If the referenced object is unavailable or removed, the synonym may no longer work correctly.
Permission Problems
Users may have permission to see a synonym but still lack the required permissions on the underlying object.
Name Conflicts
Large databases can become confusing if many synonyms use generic names.
Debugging Can Be Harder
When an application references a synonym instead of the underlying object, developers may need to trace the synonym definition to understand what is actually being accessed.
When Should You Use SQL Synonyms?
Synonyms can be useful when:
- A database object has a long name.
- Users frequently access another schema’s object.
- Applications need simplified object references.
- A database uses cross-schema or cross-database references.
- An organization has a planned synonym naming strategy.
- Database administrators need an abstraction layer for object names.
They should be used as part of a clear database design rather than simply creating synonyms for every object.
SQL Synonym Permissions and Security
Creating and using synonyms can involve database permissions.
For example, having access to a synonym does not automatically mean a user has unrestricted access to the underlying table. The required permissions depend on the database platform, object, ownership, and security configuration.
Public synonyms require particular care because they can make names broadly available within the database environment.
For sensitive data, database administrators should define permissions on the underlying objects and avoid assuming that a synonym itself provides security.
Common SQL Synonym Problems
Synonym Exists but Query Fails
A synonym can exist while the underlying object is missing, inaccessible, or incorrectly referenced.
Permission Denied
The user may have insufficient privileges to access the underlying object.
Wrong Schema
A synonym may point to an object in a different schema than expected.
Object Was Deleted
If the underlying table, view, or other referenced object is removed, the synonym may become unusable.
Database-Specific Syntax
A statement that works in Oracle may not work in SQL Server or MySQL. Always use syntax appropriate for your database system.
Frequently Asked Questions
What is a synonym in SQL?
A synonym is an alternative name for a database object. It can provide a shorter or more convenient reference to an existing object without copying its data.
What is the purpose of a synonym in SQL?
The main purpose is to simplify references to database objects. Synonyms can reduce long object names, simplify cross-schema references, and make some application queries easier to maintain.
Does MySQL support SQL synonyms?
MySQL does not support the Oracle-style CREATE SYNONYM statement. Developers can use query aliases or views depending on the requirement.
What is the difference between a synonym and an alias?
A synonym is a persistent database object on systems that support it. An alias is generally a temporary name used within a query.
What is a public synonym?
A public synonym is an alternative object name that can be made available broadly within a database environment. Oracle supports public synonyms.
Can a synonym point to a table?
Yes. On database platforms that support synonyms for tables, a synonym can provide an alternative name for a table.
Does a synonym store data?
No. A synonym does not store a separate copy of the underlying table’s data. It provides another name or reference to the existing object.
Can a synonym be deleted?
Yes. Synonyms can be removed using the appropriate DROP SYNONYM syntax on database systems that support the feature.
Conclusion
SQL synonyms provide an alternative way to reference database objects and can make database code shorter and easier to manage. They are particularly useful when object names are long, when users frequently access objects in another schema, or when an application needs a simpler reference.
Oracle Database and Microsoft SQL Server provide dedicated synonym functionality, while MySQL does not provide an equivalent CREATE SYNONYM feature. MySQL developers can instead use query aliases or views when those approaches match the requirement.
Before using synonyms, consider permissions, naming conventions, maintainability, database compatibility, and the relationship between the synonym and its underlying object. When used carefully, synonyms can be a useful part of database organization and application development.
