查询嵌套查询结果中两列的位置 [英] Query where two columns are in the result of nested query

问题描述

我正在编写这样的查询:

I'm writing a query like this:

select * from myTable where X in (select X from Y) and XX in (select X from Y)

来自 X 和 XX 列的值必须在同一个查询的结果中:select X from Y.

Values from columns X and XX has to be in the result of the same query: select X from Y.

我认为这个查询被调用了两次，所以它毫无意义.有没有其他选择可以更有效地编写此查询?也许是临时表?

I think that this query is invoked twice so its senseless. Is there any other option I can write this query more efficiently? Maybe temp table?

推荐答案

实际上没有，考虑到 myTable.X 和 myTable.YY 匹配的 X 可能没有更聪明的方法来编写这个(无需访问 Y 两次)不能来自同一行.

Actually no, there isn't a smarter way to write this (without visiting Y twice) given the X that myTable.X and myTable.YY matches to may not be from the same row.

作为替代，查询的 EXISTS 形式是

As an alternative, the EXISTS form of the query is

select *
from myTable A
where exists (select * from Y where A.X = Y.X)
  and exists (select * from Y where A.XX = Y.X)

如果 Y 包含 1,2,3,4,5 的 X 值，并且 xx = 2 和 x.xx = 4，它们都存在(在 Y 中的不同记录上)并且 myTable 中的记录应显示在输出中.

If Y contains X values of 1,2,3,4,5, and x.x = 2 and x.xx = 4, they both exist (on different records in Y) and the record from myTable should be shown in output.

之前的这个答案指出您可以使用_EXISTS_子句重写它，这比_IN_工作得更快.正如 Martin 指出的那样，这不是真的(当然不适用于 SQL Server 2005 及更高版本).查看链接

This answer previously stated that You could rewrite this using _EXISTS_ clauses which will work faster than _IN_. AS Martin has pointed out, this is not true (certainly not for SQL Server 2005 and above). See links

• http://explainextended.com/2009/06/16/in-vs-join-vs-exists/
• http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/

这篇关于查询嵌套查询结果中两列的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！