Re: distinct with <>

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1115649252.689168_at_yasure...
> vegas_girlie wrote:
>
> > hi,
> > i am trying to extract data out from an oracle server and am having a
> > tough time with the most basic sql statement. when i run
> >
> >
> > SELECT distinct tcof.EML_ADDRESS from
> > test.EML a,
> > eml_addr_t b
> > where rtrim(a.EML_ADDRESS) = a.EML_ADDR
> >
> >
> > i get returned values. however when i run
> >
> >
> > SELECT distinct a.EML_ADDRESS from
> > test.eml a,
> > eml_addr_t b
> > where rtrim(a.EML_ADDRESS) <> b.EML_ADDR
> >
> >
> > the query runs forever. i also tried the != and NOT IN and they all
> > run for hours. there are only about 6500 records in the a table and
> > the b table contains 30 million records. is it because i dont have any
> > indexes on table a?
>
> You have created a classic Cartesian join. Consider this:
>
> Take row 1 in table a. It does not match almost every row in b
> Take row 2 in table a. It does not match almost every row in b
> Take row 3 in table a. It does not match almost every row in b
>
> Repeat this step for every row in table a and the result will be
> the same. Your assumption that <> is the reverse of = is not valid.
>
> State the business problem and there is a simple solution: This isn't it.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

Let's see... 6500 X 30 Million? That's a whole lot of Cartesian.

Isn't the "customary" method of finding unmatched records to do an outer join then look for Nulls on the non preserved side? Received on Mon May 09 2005 - 10:20:20 CDT