不在访问查询中

发布于 2024-12-03 19:14:57 字数 253 浏览 1 评论 0原文

我在

tblLoc(LocCode) 下面有两个表 tblData(Item,LocCode)

在 tblData 中，存在在 tblLoc 中找不到的额外 LocCode。

SELECT D.LocCode
FROM tblData AS D
WHERE D.LocCode NOT IN (SELECT LocCode FROM tblLoc);

我使用这个查询。很慢。还有更好的查询吗？

原文

I have two tables below

tblLoc(LocCode)
tblData(Item,LocCode)

In tblData, there is extra LocCode that does not find in tblLoc.

SELECT D.LocCode
FROM tblData AS D
WHERE D.LocCode NOT IN (SELECT LocCode FROM tblLoc);

I use this query. It's slow. Is there any better query?

分享到QQ

分享到微博

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

发布评论

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

靑春怀旧 2024-12-10 19:14:57

在 tblData 和 tblLoc 之间的 LocCode 上使用 LEFT JOIN。将结果集限制为仅 tblLoc LocCode 为 Null 的行。如果您还没有 tblLoc 的 LocCode 索引，请添加一个索引。

SELECT d.LocCode
FROM
    tblData AS d
    LEFT JOIN tblLoc AS l
    ON d.LocCode = l.LocCode
WHERE l.LocCode Is Null;

Use a LEFT JOIN on LocCode between tblData and tblLoc. Restrict the result set to only those rows where tblLoc LocCode is Null. Add an index on LocCode for tblLoc, if you don't already have one.

SELECT d.LocCode
FROM
    tblData AS d
    LEFT JOIN tblLoc AS l
    ON d.LocCode = l.LocCode
WHERE l.LocCode Is Null;

回复收藏 0 原文

哽咽笑 2024-12-10 19:14:57

您所指的关系运算符称为半差或反连接。在 Access 中编写反连接的方法有很多种（ACE、Jet，等等）：除了你的和 @HansUp 的之外，还有其他几种：

SELECT D.LocCode
  FROM tblData AS D
 WHERE D.LocCode <> ALL (SELECT LocCode FROM tblLoc);

 SELECT D.LocCode
  FROM tblData AS D
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM tblLoc
                    WHERE D.LocCode = L.LocCode
                  );

我认为后者更好，因为 preciate 的参数在代码中很接近因此，作为一个人，我更容易阅读和理解。

HansUp 认为他们的更好，因为它应该比你的更快（但他们可能会和我一起敦促你始终使用典型的用例数据进行测试）。

您如何定义“更好”？

The relational operator you refer to is known as semi difference or anti join. There are arious way of writing an anti join in Access (ACE, Jet, whatever): in addition to yours and @HansUp's, here are a couple more:

SELECT D.LocCode
  FROM tblData AS D
 WHERE D.LocCode <> ALL (SELECT LocCode FROM tblLoc);

 SELECT D.LocCode
  FROM tblData AS D
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM tblLoc
                    WHERE D.LocCode = L.LocCode
                  );

I think the latter is better because the parameters to the preciate are proximate in the code and therefore easier for me as a human to read and understand.

HansUp considers theirs to be better because it should be faster than yours (but they would probably join me in urging you to always test using data typical to your use case).

How do you define 'better'?

回复收藏 0 原文

~没有更多了~