Обсуждение: except command
Hello
I tried to use the except command in postgresql 8.1
I don't understand as it works
When I read doc , I understand that it was like a difference betwwen two
queries
However , It looks as if the 2nd part was ignored
I tried to test by dblink a query unising except on the same database
and all the records where returned
Do anybody know how it works ?
exemple:
select
t1.columns .....
from
table_1 t1
EXCEPT
select
t2.same_columns .....
from
table_2 t2
Olivier
>>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>, olivier boissard <olivier.boissard@cerene.fr> wrote: > I tried to use the except command in postgresql 8.1 > I don't understand as it works > When I read doc , I understand that it was like a difference betwwen two > queries That isn't how I read it: "The EXCEPT operator returns the rows that are in the first result set but not in the second." ( http://www.postgresql.org/docs/8.1/interactive/sql-select.html ) Are you seeing something different? This gets me all the eye color codes that don't start with the letter 'B'. (There are of course easier ways to get that...) bigbird=> select * from "EyeColorCode" except select * from "EyeColorCode" where "eyeColorCode" like 'B%'; eyeColorCode | descr | isActive | dotEyeColorCode --------------+-----------------+----------+----------------- GRN | Green | t | GRN GRY | Gray | t | GRY HAZ | Hazel | t | HAZ MAR | Maroon | t | MAR MUL | Multicolored | t | DIC PNK | Pink | t | PNK XXX | Unknown | t | UNK (7 rows) -Kevin
On Mon, 13 Aug 2007 13:07:43 -0500 "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: > >>> On Mon, Aug 13, 2007 at 12:50 PM, in message <46C099F6.8050002@cerene.fr>, > olivier boissard <olivier.boissard@cerene.fr> wrote: > > I tried to use the except command in postgresql 8.1 > > I don't understand as it works > > When I read doc , I understand that it was like a difference betwwen two > > queries > > That isn't how I read it: > > "The EXCEPT operator returns the rows that are in the first result set > but not in the second." > > ( http://www.postgresql.org/docs/8.1/interactive/sql-select.html ) > > Are you seeing something different? > > This gets me all the eye color codes that don't start with the letter 'B'. > (There are of course easier ways to get that...) > > bigbird=> select * from "EyeColorCode" except select * from "EyeColorCode" where "eyeColorCode" like 'B%'; > eyeColorCode | descr | isActive | dotEyeColorCode > --------------+-----------------+----------+----------------- > GRN | Green | t | GRN > GRY | Gray | t | GRY > HAZ | Hazel | t | HAZ > MAR | Maroon | t | MAR > MUL | Multicolored | t | DIC > PNK | Pink | t | PNK > XXX | Unknown | t | UNK > (7 rows) > > -Kevin so is this faster than usiung 'where not exists' ?? Steve
>>> On Mon, Aug 13, 2007 at 1:13 PM, in message <20070814061358.d66d2cb3.steve.holdoway@firetrust.com>, Steve Holdoway <steve.holdoway@firetrust.com> wrote: > On Mon, 13 Aug 2007 13:07:43 -0500 > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: >> This gets me all the eye color codes that don't start with the letter 'B'. >> (There are of course easier ways to get that...) > so is this faster than usiung 'where not exists' ?? I was trying to show a valid use of EXCEPT, not an alternative. -Kevin
Thanks for your explanation And your small example was better as one page documentation I was totally wrong. So it's like a filter on the first query I think it's useful when we have complex queries . Olivier Kevin Grittner a écrit : >>>> On Mon, Aug 13, 2007 at 1:13 PM, in message >>>> > <20070814061358.d66d2cb3.steve.holdoway@firetrust.com>, Steve Holdoway > <steve.holdoway@firetrust.com> wrote: > >> On Mon, 13 Aug 2007 13:07:43 -0500 >> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: >> >>> This gets me all the eye color codes that don't start with the letter 'B'. >>> (There are of course easier ways to get that...) >>> >> so is this faster than usiung 'where not exists' ?? >> > > I was trying to show a valid use of EXCEPT, not an alternative. > > -Kevin > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > >
>>> On Mon, Aug 13, 2007 at 4:30 PM, in message <46C0CD72.5090407@cerene.fr>, "olivier.boissard@cerene.fr" <olivier.boissard@cerene.fr> wrote: > So it's like a filter on the first query Exactly; I think that sums it up better than anything I said. By the way, it does strike me as an odd omission that there is no set operator in the ANSI standard to get you directly to the set of disjoint elements. With two datasets, a and b, you could always get there with: (a EXCEPT b) UNION ALL (b EXCEPT a) or with: (a UNION ALL b) EXCEPT (a INTERSECT b) Of course, you could store the sets in temporary tables to get there without generating from scratch each time, if that is expensive. -Kevin
Yes I noticed It was not an ANSI sql operator I think it's a good solution to spare temporay tables or result set I was searching a way to ease some réplication scripts but I don't think it will help me. It's better to use it to get a couple of records inside complex queries from many tables . Thanks for help Olivier Kevin Grittner a écrit : >>>> On Mon, Aug 13, 2007 at 4:30 PM, in message <46C0CD72.5090407@cerene.fr>, >>>> > "olivier.boissard@cerene.fr" <olivier.boissard@cerene.fr> wrote: > >> So it's like a filter on the first query >> > > Exactly; I think that sums it up better than anything I said. > > By the way, it does strike me as an odd omission that there is no set > operator in the ANSI standard to get you directly to the set of disjoint > elements. With two datasets, a and b, you could always get there with: > > (a EXCEPT b) UNION ALL (b EXCEPT a) > > or with: > > (a UNION ALL b) EXCEPT (a INTERSECT b) > > Of course, you could store the sets in temporary tables to get there without > generating from scratch each time, if that is expensive. > > -Kevin > > > > >