A       B   C
1       1   1
NULL    1   2
NULL    2   1
2       2   2

A   B   C   D
1   1   1   X
2   1   1   Y
1   1   2   Z
2   1   2   X
1   2   1   Y
2   2   1   Z
1   2   2   X
2   2   2   Z

A       B   C     D
1       1   1     X
1       1   2     Z
1       2   1     Y
2       2   2     Y

SELECT TMP1.*, TMP2.D
FROM TMP1

LEFT JOIN TMP2
         ON CASE
             WHEN TMP1.A IS NOT NULL
                  THEN 'TMP1.A = TMP2.A
                       AND  TMP1.B = TMP2.B
                       AND  TMP1.C = TMP2.C'
             WHEN TMP1.A IS NULL
                  THEN 'TMP1.A = MIN(TMP2.A) OVER (PARTITION BY TMP2.B, TMP2.C)
                       AND  TMP1.B = TMP2.B
                       AND  TMP1.C = TMP2.C'
             END

SELECT TMP1.*, TMP2.D
FROM TMP1 LEFT JOIN
     TMP2
     ON (TMP1.A IS NOT NULL AND TMP1.A = TMP2.A AND TMP1.B = TMP2.B AND
         TMP1.C = TMP2.C
        ) OR
        (TMP1.A IS NULL AND TMP1.A = MIN(TMP2.A) OVER (PARTITION BY TMP2.B, TMP2.C) AND
         TMP1.B = TMP2.B AND TMP1.C = TMP2.C
        )

SELECT TMP1.*, TMP2.D
FROM TMP1 LEFT JOIN
     (SELECT TMP2.*,
             MIN(TMP2.A) OVER (PARTITION BY TMP2.B, TMP2.C) as MIN_A
      FROM TMP2
     ) TMP2
     ON (TMP1.A IS NOT NULL AND TMP1.A = TMP2.A AND TMP1.B = TMP2.B AND
         TMP1.C = TMP2.C
        ) OR
        (TMP1.A IS NULL AND TMP1.A = TMP2.MIN_A AND
         TMP1.B = TMP2.B AND TMP1.C = TMP2.C
        )