pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedset difference
4+ messages / 2 participants
[nested] [flat]
* set difference
@ 2002-10-15 12:52 Domoszlai László <dlacko@finit.hu>
2002-10-15 14:25 ` Re: set difference Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Domoszlai László @ 2002-10-15 12:52 UTC (permalink / raw)
To: pgsql-sql
Hello,
I would like to make symmetrical(set) difference in a query.
But the simpliest way I could find is
select id from a
except
select id from b
union
select id from b
except
select id from a
Is there any better solution for this problem?
Thanks
Laca
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set difference
2002-10-15 12:52 set difference Domoszlai László <dlacko@finit.hu>
@ 2002-10-15 14:25 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-10-15 14:25 UTC (permalink / raw)
To: Domoszlai László <dlacko@finit.hu>; +Cc: pgsql-sql
=?iso-8859-1?Q?Domoszlai_L=E1szl=F3?= <dlacko@finit.hu> writes:
> I would like to make symmetrical(set) difference in a query.
> But the simpliest way I could find is
> select id from a
> except
> select id from b
> union
> select id from b
> except
> select id from a
> Is there any better solution for this problem?
One thing you should definitely do is change "union" to "union all".
"union" implies a pass of duplicate removal, which shouldn't be
necessary here (unless a or b individually contain duplicates and
you want to get rid of those too).
Another thing to try is
(a union b) except (a intersect b)
(Again, you might be able to say union all instead of union.)
Not sure which will be faster.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set difference
@ 2002-10-16 13:44 Domoszlai László <dlacko@finit.hu>
2002-10-16 13:57 ` Re: set difference Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Domoszlai László @ 2002-10-16 13:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> > I would like to make symmetrical(set) difference in a query.
> > But the simpliest way I could find is
>
> > select id from a
> > except
> > select id from b
> > union
> > select id from b
> > except
> > select id from a
>
> > Is there any better solution for this problem?
>
> One thing you should definitely do is change "union" to "union all".
> "union" implies a pass of duplicate removal, which shouldn't be
> necessary here (unless a or b individually contain duplicates and
> you want to get rid of those too).
>
> Another thing to try is
> (a union b) except (a intersect b)
> (Again, you might be able to say union all instead of union.)
> Not sure which will be faster.
It's likely faster but my problem is I have to run queries twice. I've looked at
setOp executor and it seems symmetrical differencial can run as time as an EXCEPT.
I would write it, because it speeds up my query very much, but I don't know if it is worth. So, do you think a DIFFERENCE or EXCEPT SYMMETRIC clause will be reasonable
feauture for postgresql?
Laca
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: set difference
2002-10-16 13:44 Re: set difference Domoszlai László <dlacko@finit.hu>
@ 2002-10-16 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-10-16 13:57 UTC (permalink / raw)
To: Domoszlai László <dlacko@finit.hu>; +Cc: pgsql-sql
=?iso-8859-1?Q?Domoszlai_L=E1szl=F3?= <dlacko@finit.hu> writes:
> So, do you think a DIFFERENCE or EXCEPT SYMMETRIC clause will be reasonable
> feauture for postgresql?
I doubt it; haven't heard a request for it before, and AFAICS there's no
such thing in the SQL spec...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-16 13:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-15 12:52 set difference Domoszlai László <dlacko@finit.hu>
2002-10-15 14:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-16 13:44 Re: set difference Domoszlai László <dlacko@finit.hu>
2002-10-16 13:57 ` Tom Lane <tgl@sss.pgh.pa.us>
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