Full outer join

A full outer join of A FULL OUTER JOIN B is equivalent to a (A LEFT OUTER JOIN B) UNION ALL (B LEFT EXCEPTION JOIN A). The following example illustrates the rewrite.

  SELECT EMPNO, LASTNAME, DEPTNAME 	 
   FROM CORPDATA.EMPLOYEE XXX  	
   FULL OUTER JOIN CORPDATA.DEPARTMENT YYY  
   ON XXX.WORKDEPT = YYY.DEPTNO 

This query is rewritten as the following:

 SELECT EMPNO, LASTNAME, DEPTNAME 	 
  FROM CORPDATA.EMPLOYEE XXX  	
  LEFT OUTER JOIN CORPDATA.DEPARTMENT YYY  	
  ON XXX.WORKDEPT = YYY.DEPTNO  
 UNION ALL 
 SELECT EMPNO, LASTNAME, DEPTNAME 	 
  FROM CORPDATA.DEPARTMENT YYY  	
  LEFT EXCEPTION JOIN CORPDATA.EMPLOYEE XXX  	
  ON XXX.WORKDEPT = YYY.DEPTNO 

A query with multiple FULL OUTER JOIN requests, such as A FULL OUTER JOIN B FULL OUTER JOIN C can quickly become complicated in this rewritten state. This complication is illustrated in the following example.

If not running in live data mode, the optimizer could facilitate performance both during optimization and runtime by encapsulating intermediate results in a temporary data object. This object can be optimized once and plugged into both the scanned and probed side of the rewrite. These shared temporary objects eliminate the need to make multiple passes through the specific tables to satisfy the request.

In this example, the result of the (A FULL OUTER JOIN B) is a candidate for encapsulation during its FULL OUTER join with C.

A FULL OUTER JOIN B  FULL OUTER JOIN C

This query is rewritten as the following:

 ((A LEFT OUTER JOIN B) UNION ALL (B LEFT EXCEPTION JOIN A)) LEFT OUTER JOIN C ) 
 UNION ALL  
 (C LEFT EXCEPTION JOIN ((A LEFT OUTER JOIN B) UNION ALL (B LEFT EXCEPTION JOIN A))

FULL OUTER implies that both sides of the join request can generate NULL values in the resulting answer set. Local selection in the WHERE clause of the query could result in the appropriate downgrade of the FULL OUTER to a LEFT OUTER or INNER JOIN.

If you want FULL OUTER JOIN behavior and local selection applied, specify the local selection in the ON clause of the FULL OUTER JOIN, or use common table expressions. For example:

 WITH TEMPEMP AS (SELECT * FROM CORPDATA.EMPLOYEE XXX WHERE SALARY > 10000)  
 SELECT EMPNO, LASTNAME, DEPTNAME 	 
   FROM TEMPEMP XXX  	
   FULL OUTER JOIN CORPDATA.DEPARTMENT YYY  
   ON XXX.WORKDEPT = YYY.DEPTNO