SQL查询-exists和in之间的区别

发布于 2024-10-17 19:13:31 字数 505 浏览 4 评论 0原文

以下两个查询总是给出相同的结果吗？如果没有，为什么不呢？

1）

select PayFrequencyTypeID, PayFrequencyDesc 
from db_uspaybo.tblpayfrequency 
where (PayFrequencyTypeID,1) not in (
   select payfrequencytype,1 
   from tblcustomerpayapproval 
   where fedtaxid='903008887' and payrollyear=year(curdate())
);

2）

select payfrequencytypeid 
from tblpayfrequency 
where not exists (
  select distinct payfrequencytype 
  from tblcustomerpayapproval
);

提前致谢。

原文

Do both the following queries always give the same results? If not, why not?

select PayFrequencyTypeID, PayFrequencyDesc 
from db_uspaybo.tblpayfrequency 
where (PayFrequencyTypeID,1) not in (
   select payfrequencytype,1 
   from tblcustomerpayapproval 
   where fedtaxid='903008887' and payrollyear=year(curdate())
);

select payfrequencytypeid 
from tblpayfrequency 
where not exists (
  select distinct payfrequencytype 
  from tblcustomerpayapproval
);

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最后的乘客 2024-10-24 19:13:31

我想这就是你想要的“不存在”
这里的A.Id和B.Id是连接这些表的Pk和FK。（我不知道确切的字段名称）

select payFrequencyTypeID from tblpayFrequency A
不存在的地方（选择 *
来自 tblcustomerpayapproval B，其中 A.Id=B.Id 和 B.fedtaxid='903008887' 和 B.payrollyear=year(curdate()))；

回复收藏 0 原文

爱她像谁 2024-10-24 19:13:31

基本上，not in 和 not contains 非常相似，通常会产生相同的结果。

不同之处在于，如果集合中的某个值是 NULL，in 将返回 false（至少在 Oracle 上是这样），而 exists 仅检查是否存在记录，不考虑其值。

在这种特定情况下，您得到的 WHERE 子句将导致第一个查询返回不同的结果。

第三种方法通常在 MySQL 上更快，是在主查询中左连接表并检查连接字段是否为 NULL：

select payfrequencytypeid 
from 
  tblpayfrequency f
  left join tblcustomerpayapproval a
    on a.payfrequencytype = f.payfrequencytype
where
  a.payfrequencytype IS NULL

其他一般提示：

当然，您可以跳过 1。
您不需要在第二个查询中使用 DISTINCT。如果删除该路径，则允许数据库选择最佳优化路径。
Notists 通常比 in 更快，尽管这也取决于数据库选择的优化路径。您确实应该在实时服务器和实时数据上尝试一下，以确保这一点。

Basically not in and not exists are very similar and usually yield the same result.

A difference is that in will return false if one of the values in the set is NULL (at least, it does on Oracle), while exists only checks for existance of a record, unregarding its values.

In this specific case, you got a WHERE clause that will cause the first query to return a different result.

A third approach which is generally faster on MySQL, is to left join the table in the main query and check if the join field is NULL:

select payfrequencytypeid 
from 
  tblpayfrequency f
  left join tblcustomerpayapproval a
    on a.payfrequencytype = f.payfrequencytype
where
  a.payfrequencytype IS NULL

Other general tips:

You can skip the 1 of course.
You don't need the DISTINCT in the second query. You allow the database to choose the best optimization path if you remove that.
Not exists is often faster in regards to in, although this also depends on the optimization path chosen by the database. You should really try this on a live server and live data to be sure.

回复收藏 0 原文

云巢 2024-10-24 19:13:31

简化和标记：

SELECT p, d 
FROM f 
WHERE (p, 1) NOT IN 
    (SELECT t, 1 
    FROM a 
    WHERE i='903008887' 
    AND y = year (curdate()));


SELECT p 
FROM f 
WHERE NOT EXISTS 
    (SELECT DISTINCT t 
    FROM a);

结果不会相同。原因：第一个查询要求 2 列。但是我们无法知道年份的问题或 903008887 的过滤是否有效果。如果该过滤有效果 - 第二个查询如何完成呢？

Simplified and tagged:

SELECT p, d 
FROM f 
WHERE (p, 1) NOT IN 
    (SELECT t, 1 
    FROM a 
    WHERE i='903008887' 
    AND y = year (curdate()));


SELECT p 
FROM f 
WHERE NOT EXISTS 
    (SELECT DISTINCT t 
    FROM a);

The result will not be the same. Reason: The first query asks for 2 columns. But we cannot know wheter the question for the year has some effect, or the filtering for 903008887. If that filtering has an effect - how could it be done by the second query?

回复收藏 0 原文