Re: Select Distinct with LOB fields

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 ); Received on Wed Sep 05 2007 - 15:44:31 CDT