Aliasing is used to rename the table or columns in the query. This type of renaming is temporary and has scope only when query is being executed. We can alias or rename query tables and columns using ‘AS’ keyword. Once table/columns are renamed, we can use those new names anywhere in that particular query.
In below query we can notice that we are retrieving all the columns from both the tables – BOOK_AUTHOR and BOOKS. But both the tables have ISBN. Hence if we say ISBN = ISBN in the WHERE clause, the compiler will not understand. It will show error message like below.
Hence we need append the table names before ISBN to indicate from which table it comes from like below :
But appending the full table name before each column will make query little complex and difficult to write. If we have some short and meaningful names, then it will be easier to write as well as easy to identify. Hence let us rename the tables like below and use them to identify the columns.
SELECT * FROM BOOK_AUTHOR ba, BOOKS b WHERE ba.ISBN = b.ISBN;
But here in the result we see two ISBNs – ISBN and ISBN_1 –one is from BOOK_AUTHOR and other is from BOOKS. But we cannot differentiate them from which table they are displayed. Hence it is necessary to give them correct names to differentiate them. (We can remove duplicate columns, but here let us retain to see how to rename them).
SELECT ba.AUTHOR_ID,
ba.ISBN AS BK_ATR_ISBN,
b.ISBN AS BOOKS_ISBN,
b.BOOK_NAME,
b.NUM_OF_PAGES,
b.PUBLISHER_ID
FROM BOOK_AUTHOR ba, BOOKS b
WHERE ba.ISBN = b.ISBN;
Now there is no confusion about the tables and columns to the compiler as well as the user.
Consider the below query. We have BOOK_LOAN table used two times here. Now the compiler has to understand when which of the two BOOK_LOANS are referred in the query. For the compiler, when it sees BOOK_LOAN two times in the query, it cannot understand one is to fetch Robert’s detail and other one is to fetch all other borrowers. Hence it is necessary to differentiate both the tables, and we have to rename the tables. While renaming the tables, we can ignore ‘AS’. The new names of the table can be used anywhere in the query like we used below. In addition, when columns with same names are listed in the SELECT list, we have to use table names appended to it so that compiler/user can understand from which table to retrieve the data. In addition, any columns listed in the SELECT list are the names specified in the table. They have meaning when it is used as a table’s column. But when used in a query as a user request, it might lose the meaning. For example, in below query if we use columns names as specified in the table, then we will have ISBN and BRWR_ID in the result set which user might not understand. Suppose we rename those columns to ROBERT_BOOKS and OTHER_BORROWERS, then it gives the meaning to any user about the result.
SELECT bl.ISBN AS ROBERT_BOOKS, bl_others.BRWR_ID OTHER_BORROWERS FROM BOOK_LOAN bl, BOOK_LOAN bl_others WHERE bl.ISBN = bl_others.ISBN AND bl.BRWR_ID= 'RBT_1000' AND bl.BRWR_ID! =bl_others.BRWR_ID;
