Re: URGENT Join question

From: Guido Wegener <guido.wegener_at_gmx.de>
Date: 10 Aug 2001 00:03:55 -0700
Message-ID: <ddb4aaf6.0108092303.462ed75f_at_posting.google.com>


debo_nair_at_yahoo.com (Manoj Nair) wrote in message news:<92a0e580.0108081623.1edad313_at_posting.google.com>...
> Table 1 data
> HELLO WORLD 1
> HELLO WORLD NOW 1
>
> Table 2 data
> HELLO WORLD 1
>
> when i run
>
> select distinct b.col1,b.col2 from table2 b , table1 a where b.col1
> <> a.col1 and a.col2 = b.col2 I want no records to be returned
> cos HELLO WORLD is already present in table 1..
>
> however it returns HELLO WORLD NOW 1

Your statements seems to contain some errors, I guess you mean select distinct a.col1, a.col2 from ...

To me this behaviour seems correct, as joins are computed by rows - that means that each combination of rows is checked against the condition. You want something like this:

select distinct a.col1, a.col2 from table1 a where not exists
(select col1 from table2 b where a.col1=b.col1 and a.col2=b.col2);

This is the slow solution, an outer join would be much faster:

select distinct col1, col2 from
(
 select a.col1, a.col2, b.col2 as indicatorcolumn  from table1 a, table2 b
 where a.col1=b.col1 (+) and a.col2=b.col2 (+) )
where indicatorcolumn is null;

HTH,
  Guido Wegener Received on Fri Aug 10 2001 - 09:03:55 CEST

Original text of this message