Could someone please explain the process involved with Exists

[blueyonder]

The statament below does exactly what I want it to do but I don't understand
why?

In my mind the subquery produces a result set which is a subset of the
handset table which the initial part of the query is then applied to.
However, the 'select all from Sim where it doesn't exists' is the bit I
don't get where what doesn't exist? how is the comparison made? I haven't
specified a parameter etc..

Any help would be much appreciated.
SELECT *
FROM Sim
WHERE (((Exists (SELECT * FROM Handsets WHERE Sim.Number =
Handsets.Number))=False));

[Rick Brandt]

blueyonder wrote:

The statament below does exactly what I want it to do but I don't
understand why?

In my mind the subquery produces a result set which is a subset of the
handset table which the initial part of the query is then applied to.
However, the 'select all from Sim where it doesn't exists' is the bit
I don't get where what doesn't exist? how is the comparison made? I
haven't specified a parameter etc..

Any help would be much appreciated.
SELECT *
FROM Sim
WHERE (((Exists (SELECT * FROM Handsets WHERE Sim.Number =
Handsets.Number))=False));

A query with an EXISTS clause simply reacts to whether the sub-query returns any
records. At least one record = "True" while zero records = "False". The outer
query does not "care" what the SQL statement of the inner query is. All it
cares about is whether it returns any records or not.

In your case the inner EXISTS will be true if the Sim number from the Sim table
can also be found in the Handsets table. Use of an EXISTS with "= False" is a
bit unusual. I would have used NOT EXISTS instead which would likely make more
sense to someone looking at the SQL.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[blueyonder]

Thank you Rick.

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:9g****************@newssvr25.news.prodigy.net ...

blueyonder wrote:

>The statament below does exactly what I want it to do but I don't
understand why?

In my mind the subquery produces a result set which is a subset of the
handset table which the initial part of the query is then applied to.
However, the 'select all from Sim where it doesn't exists' is the bit
I don't get where what doesn't exist? how is the comparison made? I
haven't specified a parameter etc..

Any help would be much appreciated.
SELECT *
FROM Sim
WHERE (((Exists (SELECT * FROM Handsets WHERE Sim.Number =
Handsets.Number))=False));

A query with an EXISTS clause simply reacts to whether the sub-query
returns any records. At least one record = "True" while zero records =
"False". The outer query does not "care" what the SQL statement of the
inner query is. All it cares about is whether it returns any records or
not.

In your case the inner EXISTS will be true if the Sim number from the Sim
table can also be found in the Handsets table. Use of an EXISTS with "=
False" is a bit unusual. I would have used NOT EXISTS instead which would
likely make more sense to someone looking at the SQL.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com