please give me select sqls examples to distinct these!

 

From: Rob Sargent

Date: 2022-10-25 22:06

To: jacktby

CC: pgsql-general

Subject: Re: please give me select sqls examples to distinct these!

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.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



typedef enum SetOperation

{

    SETOP_NONE = 0,

    SETOP_UNION,

    SETOP_INTERSECT,

    SETOP_EXCEPT

} SetOperation;

---

jacktby@gmail.com

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 Tue, Oct 25, 2022 at 8:56 AM Rob Sargent <robjsargent@gmail.com> wrote:

On 10/25/22 09:24, David G. Johnston wrote:

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.

OK.  TBH I thought this was the JOOQ list! Didn't notice the typedef.  So this is all about exact match "tables".

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.