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

On Sat, Oct 24, 2015 at 5:23 PM, Dane Foster <studdugie(at)gmail(dot)com> wrote:

> On Sat, Oct 24, 2015 at 3:42 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>
>>
>>
>> W dniu 24.10.2015 o 21:03, Rafal Pietrak pisze:
>> >
>> >
>> > W dniu 24.10.2015 o 15:00, David G. Johnston pisze:
>> >> On Sat, Oct 24, 2015 at 6:41 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu
>> >> <mailto:rafal(at)ztk-rp(dot)eu>>wrote:
>> > [----------------------]
>> >>
>> >> ​Using explicit column names is expected - using "*" in non-trivial and
>> >> production queries is not.
>> >>
>> >> You can move the aliases if you would like.
>> >>
>> >> SELECT *
>> >> FROM tablea (col1, col2, col4)
>> >> JOIN tableb AS tb1 (col1, col3, col5) USING (col1)
>> >> JOIN tableb AS tb2​
>> >>
>> >> ​(col1, col6, col7) USING (col1)
>> >
>> > I knew there must have been something like this.
>>
>> Upss. Almost, but not quite. I've just read the manual on that
>> (http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html
>> )
>> and it looks like "col1", "col2", etc in the above example are column
>> *aliases*. Right?
>>
>> So I have to list *all* the columns of the aliased table irrespectively
>> if I need any of them within the output, or not.
>>
>> It's a pity standard didn't choose to make column aliasing optional,
>> allowing for cherry pick what's aliased like following:
>>
>> .. JOIN table AS tb(column7 [as alias1], column3 [as alias2],...)
>>
>> thenx anyway, "Mandatory" column aliasing is helpfull too.
>>
>> -R
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (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.
>
> 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?
>
> 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.

Dane
​