Skip site navigation (1) Skip section navigation (2)

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

From: Emi Lu <emilu(at)encs(dot)concordia(dot)ca>
To: Luigi Castro Cardeles <luigi(dot)cardeles(at)gmail(dot)com>, PostgreSQL SQL List <pgsql-sql(at)postgresql(dot)org>
Subject: Re: RE: [SQL] Why *no* ambig.uous complain in select part?
Date: 2008-08-22 21:10:33
Message-ID: 48AF2B49.3020300@encs.concordia.ca (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)encs(dot)concordia(dot)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(at)postgresql(dot)org [mailto:
>>> pgsql-sql-owner(at)postgresql(dot)org]
>>> On Behalf Of Emi Lu
>>> Sent: Friday, August 22, 2008 4:12 PM
>>> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
> 
> 
> 


In response to

Responses

pgsql-sql by date

Next:From: Mark RobertsDate: 2008-08-22 21:33:59
Subject: Re: RE: [SQL] Why *no* ambig.uous complain in select part?
Previous:From: Emi LuDate: 2008-08-22 20:48:42
Subject: Re: RE: [SQL] Why *no* ambig.uous complain in select part?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group