Shakespeare wrote:
>
> "cleveridea" <cleveridea.net_at_gmail.com> schreef in bericht
> news:1189025071.389131.137750_at_d55g2000hsg.googlegroups.com...
>> On Sep 5, 10:54 am, "Andy Fish" <ajf..._at_blueyonder.co.uk> wrote:
>>> ok, maybe I didn't phrase the question very well as everyone came up
>>> with similar responses. consider this scenario:
>>>
>>> create table foo (pk integer, data clob)
>>> create table bar (pk integer, fk integer)
>>>
>>> select foo.pk, foo.data from foo, bar where foo.pk = bar.fk
>>>
>>> because several rows in bar might join to the same row in foo, this
>>> query could return duplicate rows
>>>
>>> i cannot use select distinct because I need to return all columns from
>>> foo
>>>
>>> because I am only returning columns from foo, I know that uniqueness
>>> could
>>> be determined by checking only foo.pk
>>>
>>> hope that makes it clearer.
>>>
>>> Andy
>>>
>>
>> I like using 'exists' more than 'in' - not certain it makes a
>> difference to the CBO.
>>
>> select foo.pk, foo.data
>> from foo
>> where exists ( select null from bar where foo.pk = bar.fk );
>>
>
> I would not use select null here... It works, but I have seen some
> discussions about null before in this group ;-)
>
> Shakespeare
test
Received on Sat Sep 15 2007 - 14:24:36 CDT
