Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why *no* ambig·uous complain in select part?

Why *no* ambig·uous complain in select part?

From
Emi Lu
Date:
Good morning,

Just notice one small thing, and need your information about select

select col1, col2
from table1
left join table2
using (col1, col2)

;

This query never complain about ambiguous columns of col1 and col2 in
the select part.

My guess is:
(1) col1, col2 always return table1.col1, table1.col2
(2) because using (col1, col2)
     that's why, table name is not necessary in select part

Am I wrong? Please advise?

Thank you!

RE: [SQL] Why *no* ambig.uous complain in select part?

From
"Edward W. Rouse"
Date:
Just a guess, but it seems to me that since the join is using col1 and col2
there is no ambiguity. They should be the same no matter which table it
comes from.

Edward W. Rouse


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Emi Lu
Sent: Friday, August 22, 2008 4:12 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Why *no* ambig.uous complain in select part?

Good morning,

Just notice one small thing, and need your information about select

select col1, col2
from table1
left join table2
using (col1, col2)

;

This query never complain about ambiguous columns of col1 and col2 in
the select part.

My guess is:
(1) col1, col2 always return table1.col1, table1.col2
(2) because using (col1, col2)
     that's why, table name is not necessary in select part

Am I wrong? Please advise?

Thank you!




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: RE: [SQL] Why *no* ambig.uous complain in select part?

From
Emi Lu
Date:
Hi Edward,

> Just a guess, but it seems to me that since the join is using col1 and col2
> there is no ambiguity. They should be the same no matter which table it
> comes from.

Not always the same; "Left join" may return:

table2.col1,col2 = null,
while table1.col1,col2 is not null




> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of Emi Lu
> Sent: Friday, August 22, 2008 4:12 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Why *no* ambig.uous complain in select part?
> 
> Good morning,
> 
> Just notice one small thing, and need your information about select
> 
> select col1, col2
> from table1
> left join table2
> using (col1, col2)
> 
> ;
> 
> This query never complain about ambiguous columns of col1 and col2 in 
> the select part.
> 
> My guess is:
> (1) col1, col2 always return table1.col1, table1.col2
> (2) because using (col1, col2)
>      that's why, table name is not necessary in select part
> 
> Am I wrong? Please advise?
> 
> Thank you!
> 
> 
> 
>

Re: RE: [SQL] Why *no* ambig.uous complain in select part?

From
Emi Lu
Date:
Hi Luigi,

> you will have problems if table 1 and table 2 have the same names to col1 e
> col2. For example, table1 has col1=parcel and col2=area and table 2 has
> col1=country and col2=area then, in that case you will have ambiguity.

Would you please give me an example?

I have two tables like the following:
T1 (col1 varchar, col2 varchar, primary key (col1, col2))
T2 (col1 varchar, col2 varchar, primary key (col1, col2))


Query I have is:
===================
select col1, col2
from      T1
left join T2 using (T1, T2);

Thanks a lot!







> 
> 2008/8/22 Emi Lu <emilu@encs.concordia.ca>
> 
>> Hi Edward,
>>
>>  Just a guess, but it seems to me that since the join is using col1 and
>>> col2
>>> there is no ambiguity. They should be the same no matter which table it
>>> comes from.
>>>
>> Not always the same; "Left join" may return:
>>
>> table2.col1,col2 = null,
>> while table1.col1,col2 is not null
>>
>>
>>
>>
>>
>>  -----Original Message-----
>>> From: pgsql-sql-owner@postgresql.org [mailto:
>>> pgsql-sql-owner@postgresql.org]
>>> On Behalf Of Emi Lu
>>> Sent: Friday, August 22, 2008 4:12 PM
>>> To: pgsql-sql@postgresql.org
>>> Subject: [SQL] Why *no* ambig.uous complain in select part?
>>>
>>> Good morning,
>>>
>>> Just notice one small thing, and need your information about select
>>>
>>> select col1, col2
>>> from table1
>>> left join table2
>>> using (col1, col2)
>>>
>>> ;
>>>
>>> This query never complain about ambiguous columns of col1 and col2 in the
>>> select part.
>>>
>>> My guess is:
>>> (1) col1, col2 always return table1.col1, table1.col2
>>> (2) because using (col1, col2)
>>>     that's why, table name is not necessary in select part
>>>
>>> Am I wrong? Please advise?
>>>
>>> Thank you!
>>>
>>>
>>>
>>>
>>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
> 
> 
>

Re: RE: [SQL] Why *no* ambig.uous complain in select part?

From
Mark Roberts
Date:
On Fri, 2008-08-22 at 17:10 -0400, Emi Lu wrote:
> 
> Would you please give me an example?
> 
> I have two tables like the following:
> T1 (col1 varchar, col2 varchar, primary key (col1, col2))
> T2 (col1 varchar, col2 varchar, primary key (col1, col2))
> 
> 
> Query I have is:
> ===================
> select col1, col2
> from      T1
> left join T2 using (T1, T2);
> 
> Thanks a lot!

If (T1.col1, T1.col2) != (T2.col1, T2.col2) then the join is
unsuccessful and T2.col1 and T2.col2 will be null.  If you're wondreing
if the join was successful:

select col1, col2
from T1   left outer join T2 using (col1, col2)
where T2.col1 is not null

-Mark

Re: Why *no* ambig·uous complain in select part?

From
Tom Lane
Date:
Emi Lu <emilu@encs.concordia.ca> writes:
> Just notice one small thing, and need your information about select

> select col1, col2
> from table1
> left join table2
> using (col1, col2)
> ;

> This query never complain about ambiguous columns of col1 and col2 in
> the select part.

That's because you used USING, which merges the similarly-named columns
into just one output column.  RTFM, or any SQL book.

            regards, tom lane