内部查询是否可以?

发布于 2024-11-18 23:00:52 字数 492 浏览 3 评论 0原文

我经常看到类似...

SELECT events.id, events.begin_on, events.name
  FROM events
 WHERE events.user_id IN ( SELECT contacts.user_id 
                             FROM contacts 
                            WHERE contacts.contact_id = '1')
   OR events.user_id IN ( SELECT contacts.contact_id 
                            FROM contacts 
                           WHERE contacts.user_id = '1')

可以在查询中进行查询吗?是“内心的询问”吗? “子查询”?它算作三个查询吗(我的例子)?如果这样做不好......我该如何重写我的例子?

I often see something like...

SELECT events.id, events.begin_on, events.name
  FROM events
 WHERE events.user_id IN ( SELECT contacts.user_id 
                             FROM contacts 
                            WHERE contacts.contact_id = '1')
   OR events.user_id IN ( SELECT contacts.contact_id 
                            FROM contacts 
                           WHERE contacts.user_id = '1')

Is it okay to have query in query? Is it "inner query"? "Sub-query"? Does it counts as three queries (my example)? If its bad to do so... how can I rewrite my example?

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

扫码二维码加入Web技术交流群

发布评论

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

评论(4

独享拥抱 2024-11-25 23:00:52

你的例子还不错。最大的问题通常来自所谓的“相关子查询”的情况。此时子查询依赖于外部查询中的列。这些特别糟糕,因为实际上需要为潜在结果中的每一行重新运行子查询。

您可以使用联接和 GROUP BY 重写子查询,但性能可能会有所不同,尤其取决于您的 RDBMS。

Your example isn't too bad. The biggest problems usually come from cases where there is what's called a "correlated subquery". That's when the subquery is dependent on a column from the outer query. These are particularly bad because the subquery effectively needs to be rerun for every row in the potential results.

You can rewrite your subqueries using joins and GROUP BY, but as you have it performance can vary, especially depending on your RDBMS.

始于初秋 2024-11-25 23:00:52

它因数据库而异,特别是如果比较的列已

  • 索引或不可
  • 为空或不可

...,但通常如果您的查询不使用连接到的表中的列 - 您应该使用 IN 或 EXISTS

SELECT e.id, e.begin_on, e.name
  FROM EVENTS e
 WHERE EXISTS (SELECT NULL
                 FROM CONTACTS c 
                WHERE ( c.contact_id = '1' AND c.user_id = e.user_id )
                   OR ( c.user_id = '1' AND c.contact_id = e.user_id )

如果子表有多个与父表记录相关的记录,则使用 JOIN(INNER 或 OUTER)可能会导致记录膨胀。如果您需要这些信息,那就没问题,但如果不需要,那么您需要使用 GROUP BYDISTINCT 来获取唯一值的结果集 - 这可能会让您付出代价当您查看查询成本时。

EXISTS

虽然 EXISTS 子句看起来像相关子查询,但它们不会这样执行(RBAR:Row By Agonizing Row)。 EXISTS 根据提供的条件返回一个布尔值,并在第一个为 true 的实例时退出 - 这可以使其在处理子表中的重复项时比 IN 更快。

It varies from database to database, especially if the columns compared are

  • indexed or not
  • nullable or not

..., but generally if your query is not using columns from the table joined to -- you should be using either IN or EXISTS:

SELECT e.id, e.begin_on, e.name
  FROM EVENTS e
 WHERE EXISTS (SELECT NULL
                 FROM CONTACTS c 
                WHERE ( c.contact_id = '1' AND c.user_id = e.user_id )
                   OR ( c.user_id = '1' AND c.contact_id = e.user_id )

Using a JOIN (INNER or OUTER) can inflate records if the child table has more than one record related to a parent table record. That's fine if you need that information, but if not then you need to use either GROUP BY or DISTINCT to get a result set of unique values -- and that can cost you when you review the query costs.

EXISTS

Though EXISTS clauses look like correlated subqueries, they do not execute as such (RBAR: Row By Agonizing Row). EXISTS returns a boolean based on the criteria provided, and exits on the first instance that is true -- this can make it faster than IN when dealing with duplicates in a child table.

寄居者 2024-11-25 23:00:52

您可以改为 JOIN 到“联系人”表:

SELECT events.id, events.begin_on, events.name
FROM events
JOIN contacts
ON (events.user_id = contacts.contact_id OR events.user_id = contacts.user_id)
WHERE events.user_id = '1'
GROUP BY events.id  
-- exercise: without the GROUP BY, how many duplicate rows can you end up with?

这将以下问题留给数据库:“我们是否应该查看所有联系人表并在各个列中找到所有“1”,或者执行其他操作?”你原来的 SQL 没有给它太多选择。

You could JOIN to the Contacts table instead:

SELECT events.id, events.begin_on, events.name
FROM events
JOIN contacts
ON (events.user_id = contacts.contact_id OR events.user_id = contacts.user_id)
WHERE events.user_id = '1'
GROUP BY events.id  
-- exercise: without the GROUP BY, how many duplicate rows can you end up with?

This leaves the following question up to the database: "Should we look through all the contacts table and find all the '1's in the various columns, or do something else?" where your original SQL didn't give it much choice.

用心笑 2024-11-25 23:00:52

此类查询最常见的术语是“子查询”。使用它们本身并没有什么错,而且可以让你的生活更轻松。然而,通过重写带有子查询的查询以使用 JOIN 来代替,通常可以提高性能,因为服务器可以找到优化。

在您的示例中,执行了三个查询:主 SELECT 查询和两个 SELECT 子查询。

SELECT events.id, events.begin_on, events.name
FROM events
JOIN contacts
ON (events.user_id = contacts.contact_id OR events.user_id = contacts.user_id)
WHERE events.user_id = '1'
GROUP BY events.id

就您而言,我相信 JOIN 版本会更好,因为您可以避免对联系人进行两次 SELECT 查询,而是选择 JOIN。

请参阅有关该主题的 mysql 文档

The most common term for this sort of query is "subquery." There is nothing inherently wrong in using them, and can make your life easier. However, performance can often be improved by rewriting queries w/ subqueries to use JOINs instead, because the server can find optimizations.

In your example, three queries are executed: the main SELECT query, and the two SELECT subqueries.

SELECT events.id, events.begin_on, events.name
FROM events
JOIN contacts
ON (events.user_id = contacts.contact_id OR events.user_id = contacts.user_id)
WHERE events.user_id = '1'
GROUP BY events.id

In your case, I believe the JOIN version will be better as you can avoid two SELECT queries on contacts, opting for the JOIN instead.

See the mysql docs on the topic.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文