Re: SQL questions???

In article <6m22em$vke1_at_hkpa05.polyu.edu.hk>,   "Elton Chan" <97980015r_at_polyu.edu.hk> wrote:
>
> Hi all,
>
> Anybody know how to replace 'NOT IN' operator by other operations?
>
> SELECT * FROM TABLE1 WHERE
> CODE1 NOT IN (
> SELECT * FROM TABLE1 WHERE
> ( CODE1 = '1' OR CODE1 = '2' OR CODE1 = '3' )
> AND
> ( CODE2='A' OR CODE2='B' OR CODE2='C')
> );
>
> ANY clue??? I've tried to use outer join, but it cannot be used with OR
> operation...
>
> Thanks,
> Elton
>

You can replace NOT IN with NOT EXISTS

The example below is a slightly different version of your query.

SELECT * FROM table1 t1
WHERE t1.code2 IN ('A','B','C') AND

      t1.code1 NOT IN (SELECT t2.code1 FROM table1 t2
                       WHERE t2.code1 IN ('1','2','3'));

This could be rewritten as follows (and is usually more efficient).

SELECT * FROM table11 t1
WHERE t1.code2 IN ('A','B','C') AND

      NOT EXISTS (SELECT 'DUMMYSTRING' FROM table1 t2
                  WHERE t1.code1 = t2.code1 AND
                        t2.code1 IN ('1','2','3'));

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jun 29 1998 - 00:00:00 CDT