Production databases are often organized like the copy of the sample database shown in Figure 13-5, with all of their major tables collected together and owned by the database administrator. The database administrator gives other users permission to access the tables, using the SQL security scheme described in Chapter 15. Recall, however, that you must use qualified table names to refer to another user’s tables. In practice, this means that every query against the major tables in Figure 13-5 must use qualified table names, which makes queries like the following one long and tedious to type:
List the name, sales, office, and office sales for everyone.
SELECT NAME, OP_ADMIN.SALESREPS.SALES, OFFICE,
OP_ADMIN.OFFICES.SALES
FROM OP_ADMIN.SALESREPS, OP_ADMIN.OFFICES
To address this problem, many SQL DBMS products provide an alias or synonym capability. A synonym is a name that you define that stands for the name of some other table. In DB2, you create an alias using the CREATE ALIAS statement. (Older versions of DB2 actually used a CREATE SYNONYM statement, and Oracle still uses this form of the statement, but it has the same effect as the CREATE ALIAS statement.) If you were the user named George in Figure 13-5, for example, you might use this pair of CREATE ALIAS statements:
Create synonyms for two tables owned by another user.
CREATE ALIAS REPS
FOR OP_ADMIN.SALESREPS
CREATE ALIAS OFFICES
FOR OP_ADMIN.OFFICES
Once you have defined a synonym or alias, you can use it just like a table name in SQL queries. The previous query thus becomes:
SELECT NAME, REPS.SALES, OFFICE, OFFICES.SALES
FROM REPS, OFFICES
The use of aliases doesn’t change the meaning of the query, and you must still have permission to access the other users’ tables. Nonetheless, synonyms simplify the SQL statements you use and make it appear as if the tables were your own. If you decide later that you no longer want to use the synonyms, they can be removed with the DROP ALIAS statement:
Drop the synonyms created earlier.
DROP ALIAS REPS
DROP ALIAS OFFICES
Synonyms or aliases are supported by DB2, Oracle, and Informix. They are not specified by the ANSI/ISO SQL standard.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.