为什么 Teradata EXCEPT 不返回预期结果？

Question

为什么EXCEPT不返回预期结果？我觉得这应该有效，但它返回零行。然而，如果我对T3中不在t2中的订单构建的连接行进行计数，我会得到数千个结果，反之亦然。

人工审核

--first table (T3) without T2 match --178k
select count(*) --178K
from LAB3.Report_stg st3
left join LAB2.Report_stg  st2 
on st3.order_num = st2.order_num and st3.buid= st2.buid and st3.tie_nbr = st2.tie_num
where st2.tie_num is null
--second table (t2) without match in first (T3)
select count(*) --12.5M
from LAB2.Report_stg st2
left join LAB3.Report_stg st3
on st3.order_num = st2.order_num and st3.buid = st2.buid and st3.tie_num = st2.tie_num
where st3.tie_num is null

让我们试试EXCEPT。我预计这里有178k行，为零

--to avoid cartesian join
--select all mismatched tie_nums
--unless (EXCEPT if) that tie_num exists as a matched set between the two tables
select t3.order_num, t3.buid, t3.tie_num as t3_tie
from LAB3.Report_stg t3
left join LAB2.Report_stg t2
on t3.order_num = t2.order_num and t3.buid = t2.buid and t3.tie_num <> t2.tie_num
except
(select st3.order_num, st3.buid, st3.tie_num 
from LAB3.Report_stg st3
left join LAB2.Report_stg st2
on st3.order_num = st2.order_num and st3.buid = st2.buid and st3.tie_num = st2.tie_num
)

Answer 1

啊，我想通了！因此，子句的第二部分(EXCEPT SELECT内部(需要是一个INNER JOIN才能达到所需的效果。同样，我试图拉的是T3中相同T2订单上的所有平局，这些订单之间没有匹配。

--to avoid cartesian join
--select all mismatched tie_nums
--unless (EXCEPT if) that tie_num exists as a matched set between the two tables
select t3.order_num, t3.buid, t3.tie_num as t3_tie
from LAB3.Report_stg t3
left join LAB2.Report_stg t2
on t3.order_num = t2.order_num and t3.buid = t2.buid and t3.tie_num <> t2.tie_num
except
(select st3.order_num, st3.buid, st3.tie_num 
from LAB3.Report_stg st3
INNER join LAB2.Report_stg st2
on st3.order_num = st2.order_num and st3.buid = st2.buid and st3.tie_num = st2.tie_num
)

示例：T3|Order_Num|Tie_Num||--------|---------------||123|1||123|2||123|3||456|1||456|4|

T2|Order_Num|Tie_Num||--------|---------------||123|2||123|4||123|5||456|1||456|3|

使用上述查询的结果

T3|Order_Num|Tie_Num||--------|---------------||123|1||123|3||456|4|

所以，理想情况下，我会把这个和一个逆相结合，把T2中不在t3中的阶数联系到同一个结果集中。但这意味着我刚刚写了一本手册FULL OUTER JOINSMH