Re: distinct with <>

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)