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 -
