Re: partial JOIN (was: ID column naming convention)

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

[--------------------]
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)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