It is used when a table holds a relationship within itself, for example an employee table where MGRNO refers back to EMPNO of the manager.
The same table appears twice in the FROM clause, once for each role it plays.
Why two aliases are mandatory
Without two different aliases DB2 cannot tell the two copies of the table apart.
The aliases let you treat the same physical table as two logical tables, for example E for the employee and M for the manager.
Every column must then be qualified with the correct alias.
Example:-
-- List each employee with his manager's name
SELECT E.LASTNAME AS EMPLOYEE,
M.LASTNAME AS MANAGER
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MGRNO = M.EMPNO;
-- ANSI style with INNER JOIN
SELECT E.LASTNAME AS EMPLOYEE, M.LASTNAME AS MANAGER
FROM EMPLOYEE E INNER JOIN EMPLOYEE M
ON E.MGRNO = M.EMPNO;
Mainframe Tutorial
JCL Tutorial
Introduction
Mainframe Interview Q&A
Mainframe Quiz
Mainframe Memes
Contact us
About us
Backward
