Impala - 存在（子查询）VS 0

These queries run in Impala.

这些查询在Impala中运行。

Two similar queries that should have the same result, but have two different results.

两个类似的查询应该具有相同的结果，但有两个不同的结果。

This query gets all the results expected (about 130 in my real case)

此查询获得预期的所有结果（在我的实际情况下约为130）

select field1, field2, concrete_date
from tableA a
where exists(select *
    from tableB b
    where b.field1 = a.field1
        and b.concrete_date > (a.concrete_date + interval -5 minutes) 
        and b.concrete_date < (a.concrete_date + interval  5 minutes) 
)

This query returns a little portion of results (about 10 in my real case)

此查询返回一小部分结果（在我的实际案例中约为10）

select field1, field2, concrete_date
from tableA a
where 0 < (select count(*)
    from tableB b
    where b.field1 = a.field1
        and b.concrete_date > (a.concrete_date + interval -5 minutes) 
        and b.concrete_date < (a.concrete_date + interval  5 minutes) 
)

Where is the difference between?? I can't see it...

两者之间的区别在哪里？我看不出来......

In my tests, if I take one concrete value of field1 from my first query (but that doesn't appear in the second query results) and force the subquery changing the 'a.concrete_date' with the date that corresponds to that field1, the second query returns the expected row ok

在我的测试中，如果我从第一个查询中获取field1的一个具体值（但是没有出现在第二个查询结果中）并强制子查询使用与该field1对应的日期更改'a.concrete_date'，第二个查询返回预期的行ok

select field1, field2, concrete_date
from tableA a
where 0 < (select count(*)
    from tableB b
    where b.field1 = 'XXXXX'
        and b.concrete_date > ('2017-01-01 00:00:00' + interval -5 minutes) 
        and b.concrete_date < ('2017-01-01 00:00:00' + interval  5 minutes) 
)

1 个解决方案

#1

where b.field1 = a.field2
where b.field1 = a.field1

其中b.field1 = a.field2其中b.field1 = a.field1

There's the difference.

有区别。

#1