Re: Columns in USING clause for join don't appear in SELECT *?

[Dan <dan27649@xxxxxxxxx>]

Thanks for that, John!  I can only guess that I've not done many queries
that fall in this category.

- Dan

On Thu, May 10, 2018 at 5:53 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

> v6.1 MTU
> " JOIN with a USING clause change
> The implementation of JOIN with a USING clause specified has been changed
> to match the standard. The implicit column list of a select list with a
> USING clause consists of the common columns, followed by the remaining
> columns of the first file, and then followed by the remaining columns of
> the second file. create table t1 (a1 int, a2 int, a3 int, a4 int) create
> table t2 (a2 int, a3 int, a4 int, a5 int) For the select statement select *
> from t1 join t2 using (a2,a3) The result column list is a2, a3, t1.a1,
> t1.a4, t2.a4, t2.a5 The implicit column list of single table does not
> include the USING columns. For the select statement select t1.* from t1
> join t2 using (a2,a3) The result column list is t1.a1, t1.a4 The following
> statement is not valid. Because a2 is in the USING clause, it cannot be
> qualified in the statement. select t1.a2 from t1 join t2 using (a2,a3) The
> following is a valid statement. Because a2 is in the USING clause, a single
> a2 is in the result, so it should not be qualified. On previous releases,
> the SQL0203 message is issued. select a2 from t1 join t2 using"
>
> v7.1 SQL Reference
> ">|The result table of the join contains the columns from the USING clause
> first, then the columns from the first table of the join that were not in
> the USING clause, followed by the remaining columns from the second table
> of the join that were not in the USING clause. Any column specified in the
> USING clause cannot be qualified in the query. "
>
> It was broken when first introduced in v5r4....