我对这个查询的结果感到非常困惑:
select count(*) from my_tab mt
where mt.stat = '2473'
and mt.name= 'Tom'
and exists (select * from company_users@colink.world cu,
personnel_records@colink.world pr
where cu.user_id = pr.user_id
and mt.name = pr.name
and mt.stat = cu.stat
)
收益率:1
在company_users@colink.world中有0条stat='2473'的记录,那么为什么它会为exists返回true?
如果我这样更改查询,它将返回0:
select count(*) from my_tab mt
where mt.stat = '2473'
and mt.name= 'Tom'
and exists (select * from company_users@colink.world cu,
personnel_records@colink.world pr
where cu.user_id = pr.user_id
and mt.name = pr.name
and cu.stat = '2473'
)
更新
好吧,这真的很奇怪。为了看看会发生什么,我从另一个数据库(数据库链接引用的数据库)执行了查询,它给出了不同的(正确的)结果。
select count(*) from my_tab@mylink.world mt
where mt.stat = '2473'
and mt.name= 'Tom'
and exists (select * from company_users cu,
personnel_records pr
where cu.user_id = pr.user_id
and mt.name = pr.name
and mt.stat = cu.stat
)
返回0(如预期)。