Query joining null values (left outer join)

Question

In continuation of what was asked on the post:

Ora SQL Query: joining without references

Now I have the following case:

The idea is to get the combination from TABLE_M by the inputs in TABLE_A.

For example. The record 1 (CODE = 1) in TABLE_A corresponds to COMBINATION1 while going through TABLE_Z and TABLE_X respectivelly...

The problem comes with combinations like 2, 3 or 4 (CODE in TABLE_A). These combinations do not have any matching value in TABLE_Z and TABLE_X so then the final result should be something like:

• TABLE_A (code2) ----> COMBINATION 7
• TABLE_A (code3) ----> COMBINATION 9
• TABLE_A (code4) ----> COMBINATION 8

I tried to achieve this by using OUTER JOINS but not able to get it succesfully... :(

SELECT A.REF_X,A.REF_Z, X.CODE,Z.CODE,M.DESCR 
    FROM TABLE_A A
    LEFT OUTER JOIN TABLE_Z Z
      ON A.REF_Z = Z.CODE
    LEFT OUTER JOIN TABLE_X X
      ON A.REF_X = X.CODE
    LEFT OUTER JOIN TABLE_M M
      ON Z.REF1 = M.Z_REF1
      AND Z.REF2 = M.Z_REF2
      AND Z.REF3 = M.Z_REF3
      AND X.REF1=M.X_REF1;

And resulting something like:

As per what is expected as result I should be able to get something like shown in picture but with the correct COMBINATION showm.

Which thing is failing on the query?

After that the idea would be also to have it in two independant views.

I guess that once I have the correct query I would be able to split it easilly. Something like a CORE_VIEW which would contain TABLE_Z, TABLE_X and TABLE_M and another view which would join TABLE_A and CORE_VIEW.

Then it would be easy for code reusability.

---

After Barry's comment I was able to generate a correct query:

SELECT A.REF_X,A.REF_Z, X.CODE,Z.CODE,M.DESCR 
FROM TABLE_A A
LEFT OUTER JOIN TABLE_Z Z
  ON  A.REF_Z = Z.CODE
LEFT OUTER JOIN TABLE_X X
  ON  A.REF_X = X.CODE
LEFT OUTER JOIN TABLE_M M
  ON  (Z.REF1 = M.Z_REF1 OR (Z.REF1 IS NULL AND M.Z_REF1 IS NULL))
  AND (Z.REF2 = M.Z_REF2 OR (Z.REF2 IS NULL AND M.Z_REF2 IS NULL))
  AND (Z.REF3 = M.Z_REF3 OR (Z.REF3 IS NULL AND M.Z_REF3 IS NULL))
  AND (X.REF1 = M.X_REF1 OR (X.REF1 IS NULL AND M.X_REF1 IS NULL));

This is giving me the expected results:

The problem now is that ass said above I need to split it into a view by having TABLE_Z , TABLE_M and TABLE_Y into a sepparate view. If I directly split the query I see that I loose the results that I was having before... (I have splitted the query like below in order to have T1 as my new VIEW and had to change the order of the JOINS as I was leaving JOINS without ON clause...)

SELECT A.REF_X,A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
LEFT OUTER JOIN 
    (SELECT X.CODE X_CODE,Z.CODE Z_CODE,M.DESCR  
    FROM TABLE_Z Z
    LEFT OUTER JOIN TABLE_M M
      ON  (Z.REF1 = M.Z_REF1 OR (Z.REF1 IS NULL AND M.Z_REF1 IS NULL))
      AND (Z.REF2 = M.Z_REF2 OR (Z.REF2 IS NULL AND M.Z_REF2 IS NULL))
      AND (Z.REF3 = M.Z_REF3 OR (Z.REF3 IS NULL AND M.Z_REF3 IS NULL))
    LEFT OUTER JOIN TABLE_X X  
      ON (X.REF1 = M.X_REF1 OR (X.REF1 IS NULL AND M.X_REF1 IS NULL))
    ) T1
ON   A.REF_X = T1.X_CODE
AND  A.REF_Z = T1.Z_CODE;

Is there any way I can split it into a sepparate View?

Answer 1

You can't really do quite what you're attempting because of the AND in your outer join condition. You're partly back to the null equivalence issue rBarryYoung pointed out, but if you do that null check in your outer join, rather than in the inline view (you don't if there as none of your TABLE_X/Z refs are null), you get too many records back. This gets 16 rows, the ones you want plus some garbage:

