Google Translate

Valutazione attuale: 0 / 5

Stella inattivaStella inattivaStella inattivaStella inattivaStella inattiva
 

MSSQL e non so quali altri database permettono una Full Outer Join che in pratica prende tutti i records sia della tabella di sinistra che di destra. per intenderci e come se fosse contemporaneamente una left join e una right join.
Bene nel db2/400 sino alla 5.4 non esiste, tanto che IBM nel manuale dell'SQL V5R4 propone una soluzione per simulare la full outer join.

 

 

Vi riporto quanto trovato nella libreria··publib.boulder.ibm.com.

Simulating a full outer join

Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns nonmatching rows from both tables.

While DB2® UDB for iSeries™ does not support the full outer join syntax, you can simulate a full outer join by using a left outer join and a right exception join. Suppose that you want to find all employees and all of their projects. You want to see those employees that are not currently in charge of a project as well. The following query returns a list of all employees whose names are greater than 'S', along with their assigned project numbers:

1
2
3
4
5
6
7
8
9
10
11
/* full outer join */
SELECT EMPNO, LASTNAME, PROJNO 
FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S'
UNION
(SELECT EMPNO, LASTNAME, PROJNO
FROM CORPDATA.PROJECT EXCEPTION JOIN CORPDATA.EMPLOYEE
ON EMPNO = RESPEMP
WHERE
LASTNAME > 'S');

 

REFERENCES

Full Outer Join
- Alternative to...
iSeries Information Center, Versione 5 Release 4

- have fun -