Re: partial JOIN (was: ID column naming convention) - Mailing list pgsql-general

W dniu 24.10.2015 o 23:25, Dane Foster pisze:
>
> On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdugie@gmail.com
> <mailto:studdugie@gmail.com>> wrote:
>

[--------------------]
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>     ​
>     You may be able to accomplish that using aliased sub-selects as
>     in-line views. The purpose of the sub-selects in this use-case is
>     simply to cherry pick the columns you want.
>     SELECT *
>     FROM
>       (SELECT col1, col2, col4 FROM tablea) AS iv
>       JOIN (SELECT co1, col3, col5 FROM tableb) AS tb1 USING (col1))
>       JOIN (SELECT col1, col6, col7 FROM tableb) AS tb22 USING (col1)
>
>     Please note, this may be a performance nightmare for large tables
>     because w/o a WHERE clause that can be pushed down to the
>     sub-selects each sub-select will do a full table scan.

Yes. And that's why this is not truely an option. I'd rather give all
coluns aliases (when coding), then opt for subquery on every execute.

>
>     Please note that the 3rd JOIN clause is nutty (I translated it from
>     your original) because why would you join a table to itself just to
>     select a different set of columns?

One example (a bit artificial, I know) might be the address data, for
waybill:
create table purchases( basket int, customer int, delivery int, ...);
select * from purchases p join buyers b(customer, city, address) using
(customer) join buyers d (delivery, to_city, to_address, to_zip) using
(delivery);

... or something like that. ZIP code is not actually needed to indicate
customer (SSN might be instead).

But I wouldn't agrue if real life programming actually needs that. I've
just wanted to have the most generic example I've imagined.

>
>     Good luck,
>
>     Dane
>
> ​
> For the record SELECT * in my example is absolutely the wrong thing to
> do but your original didn't leave me w/ any other option.
>

Hmmm. I've seen people say that. I do keep that in mind, but frankly I
actually never had to avoid that to get my code working (and
maintained). I do that sometimes to limit the bandwidth necesery to
deliver the results, but not so often.

But I'd say, that "the standard" sort of does that (i.e. the star)
notoriusly:
1. with table aliasing (the case we are discussing now), standard
expects us to give column aliases *in order* they are defined within the
aliased table - that's nothing else but a "hidden star" somwhere there.
And I really wish they did it without that.
2. see the systax of INSERT: a list of column names to be prowided with
values is optional, and when you don't give it, it's like you've written
"a star" in its place. This I find *very bad* and never use it myself.
But standard people thought otherwise.

So personally, I don't see a star in a select list so harmfull, quite
the oposit.

-R