查询联接空值（左外部联接）

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.

我唯一可以看到的视图就是使用子查询分解（也称为CTE）或实际视图，并使用具有四个分支的联合来处理可能的情况：

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);

会得到：

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 

...但这很可怕，至少与您已有的工作相比。