Table Aliasing

In the last query, the table names were specified in the Join condition because both the tables had the similar column name. In order to avoid any ambiguity in selecting the records the table aliasing must be used. The table alias in the last query is itself the table name. Any alias name can be specified for the table that will be valid for that query only. Refining the above instance,

SELECT ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno; # for display the employee name and department name by department no. of employee. #

The example provided below displays the products ordered along with the customer name and the product codes. This instance is a join of 4 tables that are: orders table, product table, ordprd table, customer table.

The subsequent example is a join from more than 2 tables.

SELECT op.pcode,pname,cname FROM product p,orders o,ordprd op,customer c
WHERE c.ccode=o.ccode and
 o.ocode=op.ocode and
op.pcode=p.pcode

Pcode   Pname            Cname
102        storewell          Mohan
102        storewell          Mohan
102        storewell          Mohan
103        cinthol old        Ramanath
103        cinthol old        Mohan
103        cinthol old        Sridhar
103        cinthol old        Ramanath
103        cinthol old        Mohan
104        1.5 ton a/c       Ramanath
105        1.0 ton a/c        Ramanath
106        0.75 ton a/c        Ramanath
106        0.75 ton a/c        Mohan
106        0.75 ton a/c        Sridhar
106        0.75 ton a/c        Ramanath
107        puf refrigerator    Ramanath
107        puf refrigerator    Ramanath
107        puf refrigerator    Ramesh
107        puf refrigerator    Mohan
108        hair dye            Ramesh
108         hair dye           Selvam