SELECT A.REF_X, A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
LEFT JOIN (
  SELECT X.CODE X_CODE,Z.CODE Z_CODE,M.DESCR
  FROM TABLE_M M
  LEFT JOIN TABLE_Z Z
  ON Z.REF1 = M.Z_REF1
  AND Z.REF2 = M.Z_REF2
  AND Z.REF3 = M.Z_REF3
  LEFT JOIN TABLE_X X
  ON X.REF1 = M.X_REF1
) T1
ON (T1.X_CODE = A.REF_X OR (T1.X_CODE IS NULL))
AND (T1.Z_CODE = A.REF_Z OR (T1.Z_CODE IS NULL));

If you just tried to split out the X_REF values:

SELECT M.CODE M_CODE, X.CODE X_CODE, M.DESCR
FROM TABLE_M M
LEFT JOIN TABLE_X X
ON X.REF1 = M.X_REF1

... you'd get 30 rows; and for X_CODE A you have four possible combinations, 1, 3, 5 or 7. For Z_REF it's similar:

SELECT M.CODE M_CODE, Z.CODE Z_CODE, M.DESCR
FROM TABLE_M M
LEFT JOIN TABLE_Z Z
ON Z.REF1 = M.Z_REF1
AND Z.REF2 = M.Z_REF2
AND Z.REF3 = M.Z_REF3

... gets 18 rows; and for Z_CODE Z you have three possible combinations, 1, 2 and 8. Now, you can compare those two lists and see that for the TABLE_A combination of A and Z the only overlapping combination is number 1, which is what you want.

But it breaks down because of the nulls. In the X_CODE list you get two null matches, for combinations 8 and 9. And in the Z_CODE list you get two null matches, for 7 and 9. Once you add the OR (T1.X_CODE IS NULL) and OR (T1.Z_CODE IS NULL) you get those as well, so for TABLE_A A and Z you get combinations 1 (where both A and Z match), 7 (A matches), 8 (Z matches) and 9 (neither matches).

And if you don't have the OR ... IS NULL conditions, you get the right answer when both TABLE_A columns match, but you don't get anything when either column doesn't match, as you saw in the results you included in the question. There isn't anything in between.

So you have to drive it from TABLE_A and join to TABLE_M via TABLE_X and TABLE_Z, as you're doing in your 'correct' query.

The only way I can see you could have that view is to use subquery factoring (a.k.a a CTE) or an actual view, and a union with four branches to handle the possible scenarios:

WITH T1 AS (
  SELECT X.CODE X_CODE,Z.CODE Z_CODE,M.DESCR
  FROM TABLE_M M
  LEFT JOIN TABLE_Z Z
  ON Z.REF1 = M.Z_REF1
  AND Z.REF2 = M.Z_REF2
  AND Z.REF3 = M.Z_REF3
  LEFT JOIN TABLE_X X
  ON X.REF1 = M.X_REF1
)
SELECT A.REF_X, A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
JOIN T1 ON T1.X_CODE = A.REF_X AND T1.Z_CODE = A.REF_Z
UNION ALL
SELECT A.REF_X, A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
JOIN T1 ON T1.X_CODE = A.REF_X AND T1.Z_CODE IS NULL
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE Z_CODE = A.REF_Z)
UNION ALL
SELECT A.REF_X, A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
JOIN T1 ON T1.Z_CODE = A.REF_Z AND T1.X_CODE IS NULL
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE X_CODE = A.REF_X)
UNION ALL
SELECT A.REF_X, A.REF_Z, T1.X_CODE, T1.Z_CODE, T1.DESCR
FROM TABLE_A A
JOIN T1 ON T1.Z_CODE IS NULL AND T1.X_CODE IS NULL
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE X_CODE = A.REF_X OR Z_CODE = A.REF_Z);

which does get:

REF_X REF_Z X_CODE Z_CODE DESCR       
----- ----- ------ ------ ------------
A     Z     A      Z      COMBINATION1 
C     Y     C      Y      COMBINATION4 
D     U     D      U      COMBINATION3 
F     W     F      W      COMBINATION6 
A     FFF   A             COMBINATION7 
TTT   T            T      COMBINATION8 
SSS   JJJ                 COMBINATION9 

... but that's pretty horrible, at least compared to what you already have working.