Re: Select Distinct with LOB fields

"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 Received on Thu Sep 06 2007 - 09:52:36 CDT