--- In firebird-support@yahoogroups.com, "robert_difalco" wrote:

> --- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:
> > >
> > > SELECT * FROM Table A WHERE A.uid IN
> > > SELECT DISTINCT uid FROM Table [JOINS][WHERE];
> >
> > Yup, my first thought was "What a lousy idea, this won't even get
> > him the right result"! If RowA and RowB have the same UID, then
> > both will match the subselect and hence be returned. I cannot even
> > think of a case where the DISTINCT word of 'IN (SELECT
> > DISTINCT...)' makes any difference.
>
> I think you are missing the JOINS. Think of a JOIN say on a Links
> table that creates a tree structure. Believe it or not, the query
> above performs more efficiently that without the subquery because of
> the DISTINCT.
>
> R.

Maybe there are things that I don't see, but how does your query
differ from the same query without DISTINCT in the subselect? In my
opinion, the only possible difference between

SELECT * FROM TableA WHERE A.uid IN
(SELECT DISTINCT uid FROM TableA [JOINS][WHERE]);

and

SELECT * FROM TableA WHERE A.uid IN
(SELECT uid FROM TableA [JOINS][WHERE]);

is that the former may be slower than the latter (depending on how the
optimizer treats DISTINCT is such cases, it could simply ignore the
word). The results of these two queries should be identical anyway
(regardless of any JOIN).

Set