The EXISTS operator in A97 SQL - I cannot find documentation on it in A97 HELP

[MLH]

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 tblCorresponden ce
AS tblC
WHERE tblC.CustID = tblCorresponden ce.CustID AND tblC.OutType = "01")
AS
LogicalField FROM tblCorresponden ce WHERE tblCorresponden ce.OutType
=
"05"

[Trevor Best]

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 tblCorresponden ce
AS tblC
WHERE tblC.CustID = tblCorresponden ce.CustID AND tblC.OutType = "01")
AS
LogicalField FROM tblCorresponden ce WHERE tblCorresponden ce.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.

[lylefair]

What is the "uncommon objective"?

[lylefair]

Execution Plans: Which is more efficient?

--- Query15 ---

**** (inserted by poster) ****
SELECT *
FROM Table1
WHERE EXISTS(SELECT NUMBER FROM Table2 WHERE Table1.ID=Table 2.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=Tabl e2.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.

[MLH]

Well, I was probably wrong in saying that. I thought it more
common for a query to extract say 3 rows from a 10 row table
displaying data from ONLY the 3 of the 10 records than to
extract the same 3 rows, but mention (with an extra field)
data from say another 4th and 5th row of the table being
examined.

What is the "uncommon objective"?

[Bri]

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?

It is in the help for 'Subqueries - SQL Subqueries' in the Index or look
for EXISTS in Find to get to the same place.

--
Bri

[MLH]

FOUND IT! Thank-you very much, Bri.

It is in the help for 'Subqueries - SQL Subqueries' in the Index or look
for EXISTS in Find to get to the same place.

A subquery is a SELECT statement nested inside a SELECT,
SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside
another subquery.

Syntax

You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement)
expression [NOT] IN (sqlstatement)
[NOT] EXISTS (sqlstatement)

A subquery has these parts:

Part Description
comparison An expression and a comparison operator that compares
the expression with the results of the subquery.
expression An expression for which the result set of the subquery
is searched.
sqlstatement A SELECT statement, following the same format and
rules as any other SELECT statement. It must be enclosed in
parentheses.

[Bob Quintal]

"lylefair" <ly******@yahoo .ca> wrote in
news:11******** *************@g 44g2000cwa.goog legroups.com:

Execution Plans: Which is more efficient?

--- Query15 ---

**** (inserted by poster) ****
SELECT *
FROM Table1
WHERE EXISTS(SELECT NUMBER FROM Table2 WHERE
Table1.ID=Table 2.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=Tabl e2.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.

Query16, which uses the join, will return as many rows as there
are in table 2. Repeat your plan with SELECT DISTINCT and you
may not find it as speedy.
--
Bob Quintal

PA is y I've altered my email address.

[lylefair]

No.
Query16 returns only one row with all the fields from both Table1 and
Table2. This row has both Table1.ID and Table2.Number = 9. This is the
only match that exists.

Query15 also returns one row, but only the fields of Table1 are
included.

[lylefair]

Reading the rest of your post: of course,this is not MY plan. It is
JET's compilation plan and manifests itself in showplan.out after
inserting this key in the registry:

[HKEY_LOCAL_MACH INE\SOFTWARE\Mi crosoft\Jet\4.0 \Engines\Debug]
"JETSHOWPLAN"=" ON"

And as speedy as what? I didn't say it was speedy and I don't know if
it's speedy. We were discussing whether or not JET optimizes The EXISTS
operator.
After looking at the compilation plan my guess is ... probably not.