代码之家 › 专栏 › 技术社区 › learningbyexample

“NOT EXISTS”能否仅通过交换句子来替换“NOT IN”?

db2-400 db2

learningbyexample · 技术社区 · 7 年前

当NOT-EXISTS与NOT-IN一起正确工作时,我如何使NOT-EXISTS在这里工作,并且我应该使用NOT-EXISTS得到相同的结果,但我不是。

我使用不存在句的逻辑有什么问题

NOT EXISTS应与A.C\U序列进行比较,如NOT IN。与NOT相同的逻辑,NOT为我提供了正确的记录。

Select A.C_SEQUENCE, A.STATUS 
FROM PROD.CONTROL A
where A.AID = 'BILLINGS'
and A.USER='GLOBAL_NETWORK'
--and A.STATUS = 'ON'
and NOT EXISTS
(
        select B.H_SEQUENCE
        from PROD.STATUS_R B, PROD.CONTROL A
        where A.USER='GLOBALNETWORK'
        and A.C_SEQUENCE = B.H_SEQUENCE 
        and B.H_STAT in('IGN','ACK')
)
order by C_date DESC limit 5000

Select A.C_SEQUENCE, A.STATUS 
FROM PROD.CONTROL A
where A.AID = 'BILLINGS'
and A.USER='GLOBAL_NETWORK'
--and A.STATUS = 'ON'
and A.C_SEQUENCE NOT IN 
(
        select B.H_SEQUENCE
        from PROD.STATUS_R B, PROD.CONTROL A
        where A.USER='GLOBALNETWORK'
        and A.C_SEQUENCE = B.H_SEQUENCE 
        and B.H_STAT in('IGN','ACK')
)
order by C_date DESC limit 5000

1 回复 | 直到 7 年前

jmarkmurphy 7 年前

如果没有样本数据,这只是一个偶然的机会,但我会写下 NOT EXISTS 版本如下:

Select A.C_SEQUENCE, A.STATUS 
FROM PROD.CONTROL A
where A.AID = 'BILLINGS'
and A.USER='GLOBAL_NETWORK'
--and A.STATUS = 'ON'
and NOT EXISTS
(
        select *
        from PROD.STATUS_R B
        and A.C_SEQUENCE = B.H_SEQUENCE 
        and B.H_STAT in('IGN','ACK')
)
order by C_date DESC limit 5000

不存在 表示您只需要外部选择记录,其中子选择不返回记录。但实际上,由于子选择不包含太多外部选择中尚未包含的内容,因此最好这样编写:

Select A.C_SEQUENCE, A.STATUS 
FROM PROD.CONTROL A
  left exception join prod.status_r_b b
    on b.h_sequence = a.c_sequence
      and b.h_stat in ('IGN', 'ACK')
where A.AID = 'BILLINGS'
  and A.USER='GLOBAL_NETWORK'
--and A.STATUS = 'ON'
order by C_date DESC limit 5000

您还应该能够简化 NOT IN 版本如下:

Select A.C_SEQUENCE, A.STATUS 
FROM PROD.CONTROL A
where A.AID = 'BILLINGS'
and A.USER='GLOBAL_NETWORK'
--and A.STATUS = 'ON'
and A.C_SEQUENCE NOT IN 
(
        select B.H_SEQUENCE
        from PROD.STATUS_R B
        and B.H_STAT in('IGN','ACK')
)
order by C_date DESC limit 5000

在这种情况下 不在 确认可以返回行,但只能从中选择行 prod.control 其中序列号不在筛选的 prod.status_r 结果集。