Re: Minus Operator with Missing Column
From: jimmyb <jimmybrock_at_gmail.com>
Date: Fri, 8 Jan 2010 16:07:26 -0800 (PST)
Message-ID: <6bc47ef3-f1e8-43e6-b3b8-c0e9fe66b30c_at_j4g2000yqe.googlegroups.com>
On Jan 8, 3:55 pm, vsevolod afanassiev <vsevolod.afanass..._at_gmail.com> wrote:
> The "minus" operator should be applied to "person_id" column and you
> are applying it to both "person_id" and "creation_date", this is the
> reason for getting 21247 records instead of 1399.
>
> Since tables are small performance shouldn't be an issue, so why not
> use
>
> select a.person_id, a.creation_date
> from tbl_A a
> where
> a.person_id not in
> (select b.person_id from tbl_B b);
Date: Fri, 8 Jan 2010 16:07:26 -0800 (PST)
Message-ID: <6bc47ef3-f1e8-43e6-b3b8-c0e9fe66b30c_at_j4g2000yqe.googlegroups.com>
On Jan 8, 3:55 pm, vsevolod afanassiev <vsevolod.afanass..._at_gmail.com> wrote:
> The "minus" operator should be applied to "person_id" column and you
> are applying it to both "person_id" and "creation_date", this is the
> reason for getting 21247 records instead of 1399.
>
> Since tables are small performance shouldn't be an issue, so why not
> use
>
> select a.person_id, a.creation_date
> from tbl_A a
> where
> a.person_id not in
> (select b.person_id from tbl_B b);
Actually, that is what I ended doing, except I used not exists...and got the results I wanted.
I'm guessing the MINUS operator works differently than a UNION when you having missing columns from one of the tables. Received on Fri Jan 08 2010 - 18:07:26 CST