A97 SQL中的EXISTS运算符 - 我在A97帮助中找不到它的文档 [英] The EXISTS operator in A97 SQL - I cannot find documentation on it in A97 HELP
问题描述
Leigh Purvis先生给了我一个非常聪明的SQL来完成什么
可能是一个不常见的目标。在其中，他使用EXISTS操作符。
我在A97帮助中找不到关于它的文档。我想阅读更多关于这个有用的SQL运算符的
。建议？
SELECT CustID，OutType，EXISTS（SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence。 CustID AND tblC.OutType =" 01"）
AS
LogicalField FROM tblCorrespondence WHERE tblCorrespondence.OutType
=
" 05"
MLH写道：Leigh Purvis先生给了我一个非常聪明的人一块SQL来完成什么
可能是一个不常见的目标。在其中，他使用EXISTS操作符。
我在A97 HELP中找不到关于它的文档。我想更多地了解这个有用的SQL运算符。建议？
SELECT CustID，OutType，EXISTS（SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType =" 01"）
AS
LogicalField FROM tblCorrespondence WHERE tblCorrespondence.OutType
=
" 05"
http://msdn.microsoft.com/library /de...lce_exists.asp
看到最多人们使用exists（select * from ...），看起来资源好了b $ b饥饿但是SQL Server实际上并没有选择这个
实例中的所有列。不确定Access会做什么。
你必须自己测试，但你可能会发现使用DCount（）（或
我的tCount（） ）更有效地对抗Access表，YMMV。
什么是不常见的目标？
执行计划：哪个更有效？
---查询15 ---
*** *（由海报插入）****
SELECT *
FROM Table1
WHERE EXISTS（从Table2中选择数字WHERE Table1.ID = Table2.Number）;
****（结束插入）****
- 查询输入 -
表''表2''
表''表1''
- 查询的结束输入 -
01）排序表''表2''
02）使用临时索引将'加入表''表1''结果为''01）''
加入表达式Table1.ID = Table2.Number
---查询16 ---
****（海报插入）****
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.Number;
****（end insert）****
- 输入查询 -
表''表1''
使用索引''ID''
索引：
ID 10个条目，1个页面，10个值
有1列，固定，聚集和/或计数器
表''表2''
- 查询的结束输入 -
01）内部联接表''Table2''到表''Table1''
使用索引''表1！ID''
加入表达式Table2.Number = Table1.ID
----
我从不使用EXISTS，更喜欢JOINS。
Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what
is probably an uncommon objective. In it, he uses the EXISTS operator.
I can find no documentation on it in A97 HELP. I would like to read
more about this useful SQL operator. Suggestions?
SELECT CustID, OutType, EXISTS (SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType = "01")
AS
LogicalField FROM tblCorrespondence WHERE tblCorrespondence.OutType
=
"05"
MLH wrote:Mr Leigh Purvis gave me a very clever piece of SQL to accomplish what
is probably an uncommon objective. In it, he uses the EXISTS operator.
I can find no documentation on it in A97 HELP. I would like to read
more about this useful SQL operator. Suggestions?
SELECT CustID, OutType, EXISTS (SELECT CustID FROM tblCorrespondence
AS tblC
WHERE tblC.CustID = tblCorrespondence.CustID AND tblC.OutType = "01")
AS
LogicalField FROM tblCorrespondence WHERE tblCorrespondence.OutType
=
"05"
http://msdn.microsoft.com/library/de...lce_exists.asp
SQL Server does a great job of optimising this, IME Access does not, I
see most people use exists(select * from...), which looks resource
hungry but SQL Server doesn''t actually select all columns in this
instance. Not sure what Access does though.
You''ll have to test for yourself but you might find using DCount() (or
my tCount()) to be more efficient against Access tables, YMMV.
What is the "uncommon objective"?
Execution Plans: Which is more efficient?
--- Query15 ---
**** (inserted by poster) ****
SELECT *
FROM Table1
WHERE EXISTS(SELECT NUMBER FROM Table2 WHERE Table1.ID=Table2.Number);
**** (end insert) ****
- Inputs to Query -
Table ''Table2''
Table ''Table1''
- End inputs to Query -
01) Sort table ''Table2''
02) Semi Join table ''Table1'' to result of ''01)''
using temporary index
join expression "Table1.ID=Table2.Number"
--- Query16 ---
**** (inserted by poster) ****
SELECT *
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.Number;
**** (end insert) ****
- Inputs to Query -
Table ''Table1''
Using index ''ID''
Having Indexes:
ID 10 entries, 1 page, 10 values
which has 1 column, fixed, clustered and/or counter
Table ''Table2''
- End inputs to Query -
01) Inner Join table ''Table2'' to table ''Table1''
using index ''Table1!ID''
join expression "Table2.Number=Table1.ID"
----
I never use EXISTS, preferring JOINS.
这篇关于A97 SQL中的EXISTS运算符 - 我在A97帮助中找不到它的文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
