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

[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=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.

[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*********************@g44g2000cwa.googlegro ups.com:

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.

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_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\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.

[lylefair]

Whatever that means.

[Bob Quintal]

"lylefair" <ly******@yahoo.ca> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

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.

Right. In the real world, there is often a need to return only
the rows from one table which meet a criterium which exists in
one or more rows of a second table. If there are more than one
row, a join will return duplicate values. Using EXISTS will
result in the query returning rows from table 1 only the number
of rows in table one which match the criterium.

Using the DISTINCT keyword in the query will also return only
the rows meeting the criterium, but that will be less efficient
as the database engine must create a scratch table then compress
that to the final result.

--
Bob Quintal

PA is y I've altered my email address.

[lylefair]

I think you misread what I said, for the second or third time in this
thread.

If you want to make some point about exists and joins then by all means
do it, but I would appreciate it if you did independently of the point
I am trying to make, which is that Exists in JET may be inefficient.

I don't want to do anything with these queries. I created them only to
see their JET compilation plan.

If you want to discuss you point then I ask:

Can you post in full a query string where EXISTS will do something more
effectively or efficiently than a JOIN? (assuming JET 4.0).

[Bob Quintal]

"lylefair" <ly******@yahoo.ca> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

I think you misread what I said, for the second or third time
in this thread.

If you want to make some point about exists and joins then by
all means do it, but I would appreciate it if you did
independently of the point I am trying to make, which is that
Exists in JET may be inefficient.

I don't want to do anything with these queries. I created them
only to see their JET compilation plan.

If you want to discuss you point then I ask:

Can you post in full a query string where EXISTS will do
something more effectively or efficiently than a JOIN?
(assuming JET 4.0).

I'll have to assume Jet 3.51, went back to Access '97 because of
crap in '2000.

select invoice_no from invoice where exists (select * from
invoice_line where shipped_date is null and invoice.invoice no=
invoice_line.invoice_no);

vs:

select distinct invoice_no from invoice , invoice_line inner
join invoice.invoice no = invoice_line.invoice_no where
invoice_line where shipped_date is null;


--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

"lylefair" <ly******@yahoo.ca> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

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_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\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.

ShowPlan never shows subquery optimizations -- it says somewhere in
the ShowPlan output, I believe, that subquery optimization is not
shown, that MS never got around to implementing it.

That is, if I'm remembering correctly.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

Yes, I think you are right.