• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1300
  • Last Modified:

Alternative to FULL OUTER JOIN

1) We have DB2 V5R3 on iseries AS/400.
2) FULL OUTER JOIN is not supported
3) There is a query we have written with repeating WHERE conditions for LEFT OUTER JOIN UNION RIGHT OUTER JOIN.
4) Can we not write in a single query without repeating WHERE conditions?
0
k_murli_krishna
Asked:
k_murli_krishna
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
4) no.
0
 
theo kouwenhovenCommented:
Maybe there is a workarond, please post hte whole SQL query here
0
 
k_murli_krishnaAuthor Commented:
SELECT e.EMPNO, e.ENAME, d.DEPTNO, d.DEPTNAME FROM EMP e LEFTOUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE e.ENAME = 'SCOTT' AND d.DEPTNAME = 'MECH' ORDER BY e.EMPNO
UNION
SELECT e.EMPNO, e.ENAME, d.DEPTNO, d.DEPTNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE e.ENAME = 'SCOTT' AND d.DEPTNAME = 'MECH' ORDER BY e.EMPNO;

Also:
SELECT e.EMPNO, e.ENAME, d.DEPTNO, d.DEPTNAME FROM EMP e LEFTOUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE e.ENAME = 'SCOTT' AND d.DEPTNAME = 'MECH' ORDER BY e.EMPNO
UNION
SELECT e.EMPNO, e.ENAME, d.DEPTNO, d.DEPTNAME FROM EMP e RIGHT OUTER JOIN DEPT d ON e.DEPTNO = d.DEPTNO WHERE e.ENAME = 'BRENT' AND d.DEPTNAME = 'ELEC' AND e.SAL > 8000 ORDER BY d.DEPTNO;

Maybe IN and NOT IN can be made use of. For multiple columns IN and NOT IN which was also not supported, joins were used as an alternative.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
momi_sabagCommented:
you could try to have a join with no join conditions (carthesian join) but your solution would probably run faster
0
 
k_murli_krishnaAuthor Commented:
But, momi, the results would be bloated and not what they should be. Suggest a correct solution else no idea/next to impossible/not possible like angelIIl from one of you is also good enough.
0
 
theo kouwenhovenCommented:
Did you tried what the performance is/was with a "not in (select....)" ?
0
 
k_murli_krishnaAuthor Commented:
Thanks, I will try now.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now