Thread: 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.