SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-58
Types of Join Queries
The next query specifies a left join operation. All data is preserved from the DEPT
table:
SELECT DT.DEPTNAME, E.EMP_LNAME, E.EMP_FNAME, DD.DEP_FNAME
FROM DEPT DT
LEFT JOIN EMPLOYEE E ON DT.DEPTNO = E.DEPTNO
LEFT JOIN DEPD DD ON E.EMPNO = DD.EMPNO ;
Now, the Personnel department, which has no employees, and the Accounting
department, which has one employee with no dependents, appear in the result table:
DEPTNAME EMP_LNAME EMP_FNAME DEP_FNAME
------------ -------------- --------------- -----------
Accounting Smith John ?
Maintenance Gray Tina William
Maintenance Gray Tina Gwendolyn
Personnel ? ? ?
--- 4 row(s) selected.
This left join operation displays this hierarchical relationship:
For another example of a left join operation, consider this query and its result table. All
data is preserved from the EMPLOYEE table. Note that the department without an
employee is not shown as this is not the relation requested:
SELECT DT.DEPTNAME, E.EMP_LNAME, E.EMP_FNAME, DD.DEP_FNAME
FROM EMPLOYEE E
LEFT JOIN DEPT DT ON E.DEPTNO = DT.DEPTNO
LEFT JOIN DEPD DD ON E.EMPNO = DD.EMPNO ;
DEPTNAME EMP_LNAME EMP_FNAME DEP_FNAME
------------ ------------ ------------- ------------
Accounting Smith John ?
Maintenance Gray Tina William
Maintenance Gray Tina Gwendolyn
--- 3 row(s) selected.
Department
Dependent
Employee
010