pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find entries missing in 2nd table?
13+ messages / 8 participants
[nested] [flat]
* How to find entries missing in 2nd table?
@ 2006-07-11 14:19 alex-lists-pgsql@yuriev.com
0 siblings, 3 replies; 13+ messages in thread
From: alex-lists-pgsql@yuriev.com @ 2006-07-11 14:19 UTC (permalink / raw)
To: pgsql-sql
Hi,
I realize I probably lost my marbles but I've been having a god
awful time with a single query:
control:
....
....
controller_id pk;
datapack:
controller_id fk;
....
....
....
I need to get all entries from the table control that are not listed in
datapack.
Thanks,
Alex
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-11 15:52 Richard Broersma Jr <rabroersma@yahoo.com>
parent: alex-lists-pgsql@yuriev.com
2 siblings, 1 reply; 13+ messages in thread
From: Richard Broersma Jr @ 2006-07-11 15:52 UTC (permalink / raw)
To: pgsql-sql
> Hi,
> I realize I probably lost my marbles but I've been having a god
> awful time with a single query:
>
> control:
> ....
> ....
> controller_id pk;
>
>
> datapack:
>
> controller_id fk;
> ....
> ....
> ....
>
> I need to get all entries from the table control that are not listed in
> datapack.
SELECT C.CONTROLLER_ID
FROM CONTROL AS C
LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID)
WHERE D.CONTROLLER_ID IS NULL;
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-11 16:00 Scott Marlowe <smarlowe@g2switchworks.com>
parent: alex-lists-pgsql@yuriev.com
2 siblings, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2006-07-11 16:00 UTC (permalink / raw)
To: pgsql-sql
On Tue, 2006-07-11 at 09:19, alex-lists-pgsql@yuriev.com wrote:
> Hi,
> I realize I probably lost my marbles but I've been having a god
> awful time with a single query:
>
> control:
> ....
> ....
> controller_id pk;
>
>
> datapack:
>
> controller_id fk;
> ....
> ....
> ....
>
> I need to get all entries from the table control that are not listed in
> datapack.
OK, this is a pretty common problem. Easy solution is left join / null.
select t1.id from table1 t1 left join table table2 t2 on (t1.id=t2.id)
where t2.id is null
since a left join gives us all rows from the left table, and nulls where
the right table doesn't match up, and we're asking for all the rows
where t2.id is null, we'll get all the rows in t1 that don't have a
match in t2. cool, huh?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-11 16:04 Frank Bax <fbax@sympatico.ca>
parent: alex-lists-pgsql@yuriev.com
2 siblings, 0 replies; 13+ messages in thread
From: Frank Bax @ 2006-07-11 16:04 UTC (permalink / raw)
To: pgsql-sql
At 10:19 AM 7/11/06, alex-lists-pgsql@yuriev.com wrote:
>control:
> ....
> controller_id pk;
>
>datapack:
>
> controller_id fk;
>
>I need to get all entries from the table control that are not listed in
>datapack.
select controller.controller_id from controller
left join datapack on controller.controller_id = datapack.controller_id
where datapack.controller_id is null;
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-11 16:57 Milen A. Radev <milen@radev.net>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Milen A. Radev @ 2006-07-11 16:57 UTC (permalink / raw)
To: pgsql-sql
Richard Broersma Jr написа:
>> Hi,
>> I realize I probably lost my marbles but I've been having a god
>> awful time with a single query:
>>
>> control:
>> ....
>> ....
>> controller_id pk;
>>
>>
>> datapack:
>>
>> controller_id fk;
>> ....
>> ....
>> ....
>>
>> I need to get all entries from the table control that are not listed in
>> datapack.
>
> SELECT C.CONTROLLER_ID
>
> FROM CONTROL AS C
> LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID)
>
> WHERE D.CONTROLLER_ID IS NULL;
>
Or
(SELECT controller_id FROM control)
EXCEPT
(SELECT controller_id FROM datapack)
?
--
Milen A. Radev
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-11 17:03 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Broersma Jr @ 2006-07-11 17:03 UTC (permalink / raw)
To: pgsql-sql
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> > >> I need to get all entries from the table control that are not listed in
> > >> datapack.
> > >
> > > SELECT C.CONTROLLER_ID
> > >
> > > FROM CONTROL AS C
> > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID = D.CONTROLLER_ID)
> > >
> > > WHERE D.CONTROLLER_ID IS NULL;
> > >
> >
> >
> > Or
> > (SELECT controller_id FROM control)
> > EXCEPT
>
> Good point! But don't forget to include the list. :-)
>
> Regards,
>
> Richard Broersma Jr.
> > (SELECT controller_id FROM datapack)
> > ?
> >
> > --
> > Milen A. Radev
> >
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-12 08:06 Exner, Peter <Exner@his.de>
0 siblings, 2 replies; 13+ messages in thread
From: Exner, Peter @ 2006-07-12 08:06 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
Hi,
what about
SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT DISTINCT controller_id FROM datapack);
?
Regards
Peter
> -----Ursprüngliche Nachricht-----
> Von: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] Im Auftrag von
> Richard Broersma Jr
> Gesendet: Dienstag, 11. Juli 2006 19:04
> An: SQL Postgresql List
> Betreff: Re: [SQL] How to find entries missing in 2nd table?
>
>
>
> --- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> > > >> I need to get all entries from the table control that are not
> > > >> listed in datapack.
> > > >
> > > > SELECT C.CONTROLLER_ID
> > > >
> > > > FROM CONTROL AS C
> > > > LEFT JOIN DATAPACK AS D ON ( C.CONTROLLER_ID =
> D.CONTROLLER_ID)
> > > >
> > > > WHERE D.CONTROLLER_ID IS NULL;
> > > >
> > >
> > >
> > > Or
> > > (SELECT controller_id FROM control)
> > > EXCEPT
> >
> > Good point! But don't forget to include the list. :-)
> >
> > Regards,
> >
> > Richard Broersma Jr.
> > > (SELECT controller_id FROM datapack) ?
> > >
> > > --
> > > Milen A. Radev
> > >
> >
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-12 18:13 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Exner, Peter <Exner@his.de>
1 sibling, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2006-07-12 18:13 UTC (permalink / raw)
To: Exner, Peter <Exner@his.de>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
On Wed, 2006-07-12 at 03:06, Exner, Peter wrote:
> Hi,
>
> what about
>
> SELECT controller_id FROM control
> WHERE controller_id NOT IN
> (SELECT DISTINCT controller_id FROM datapack);
That one works too, but it's generally not as fast as the left join / is
null query on large tables. Give the two a try on a test set and you
should see the speed difference.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-13 15:46 Aaron Bono <postgresql@aranya.com>
parent: Exner, Peter <Exner@his.de>
1 sibling, 2 replies; 13+ messages in thread
From: Aaron Bono @ 2006-07-13 15:46 UTC (permalink / raw)
To: Exner, Peter <Exner@his.de>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
On 7/12/06, Exner, Peter <Exner@his.de> wrote:
>
> Hi,
>
> what about
>
> SELECT controller_id FROM control
> WHERE controller_id NOT IN
> (SELECT DISTINCT controller_id FROM datapack);
The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a
huge performance problem. Is that true on PostgreSQL also?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-13 16:16 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 13+ messages in thread
From: Richard Broersma Jr @ 2006-07-13 16:16 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; Exner, Peter <Exner@his.de>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
> > SELECT controller_id FROM control
> > WHERE controller_id NOT IN
> > (SELECT DISTINCT controller_id FROM datapack);
> The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a
> huge performance problem. Is that true on PostgreSQL also?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-13 16:25 Aaron Bono <postgresql@aranya.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2006-07-13 16:25 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Exner, Peter <Exner@his.de>; pgsql-sql
On 7/13/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> > > SELECT controller_id FROM control
> > > WHERE controller_id NOT IN
> > > (SELECT DISTINCT controller_id FROM datapack);
> > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT
> is a
> > huge performance problem. Is that true on PostgreSQL also?
>
> From my experience, it does not preform as well as the standard group by
> clause. I noticed a ~20%
> increase in query run times.
So in that case this would be better:
SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack);
or
SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT controller_id FROM datapack GROUP BY controller_id);
Guess you need to do some explain plans to see which would be best.
Good luck!
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-13 16:33 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Broersma Jr @ 2006-07-13 16:33 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Exner, Peter <Exner@his.de>; pgsql-sql
> > > > SELECT controller_id FROM control
> > > > WHERE controller_id NOT IN
> > > > (SELECT DISTINCT controller_id FROM datapack);
> > > The DISTINCT is not necessary. I have heard with Oracle that DISTINCT
> > is a
> > > huge performance problem. Is that true on PostgreSQL also?
> >
> > From my experience, it does not preform as well as the standard group by
> > clause. I noticed a ~20%
> > increase in query run times.
>
>
>
> So in that case this would be better:
>
> SELECT controller_id FROM control
> WHERE controller_id NOT IN
> (SELECT controller_id FROM datapack);
>
> or
>
> SELECT controller_id FROM control
> WHERE controller_id NOT IN
> (SELECT controller_id FROM datapack GROUP BY controller_id);
Well in this case, the group by or distinct is simple not needed for the query to preform
correctly. The additional group by clause in the second query could cause it to preform
additional processing which "may" cause it to preform slower.
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to find entries missing in 2nd table?
@ 2006-07-17 01:09 Nate Teller <nate.teller@mindspring.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 13+ messages in thread
From: Nate Teller @ 2006-07-17 01:09 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; Exner, Peter <Exner@his.de>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
I have received good performance with the following:
select c.controller
from control c
left outer join datapack d on d.controller_id = c.controller_id
where d.controller_id is null
Nate Teller
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Aaron Bono
Sent: Thursday, July 13, 2006 11:46 AM
To: Exner, Peter
Cc: Richard Broersma Jr; SQL Postgresql List
Subject: Re: [SQL] How to find entries missing in 2nd table?
On 7/12/06, Exner, Peter < Exner@his.de <mailto:Exner@his.de> > wrote:
Hi,
what about
SELECT controller_id FROM control
WHERE controller_id NOT IN
(SELECT DISTINCT controller_id FROM datapack);
The DISTINCT is not necessary. I have heard with Oracle that DISTINCT is a
huge performance problem. Is that true on PostgreSQL also?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-07-17 01:09 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-11 14:19 How to find entries missing in 2nd table? alex-lists-pgsql@yuriev.com
2006-07-11 15:52 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-11 16:57 ` Milen A. Radev <milen@radev.net>
2006-07-11 16:00 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-11 16:04 ` Frank Bax <fbax@sympatico.ca>
2006-07-11 17:03 Re: How to find entries missing in 2nd table? Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-12 08:06 Re: How to find entries missing in 2nd table? Exner, Peter <Exner@his.de>
2006-07-12 18:13 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-13 15:46 ` Aaron Bono <postgresql@aranya.com>
2006-07-13 16:16 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-13 16:25 ` Aaron Bono <postgresql@aranya.com>
2006-07-13 16:33 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-17 01:09 ` Nate Teller <nate.teller@mindspring.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox