Re: SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset

"Andre Doehn" <a.doehn_at_gmx.net> wrote in message news:9pkdt4$iopmi$1_at_ID-71421.news.dfncis.de...
> hi,
>
> the following statement should return only rows returned by the first
query
> but not by the second:
>
> SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT WHERE TRIM(LOWER(TT.EMAIL)) NOT
> IN
>
> (SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
> KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)
>
> The resultset of this query is empty!!!
>
> Exactly the same two queries combined with MINUS:
>
> SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT
>
> MINUS
>
> (SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
> KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)
>
> ...now it returns 752 rows which are only in the first query!
>
> what iam doing wrong? i tried everything!
>
> thanks...
>
> andre
>

The distinct in the subquery in the first solution is redundant and should be deleted. The parameter to in is a set and sets are unique by design. Oracle already will perform a distinct for you and not leave your distinct out.
The column alias in the subquery is also redundant. The distincts in the minus scenario are alos redundant (all of them) as MINUS is a set operator.
The use of TT.EMAIL is inconsistent.
You trim it in the first solution and you don't trim it in the second solution.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Oct 05 2001 - 12:18:58 CDT