Re: distinct with <>

From: Randy Harris <randy_at_SpamFree.com>
Date: Mon, 09 May 2005 17:40:25 GMT
Message-ID: <dgNfe.415$j17.107@newssvr33.news.prodigy.com>

"vegas_girlie" <minnie_valerie_at_hotmail.com> wrote in message news:1115658650.824997.152500_at_f14g2000cwb.googlegroups.com...
> hi!
> 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
>
> seems to work. when i first did it without the null statement it
> returned values that existed in both tables. why do i have to search
> for nulls? i am confused about that.
>
> thanks a million!

Without the arguments for NULL, the query says:

Find ALL records in eml and include any matching records in eml_address.

With the Null argument, it says:

Find all records in eml where there is no matching record in eml_address. (The matchup is NULL on the eml_address side)

This is the technique that I learned to find unmatched records. Daniel Morgan suggests using NOT EXISTS. Perhaps that would be more efficient? I dunno. Received on Mon May 09 2005 - 12:40:25 CDT