Обсуждение: Questions about Exists-Not exists clause

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've got, imagine, the next two tables:

table A:

field1    char(3)
field2     varchar(50)
field3     numeric(6)


table B:

field4    date
field5    time
field6    numeric(6)


I need to know which rows in table A are not in table B, and the join
fields are the two numeric ones (field3 and field6).


I've been taking a look at the EXISTS statement, but I can't see how I
should do it.

Some advice??
Thanks in advance.

Josep Sànchez
~  [papapep]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/H8NE2vx52x0kyz4RAuy0AKCpjWhD04QaHFMICLx/ZWCdzaTfzgCgzHRv
ycw/mHdtnEhY09hgsxc3Qmw=
=SbwX
-----END PGP SIGNATURE-----

Am Don, 2003-07-24 um 13.30 schrieb papapep:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I've got, imagine, the next two tables:
>
> table A:
>
> field1    char(3)
> field2     varchar(50)
> field3     numeric(6)
>
>
> table B:
>
> field4    date
> field5    time
> field6    numeric(6)
>
>
> I need to know which rows in table A are not in table B, and the join
> fields are the two numeric ones (field3 and field6).

SELECT * FROM A WHERE NOT EXISTS (SELECT NULL FROM B WHERE
A.field3=B.field6);

>
> I've been taking a look at the EXISTS statement, but I can't see how I
> should do it.
>
> Some advice??
> Thanks in advance.

np
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

>  > I've got, imagine, the next two tables:
>  >
>  > table A:
>  >
>  > field1    char(3)
>  > field2    varchar(50)
>  > field3    numeric(6)
>  >
>  >
>  > table B:
>  >
>  > field4    date
>  > field5    time
>  > field6    numeric(6)
>  >
>  >
>  > I need to know which rows in table A are not in table B, and the join
>  > fields are the two numeric ones (field3 and field6).
>
>SELECT * FROM A WHERE NOT EXISTS (SELECT NULL FROM B WHERE
>A.field3=B.field6);

I did this recently, using
SELECT * FROM A WHERE field3 NOT IN (SELECT field6 FROM B)

Now if this is totally wrong tell me *gently*, it's the first
question I felt I was up to to answer :-)

Regards,

     Martin
--
<mailto:mac.com@nemo>
<http://www.mechintosh.com/>

Not wrong, but under 7.3.x it can be much slower then using 'not
exists' when the sub-select returns a long list of values.  I believe
the 7.4 fixes this so both will end up equivalent.

Avi

On Thursday, Jul 24, 2003, at 15:46 America/Chicago, M Spreij wrote:

>> SELECT * FROM A WHERE NOT EXISTS (SELECT NULL FROM B WHERE
>> A.field3=B.field6);
>
> I did this recently, using
> SELECT * FROM A WHERE field3 NOT IN (SELECT field6 FROM B)
>
> Now if this is totally wrong tell me *gently*, it's the first question
> I felt I was up to to answer :-)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Many, many thanks to all who have answered.
It works fine (and fast) with the:

SELECT * FROM A WHERE NOT EXISTS (SELECT NULL FROM B WHERE
A.field3=B.field6);

Josep Sànchez
~ [papapep]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/IQiv2vx52x0kyz4RAn4hAJ9snDZGCrmmU1NauvdoQqVDiHM1AQCgho8W
DkxWzW4jl7ddXa137oz/Im8=
=CMDZ
-----END PGP SIGNATURE-----

On Thu, Jul 24, 2003 at 22:46:36 +0200,
  M Spreij <nemo@mechintosh.com> wrote:
> >
> >SELECT * FROM A WHERE NOT EXISTS (SELECT NULL FROM B WHERE
> >A.field3=B.field6);
>
> I did this recently, using
> SELECT * FROM A WHERE field3 NOT IN (SELECT field6 FROM B)
>
> Now if this is totally wrong tell me *gently*, it's the first
> question I felt I was up to to answer :-)

If field6 can have null values these two queries aren't equivalent.
If field6 has a null value than for the second query the where clause
will never be true. It will either be false or unknown so that no rows
will be selected.