Re: distinct with <>

vegas_girlie wrote:

> 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?

Right. The query runs forever because you haven't specified how to join the two tables together. Changing the = operator to <> or != removes the join and creates a cartesian product.

You can either

1. do an outer join and look for nulls:

SELECT distinct a.EML_ADDRESS
from test.eml a, eml_addr_t b
where a.eml_address = b.eml_address(+)
and b.eml_address is null

OR 2)use a subquery

SELECT distinct a.EML_ADDRESS
from test.eml a
where a.eml_address not in

   (select b.EML_ADDR from eml_addr_t b)

Not sure which will be faster.

-- 
//-Walt