移到where子句的SQL条件产生差异

Question

查询1

select count(1)  
from sdb_snmp_sysdata s 
  left join sdb_snmp_entphysicaltable e on s.source = e.source  **and e.class = 3**
  left join SDB_DF_DEVICE_DNS dns on dns.source = s.source
  left join sdb_fdb_node f on upper(f.oldnodeid) = upper(dns.dns_name)
  where (regexp_like(s.descr, 'NFXS-F FANT-F ALCATEL-LUCENT|Motorola APEX3000')
  or regexp_like(e.descr, 'Motorola BSR64000 HD 100A Redundant Chassis|AS2511-RJ chassis')
  or trim(e.ModelName) in ('RFGW1', 'ARCT01949', 'ARCT03253', 'UBR10012', 'WS-C3750-48TS-S', 'WS-C3750V2-48TS-S')
  or e.name like '%Nexus5596 Chassis%')

查询2：

select count(1)  
    from sdb_snmp_sysdata s 
      left join sdb_snmp_entphysicaltable e on s.source = e.source 
      left join SDB_DF_DEVICE_DNS dns on dns.source = s.source
      left join sdb_fdb_node f on upper(f.oldnodeid) = upper(dns.dns_name)
      where (regexp_like(s.descr, 'NFXS-F FANT-F ALCATEL-LUCENT|Motorola APEX3000')
      or regexp_like(e.descr, 'Motorola BSR64000 HD 100A Redundant Chassis|AS2511-RJ chassis')
      or trim(e.ModelName) in ('RFGW1', 'ARCT01949', 'ARCT03253', 'UBR10012', 'WS-C3750-48TS-S', 'WS-C3750V2-48TS-S')
      or e.name like '%Nexus5596 Chassis%') **and e.class = 3**

以上两个查询通过更改e.class condition from on clause to where clause返回不同的行数。我搞不懂。任何帮助都是非常感谢的。

我的理解是:查询1使sysdata和entphysicaltable散列联接之间的左外部连接在对各个表进行全面扫描之后发生。在第二个查询中，join发生在entphysicaltable简化为仅包含entphysicaltable.class = 3的记录之后。

对我来说，查询同样有意义，但返回不同的结果。

我可以联想到这个question，我想知道一个具体的原因。

Answer 1

最好的解释是在一个小例子上。有两张桌子

TABLE A
        C1
----------
         1 
         2  

TABLE B         

        C1 C2
---------- -
         1 x

然后，ON子句中带有筛选器B.c2 = 'x'的查询返回2行

select * 
from A left outer join B
on A.c1 = B.c1 and B.c2 = 'x';

        C1         C1 C2
---------- ---------- --
         1          1 x  
         2              

当筛选器在WHERE子句中移动时，只传递一行

select * 
from A left outer join B
on A.c1 = B.c1 
WHERE B.c2 = 'x';


        C1         C1 C2
---------- ---------- --
         1          1 x 

WHERE子句simple重写了缺失的logik - wee外部联接行，它们都知道NULL不等于'x'，因此第二行被丢弃。

如果您在旧的连接语法结构(如B.c2(+) = 'x' )中看到，这是完全相同的主题。

Answer 2

如果我正确地阅读了你的问题，那么它就简单地归结为左联接是如何工作的。

(外部)左联接的工作方式是，它将连接左边的东西和右侧的东西。然后，作为一个外部联接，它将尝试向右侧添加空值，因为在右侧没有匹配。

但是，通过在WHERE子句中添加约束，您将告诉查询引擎过滤空行，因为它们将与where子句不匹配。如果ON子句中有过滤器-查询引擎将不会删除/过滤空行。这是因为联接后的WHERE是“执行”的。

这就是为什么得到不同的行数的原因，因为外部联接基于您是使用on子句还是WHERE子句的功能不同。因此，如果希望联接包含空行，则需要使用ON子句。