对于中的每一列，只需要1个join和 Table2 可能包含值的 'ALL' 天气状况 ON 条款应为：

... AND (Table1.columnName = Table2.columnName OR Table2.columnName = 'ALL') AND ...

而不仅仅是：

Table1.columnName = Table2.columnName

如果Hive不支持 OR 在 ON 条款，你可以用 CROSS JOIN 和一个 WHERE 条款：

SELECT ...
FROM Table1 CROSS JOIN Table2
WHERE (Table1.column1 = Table2.column1 OR Table2.column1 = 'ALL') 
   AND ...

可以将连接条件表示为：

from table1 t1 join
     table2 t2
     on (t2.Segment_Hierarchy_Level_1_Name = t1.Segment_Hierarchy_Level_1_Name or
         t2.Segment_Hierarchy_Level_1_Name = 'ALL'
        ) and
        (t2.Source_system = t1.Source_system or
         t2.Source_system = 'ALL'
        ) and
        (t2.Segment_Code = t1.Segment_Code or
         t2.Segment_Code = 'ALL'
        ) and
        . . .   -- repeat for remaining columns

不过，我怀疑表演会很糟糕。您似乎有一些列不包含 'ALL' . 你应该把那些删掉，然后用这个短语 JOIN 作为：

from table1 t1 join
     table2 t2
     on t2.Segment_Hierarchy_Level_1_Name = t1.Segment_Hierarchy_Level_1_Name and
        t2.Source_system = t1.Source_system and
        t2.RTM_Distribution_Channel = t1.RTM_Distribution_Channel and
        . . .  - non-wildcarded columns
        (t2.Segment_Code = t1.Segment_Code or
         t2.Segment_Code = 'ALL'
        ) and
        . . .   -- repeat for remaining wildcarded columns

初始连接条件应该对性能有所帮助。
编辑：
可以使用 where 对于 OR 条件：

from table1 t1 join
     table2 t2
     on t2.Segment_Hierarchy_Level_1_Name = t1.Segment_Hierarchy_Level_1_Name and
        t2.Source_system = t1.Source_system and
        t2.RTM_Distribution_Channel = t1.RTM_Distribution_Channel and
        . . .  - non-wildcarded columns
where (t2.Segment_Code = t1.Segment_Code or
        t2.Segment_Code = 'ALL'
       ) and
        . . .   -- repeat for remaining wildcarded columns

也就是说，我认为最近版本的hive确实支持 OR .