Re: NOT IN

On 4 Jul, 15:26, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 4, 8:47 am, colmkav <colmj..._at_yahoo.co.uk> wrote:
>
> > is it possible to write an SQL Oracle query where you want to check
> > whether a combination of fields exist in another query
>
> > eg I tried the following but got an error:
>
> > select count(*) from tmp_risk_sum where book, strategy, exposuretypeid
> > not in (select book, strategy, exposuretypeid from tmp_risk_sum_ORACLE)
>
> SELECT
> COUNT(*)
> FROM
> TMP_RISK_SUM
> WHERE
> (BOOK,STRATEGY,EXPOSURETYPEID) NOT IN (
> SELECT
> BOOK,
> STRATEGY,
> EXPOSURETYPEID
> FROM
> TMP_RISK_SUM_ORACLE);
>
> You may find that this syntax executes more efficiently:
> SELECT
> COUNT(*)
> FROM
> TMP_RISK_SUM S,
> (SELECT DISTINCT
> BOOK,
> STRATEGY,
> EXPOSURETYPEID
> FROM
> TMP_RISK_SUM_ORACLE) SO
> WHERE
> S.BOOK=SO.BOOK(+)
> AND S.STRATEGY=SO.STRATEGY(+)
> AND S.EXPOSURETYPEID=SO.EXPOSURETYPEID(+)
> AND SO.BOOK IS NULL;
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Why does this work? Surely you would need a similar SO.STRATEGY IS NULL a nd EXPOSURETYPEID IS NULL

Also why does this execute better Received on Wed Jul 04 2007 - 09:27:48 CDT