Обсуждение: please give me select sqls examples to distinct these!
typedef enum SetOperation
{
SETOP_NONE = 0,
SETOP_UNION,
SETOP_INTERSECT,
SETOP_EXCEPT
} SetOperation;
jacktby@gmail.com
On Oct 25, 2022, at 7:55 AM, jacktby@gmail.com wrote:
body { line-height: 1.5; } body { font-size: 14px; font-family: "Microsoft YaHei UI"; color: rgb(0, 0, 0); line-height: 1.5; } typedef enum SetOperation{SETOP_NONE = 0,SETOP_UNION,SETOP_INTERSECT,SETOP_EXCEPT} SetOperation;jacktby@gmail.com
Please use just text.
What ‘dialect’ are using? In Postgres
0: select * from table
1: select * from table union select * from table is same shape
2: select * from table join table b on Id = idb
3: select * from table except select * from tableb
thanks
jacktby@gmail.com
From: Rob SargentDate: 2022-10-25 22:06To: jacktbyCC: pgsql-generalSubject: Re: please give me select sqls examples to distinct these!div.FoxDIV_20221025220947792 { font-size: 10.5pt } div.FoxDIV_20221025220947792 { line-height: 1.5; } div.FoxDIV_20221025220947792 { font-size: 14px; font-family: "Microsoft YaHei UI"; color: rgb(0, 0, 0); line-height: 1.5; } On Oct 25, 2022, at 7:55 AM, jacktby@gmail.com wrote:typedef enum SetOperation{SETOP_NONE = 0,SETOP_UNION,SETOP_INTERSECT,SETOP_EXCEPT} SetOperation;jacktby@gmail.comPlease use just text.What ‘dialect’ are using? In Postgres0: select * from table1: select * from table union select * from table is same shape2: select * from table join table b on Id = idb3: select * from table except select * from tableb
On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent <robjsargent@gmail.com> wrote:
On Oct 25, 2022, at 7:55 AM, jacktby@gmail.com wrote:typedef enum SetOperation{SETOP_NONE = 0,SETOP_UNION,SETOP_INTERSECT,SETOP_EXCEPT} SetOperation;Please use just text.What ‘dialect’ are using? In Postgres0: select * from table1: select * from table union select * from table is same shape2: select * from table join table b on Id = idb3: select * from table except select * from tableb
#2 is probably conceptually correct but in this context should be written as:
SELECT *
FROM table
INTERSECT
SELECT *
FROM table
Distinct is a default modifier, one can specify ALL if that isn't desired.
This is trivially answered by the documentation as well:
David J.
On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent <robjsargent@gmail.com> wrote:2: select * from table join table b on Id = idb#2 is probably conceptually correct but in this context should be written as:
Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong.
David J.
On 10/25/22 09:24, David G. Johnston wrote:
OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So this is all about exact match "tables".On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent <robjsargent@gmail.com> wrote:2: select * from table join table b on Id = idb#2 is probably conceptually correct but in this context should be written as:Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong.David J.
I suppose my #2 could be framed as
select a* from table as a join table as b on a.<criteria> = b.<criteria>
to be a functional equivalent of INTERSECT?
On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent <robjsargent@gmail.com> wrote:
On 10/25/22 09:24, David G. Johnston wrote:OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So this is all about exact match "tables".On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent <robjsargent@gmail.com> wrote:2: select * from table join table b on Id = idb#2 is probably conceptually correct but in this context should be written as:Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong.David J.
I suppose my #2 could be framed as
select a* from table as a join table as b on a.<criteria> = b.<criteria>
to be a functional equivalent of INTERSECT?
SELECT DISTINCT a.*
David J.
On 10/25/22 09:58, David G. Johnston wrote:
Yeah, sorry. Meant to distinguish to two "table" defs in some way.On Tue, Oct 25, 2022 at 8:56 AM Rob Sargent <robjsargent@gmail.com> wrote:On 10/25/22 09:24, David G. Johnston wrote:OK. TBH I thought this was the JOOQ list! Didn't notice the typedef. So this is all about exact match "tables".On Tue, Oct 25, 2022 at 8:22 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Tue, Oct 25, 2022 at 7:06 AM Rob Sargent <robjsargent@gmail.com> wrote:2: select * from table join table b on Id = idb#2 is probably conceptually correct but in this context should be written as:Actually, a join is NOT conceptually correct here - the output columns for "SELECT *" will be wrong.David J.
I suppose my #2 could be framed as
select a* from table as a join table as b on a.<criteria> = b.<criteria>
to be a functional equivalent of INTERSECT?SELECT DISTINCT a.*David J.