内部查询是否可以?
我经常看到类似...
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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
你的例子还不错。最大的问题通常来自所谓的“相关子查询”的情况。此时子查询依赖于外部查询中的列。这些特别糟糕,因为实际上需要为潜在结果中的每一行重新运行子查询。
您可以使用联接和 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.它因数据库而异,特别是如果比较的列已
...,但通常如果您的查询不使用连接到的表中的列 - 您应该使用
IN 或
EXISTS
:如果子表有多个与父表记录相关的记录,则使用 JOIN(INNER 或 OUTER)可能会导致记录膨胀。如果您需要这些信息,那就没问题,但如果不需要,那么您需要使用
GROUP BY
或DISTINCT
来获取唯一值的结果集 - 这可能会让您付出代价当您查看查询成本时。EXISTS
虽然 EXISTS 子句看起来像相关子查询,但它们不会这样执行(RBAR:Row By Agonizing Row)。
EXISTS
根据提供的条件返回一个布尔值,并在第一个为 true 的实例时退出 - 这可以使其在处理子表中的重复项时比IN
更快。It varies from database to database, especially if the columns compared are
..., but generally if your query is not using columns from the table joined to -- you should be using either
IN
orEXISTS
: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
orDISTINCT
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 thanIN
when dealing with duplicates in a child table.您可以改为
JOIN
到“联系人”表:这将以下问题留给数据库:“我们是否应该查看所有联系人表并在各个列中找到所有“1”,或者执行其他操作?”你原来的 SQL 没有给它太多选择。
You could
JOIN
to the Contacts table instead: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.
此类查询最常见的术语是“子查询”。使用它们本身并没有什么错,而且可以让你的生活更轻松。然而,通过重写带有子查询的查询以使用 JOIN 来代替,通常可以提高性能,因为服务器可以找到优化。
在您的示例中,执行了三个查询:主 SELECT 查询和两个 SELECT 子查询。
就您而言,我相信 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.
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.