有其他方法可以编写此查询吗？

发布于 2024-09-04 21:19:39 字数 317 浏览 5 评论 0原文

我有表 A、B、C，其中 A 代表可以在 C 中存储零个或多个子项的项目。B 表只有 2 个外键来连接 A 和 C。

我有这个 sql 查询：

select * from A
where not exists (select * from B natural join C where B.id = A.id and C.value > 10);

其中表示：“给我的表 A 中的每个项目的所有子项目的值都小于 10。

有没有办法优化这个？有没有办法不使用 exists 运算符来编写这个？

原文

I have tables A, B, C, where A represents items which can have zero or more sub-items stored in C. B table only has 2 foreign keys to connect A and C.

I have this sql query:

select * from A
where not exists (select * from B natural join C where B.id = A.id and C.value > 10);

Which says: "Give me every item from table A where all sub-items have value less than 10.

Is there a way to optimize this? And is there a way to write this not using exists operator?

分享到QQ

分享到微博

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

发布评论

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

屌丝范 2024-09-11 21:19:39

可以使用三种常用方法来测试某个值是否在一个表中而不是另一个表中：

NOT EXISTS
NOT IN
LEFT JOIN ... WHERE ... IS NULL

您已经显示了第一个表的代码。这是第二个：

SELECT *
FROM A
WHERE id NOT IN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
)

使用左连接：

SELECT *
FROM A
LEFT JOIN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
) BC
ON A.id = BC.id
WHERE BC.id IS NULL

根据数据库类型和版本，三种不同的方法可能会产生具有不同性能特征的不同查询计划。

There are three commonly used ways to test if a value is in one table but not another:

NOT EXISTS
NOT IN
LEFT JOIN ... WHERE ... IS NULL

You have already shown code for the first. Here is the second:

SELECT *
FROM A
WHERE id NOT IN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
)

And with a left join:

SELECT *
FROM A
LEFT JOIN (
    SELECT b.id
    FROM B
    NATURAL JOIN C
    WHERE C.value > 10
) BC
ON A.id = BC.id
WHERE BC.id IS NULL

Depending on the database type and version, the three different methods can result in different query plans with different performance characteristics.

回复收藏 0 原文

~没有更多了~