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"

[lylefair]

Whatever that means.

[Bob Quintal]

"lylefair" <ly******@yahoo .ca> wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.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.goo glegroups.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.in voice_no);

vs:

select distinct invoice_no from invoice , invoice_line inner
join invoice.invoice no = invoice_line.in voice_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.goo glegroups.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_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.

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.