Обсуждение: removing duplicate entries in a statement

G'morning all,

     My task is to take table <abc> and join it with <def> with
<WHERE> clause <WHERE abc.ghi = def.ghi>.  My immediate reaction is to
write SQL statement:

          SELECT
            a.*, d.p
          INTO
            output_table
          FROM
            abc a LEFT OUTER JOIN def d
          ON
            a.ghi = d.ghi;

The problem with this is that <def> has some duplicate entries, seve-
ral occurrences of a row that has precisely the same column values as
some other row in <def>, so I'm getting duplicate rows in table
<output_table>.

     I've found one way to get around that; I wrote SQL statement:

          SELECT
            a.*, d.p
          INTO
            output_table
          FROM
              abc a
            LEFT OUTER JOIN
              (SELECT * FROM def UNION SELECT * FROM def) d
          ON
            a.ghi = d.ghi;

Since I'm doing a <UNION>, that gets rid of all the duplicate rows.
This solves my problem, but it seems to take an enormous amount of
time to execute.  Is this the best way to remove duplicate entries, or
is there some other way?

                                ---Kevin Simonson

"You'll never get to heaven, or even to LA,
if you don't believe there's a way."
from _Why Not_

On Fri, Apr 27, 2007 at 09:18:59 -0600,
  kvnsmnsn@cs.byu.edu wrote:
>
> The problem with this is that <def> has some duplicate entries, seve-
> ral occurrences of a row that has precisely the same column values as
> some other row in <def>, so I'm getting duplicate rows in table
> <output_table>.
>
> This solves my problem, but it seems to take an enormous amount of
> time to execute.  Is this the best way to remove duplicate entries, or
> is there some other way?

You could try left joining abc with a subselect of abc obtained using IN.