如何使用 T-SQL 的 Exists 关键字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/912513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use T-SQL's Exists keyword?
提问by Malfist
I have a query I want to run as a subquery that will return a set of FK's. With them I want to return only rows that has a matching key.
我有一个查询，我想作为子查询运行，它将返回一组 FK。有了它们，我只想返回具有匹配键的行。
Subquery:
子查询：
SELECT ID
FROM tblTenantTransCode
WHERE
tblTenantTransCode.CheckbookCode =
(SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income')
That will return all the transaction codes that have a checkbook code that matches Rent Income
这将返回所有具有与租金收入匹配的支票簿代码的交易代码
Now I want to select All Transactions where their transactioncode matches an ID returned in the subquery. I've gotten this far, but SQL Server complains of a syntax error. How can I do this?
现在我想选择其事务代码与子查询中返回的 ID 匹配的所有事务。我已经到了这一步，但 SQL Server 抱怨语法错误。我怎样才能做到这一点？
Full Query:
完整查询：
SELECT *
FROM tblTransaction
WHERE
tblTransaction.TransactionCode IN
(SELECT ID FROM tblTenantTransCode
WHERE tblTenantTransCode.CheckbookCode =
(SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income'))
Tables:
表格：
tblCheckbookCode
ID
Description
Other Info
tblTenantTransCode
ID
CheckbookCode <-- fk we're looking for
in the tblCheckbookCode.
We're selecting only checkbook codes
that have the Description 'Rent Income'
Other Info
tblTransactions
ID
TransactionCode <-- fk to tenant transaction code.
We're looking for an ID that is returned
in the above query/join
回答by spencer7593
To answer your question about using the EXISTSkeyword, here is an example query that uses an EXISTS predicate, based on the query as currently given in your question.
为了回答您关于使用EXISTS关键字的问题，这里有一个使用 EXISTS 谓词的示例查询，基于您的问题中当前给出的查询。
SELECT t.*
FROM tblTransaction t
WHERE EXISTS
(
SELECT 1
FROM tblTenantTransCode ttc
JOIN tblCheckbookCode cc
ON (cc.ID = ttc.CheckbookCode AND cc.Description='Rent Income')
WHERE ttc.ID = t.TransactionCode
)
Additional Details:
额外细节：
We all recognize that there are a variety of SQL statements that will return the result set that meets the specified requirements. And there are likely going to be differences in the observed performance of those queries. Performance is particularly dependent on the DBMS, the optimizer mode, the query plan, and the statistics (number of rows and data value distribution).
我们都认识到，有多种 SQL 语句会返回满足指定要求的结果集。观察到的这些查询的性能可能会有所不同。性能尤其依赖于 DBMS、优化器模式、查询计划和统计信息（行数和数据值分布）。
One advantage of the EXISTSis that it makes clear that we aren't interested returning any expressions from tables in the subquery. It serves to logically separate the subquery from the outer query, in a way that a JOINdoes not.
的一个优点EXISTS是它清楚地表明我们对从子查询中的表返回任何表达式不感兴趣。它用于在逻辑上将子查询与外部查询分开，而 aJOIN则没有。
Another advantage of using EXISTSis that avoids returning duplicate rows that would be (might be) returned if we were to instead use a JOIN.
using 的另一个优点EXISTS是避免返回重复的行，如果我们改为使用JOIN.
An EXISTSpredicate can be used to test for the existence of any related row in a child table, without requiring a join. As an example, the following query returns a set of all orders that have at least one associated line_item:
一个EXISTS谓语可用于测试的子表的任何相关行的存在，而不需要加入。例如，以下查询返回一组至少具有一个关联 line_item 的所有订单：
SELECT o.*
FROM order o
WHERE EXISTS
( SELECT 1
FROM line_item li
WHERE li.order_id = o.id
)
Note that the subquery doesn't need to find ALL matching line items, it only needs to find one row in order to satisfy the condition. (If we were to write this query as a JOIN, then we would return duplicate rows whenever an order had more than one line item.)
请注意，子查询不需要查找所有匹配的行项目，它只需要查找一行即可满足条件。（如果我们将此查询编写为 a JOIN，那么只要订单有多个行项目，我们就会返回重复的行。）
A NOT EXISTSpredicate is also useful, for example, to return a set of orders that do nothave any associated line_items.
一个NOT EXISTS谓语也是有用的，例如，返回一组做的订单不会有任何相关line_items。
SELECT o.*
FROM order o
WHERE NOT EXISTS
( SELECT 1
FROM line_item li
WHERE li.order_id = o.id
)
Of course, NOT EXISTSis just one alternative. An equivalent result set could be obtained using an OUTER join and an IS NULL test (assuming we have at least one expression available from the line_item table that is NOT NULL)
当然，NOT EXISTS只是一种选择。可以使用 OUTER join 和 IS NULL 测试获得等效的结果集（假设我们至少有一个来自 line_item 表的非空表达式）
SELECT o.*
FROM order o
LEFT
JOIN line_item li ON (li.order_id = o.id)
WHERE li.id IS NULL
There seems to be a lot of discussion (relating to answers to the original question) about needing to use an INpredicate, or needing to use a JOIN.
似乎有很多关于需要使用IN谓词或需要使用JOIN.
Those constructs are alternatives, but aren't necessary. The required result set can be returned by a query without using an INand without using a JOIN. The result set can be returned with a query that uses an EXISTSpredicate. (Note that the title of the OP question did ask about how to use the EXISTSkeyword.)
这些构造是替代品，但不是必需的。查询可以在不使用IN和 不使用 的情况下返回所需的结果集JOIN。结果集可以通过使用EXISTS谓词的查询返回。（请注意，OP 问题的标题确实询问了如何使用EXISTS关键字。）
Here is another alternative query (this is not my first choice), but the result set returned does satisfy the specified requirements:
这是另一个替代查询（这不是我的首选），但返回的结果集确实满足指定的要求：
SELECT t.*
FROM tblTransaction t
WHERE EXISTS
(
SELECT 1
FROM tblTenantTransCode ttc
WHERE ttc.ID = t.TransactionCode
AND EXISTS
(
SELECT 1
FROM tblCheckbookCode cc
WHERE cc.ID = ttc.CheckbookCode
AND cc.Description = 'Rent Income'
)
)
Of primary importance, the query should return a correct result set, one that satisfies the specified requirements, given all possible sets of conditions.
最重要的是，在给定所有可能的条件集的情况下，查询应该返回一个正确的结果集，该结果集满足指定的要求。
Some of the queries presented as answers here do NOTreturn the requested result set, or if they do, they happen to do so by accident. Some of the queries will work if we pre-assume something about the data, such that some columns are UNIQUEand NOT NULL.
此处作为答案呈现的某些查询不返回请求的结果集，或者如果返回，则它们碰巧这样做了。如果我们预先假设有关数据的某些内容，例如某些列是UNIQUE和，则某些查询将起作用NOT NULL。
Performance differences
性能差异
Sometimes a query with an EXISTSpredicate will not perform as well as a query with a JOINor an INpredicate. In some cases, it may perform better. (With the EXISTSpredicate, the subquery only has to find one row that satisfies the condition, rather than finding ALL matching rows, as would be required by a JOIN.)
有时，带有EXISTS谓词的查询的性能不如带有 aJOIN或IN谓词的查询 。在某些情况下，它可能会表现得更好。（使用EXISTS谓词，子查询只需找到满足条件的一行，而不是像 . 那样查找所有匹配的行JOIN。）
Performance of various query options is best gauged by observation.
各种查询选项的性能最好通过观察来衡量。
回答by Austin Salonen
You are describing an inner join.
您正在描述内部联接。
select tc.id
from tblTenantTransCode tc
inner join tblCheckbookCode cc on tc.CheckbookCode = cc.CheckbookCode
EDIT: It's still an inner join. I don't see any reason yet to use the IN clause.
编辑：它仍然是一个内部连接。我还没有看到任何使用 IN 子句的理由。
select *
from tblTransaction t
inner join tblTenantTransCode tc on tc.id = t.TransactionCode
inner join tblCheckbookCode cc on cc.id = tc.CheckbookCode
where cc.description = 'Rent Income'
EDIT: If you must use the EXISTS predicate to solve this problem, see @spencer7953's answer. However, from what I'm seeing, the solution above is simpler and there are assumptions of uniqueness based on the fact that "Subquery" works for you (it wouldn't 100% of the time if there wasn't uniqueness in that table). I'm also addressing
编辑：如果您必须使用 EXISTS 谓词来解决此问题，请参阅 @spencer7953 的答案。但是，从我所看到的，上面的解决方案更简单，并且基于“子查询”对您有用的事实存在唯一性假设（如果该表中没有唯一性，则不会 100% 的时间）。我也在致辞
Now I want to select All Transactions where their transactioncode matchesan ID returned in the subquery
现在我想选择其事务代码与子查询中返回的 ID匹配的所有事务
in my answer. If the request were something on the lines of this:
在我的回答中。如果请求是这样的：
Now I want to select All Transcations when anytransactioncode matches an ID returned in the subquery.
现在我想在任何事务代码与子查询中返回的 ID 匹配时选择所有 事务。
I would use EXISTS to see if any transactioncode existed in the child table and return every row or none as appropriate.
我将使用 EXISTS 来查看子表中是否存在任何事务代码，并根据需要返回每一行或不返回任何行。
回答by Eric
Given your full query, this query will get you where you need to go using a single join.
鉴于您的完整查询，此查询将使用单个连接将您带到需要去的地方。
The join filters out any transaction that doesn't have a transaction code of 'Rent Income.' It will take all record from the first table, build out the subset of the second table (that WHERE clause limits the records), and then filters the first table where those table math the join condition.
联接过滤掉任何没有“租金收入”交易代码的交易。它将从第一个表中获取所有记录，构建第二个表的子集（WHERE 子句限制记录），然后过滤第一个表，其中这些表对连接条件进行数学运算。
SELECT
t.*
FROM
tblTransaction t
INNER JOIN tblTenantTransCode c ON
t.TransactionCode = c.ID
INNER JOIN tblCheckbookCode chk ON
c.CheckbookCode = chk.ID
WHERE
chk.Description = 'Rent Income'
Edit: One other note: Avoid using SELECT * -- always specify the columns. Edit Dos: I missed that there were three tables. Corrected! Thanks, spencer!
编辑：另一个注意事项：避免使用 SELECT * - 始终指定列。编辑 Dos：我错过了三张桌子。更正！谢谢，斯宾塞！
回答by Justin Niessner
You need to use the 'IN' clause:
您需要使用“IN”子句：
select id from tblTenantTransCode
where tblTenantTransCode.CheckbookCode in
(select id from tblCheckbookCode
where description = 'rent income')
an inner join would probably be a better solution though...
不过，内部连接可能是更好的解决方案......
select ttc.id from tblTenantTransCode as ttc
inner join tblCheckbookCode as tcc
on ttc.CheckBookId = tcc.id
where tcc.description = 'rent income'
回答by KM.
Try this:
尝试这个：
SELECT
tblTenantTransCode.ID
FROM tblCheckbookCode
INNER JOIN tblTenantTransCode ON tblCheckbookCode.ID=tblTenantTransCode.CheckbookCode
WHERE tblCheckbookCode.Description = 'Rent Income'
Make sure you index tblCheckbookCode.Description.
确保您索引tblCheckbookCode.Description.
