Rename in DBMS

In a query when same tables from different schema or same schema are used, then it is necessary to differentiate their names in the query. Otherwise compiler will not understand which column or conditions are applicable to which tables. In such cases we need to use alias names for the tables to differentiate them.

Consider an example of retrieving manager ID and his name from EMPLOYEE table for a given employee. This query involves EMPLOYEE table two times -once to pull the name of manager id for the given employee and second time to retrieve the name of the manager.

SELECT emp.EMP_ID, mgr.EMP_ID as MGR_ID, mgr.EMP_NAME AS MANAGER_NAME
FROM EMPLOYEE emp, EMPLOYEE mgr
WHERE emp.MGR_ID = mgr.EMP_ID
AND emp.EMP_ID = 100;

Here we can notice the EMPLOYEE table is renamed as emp and mgr respectively. Now the compiler will differentiate these two tables clearly from each other, and prefixing these names to the columns makes it even clearer to it. In addition, we can see renaming of the columns here, which will help the user to differentiate the columns as employee id column and manager columns.

Usually it is best practice to write column names prefixed by schema and table names, at least table names should be added if the queries are executed involves single schema objects. In such case instead of writing the whole table name we can write the smaller names like we saw in above example.

Translate ยป