Home » SQL & PL/SQL » SQL & PL/SQL » Not in Vs Not exists
Not in Vs Not exists [message #265118] Wed, 05 September 2007 08:11 Go to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Dear All,

GOOD DAY!

i have a union query, which i would like to tune more, as my knowledge is notin will take more load than not exits. but when i am trying with not exists i am getting wrong results.

the query which i am using is..

select a.custno,
b.custbillno from table1 a,table2 b, table3 c
where a.slno = b.slno
and b.slno = c.slno
and a.custno not in
(select custno from table3 where locationcd ='NL')
union
select a.custno,
decode(b.custbillno,a.custno,b.custnillno,a.custno) from table1 a,table2 b, table3 c
where a.slno = b.slno
and b.slno = c.slno
and a.custno in
(select custno from table3 where locationcd ='NL')

regards
Sai Kumar.

[Updated on: Wed, 05 September 2007 08:14]

Report message to a moderator

Re: Not in Vs Not exists [message #265123 is a reply to message #265118] Wed, 05 September 2007 08:24 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Please format you post first.
Please see How to Format
Cheers
Soumen

[Updated on: Wed, 05 September 2007 08:35]

Report message to a moderator

Re: Not in Vs Not exists [message #265126 is a reply to message #265118] Wed, 05 September 2007 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Then, "not in" and "not exists" are not the same.
SQL> create table emp as select empno, decode(deptno,10,null,deptno) deptno from scott.emp;

Table created.

SQL> create table dept as select deptno from scott.dept;

Table created.

SQL> select deptno from dept d where not exists (select null from emp e where e.deptno = d.deptno);
    DEPTNO
----------
        40
        10

2 rows selected.

SQL> select deptno from dept d where d.deptno not in (select e.deptno from emp e);

no rows selected

Regards
Michel
Re: Not in Vs Not exists [message #265193 is a reply to message #265126] Wed, 05 September 2007 13:19 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Sure Michel, as new to this forum will follow the rules.

here are the table details with data

SQL> select * from custbill;

CUSTNOBILL
----------
11111
22222
33333

SQL> select * from custbuy;

CUSTNOBILL CUSTNO
---------- ---------
11111 11111
11111 11112
22222 22222
22222 22221
33333 33331

SQL> select * from cziftag;

CZIFREF CZIFSTATUS
--------- ----------
11111 W
11112 W
22222 W
22221 W
33331 W

and this is the sql which i am executing.

   select buy.custnobill
            ,buy.custno
      from custbuy buy
           ,custbill bil
           ,cziftag ift
      where  ift.czifstatus in ('E','W')
             and buy.custno = ift.czifref
             and bil.custnobill = buy.custnobill
             and bil.custnobill not in 
                     (select custnobill from custbuy
                      minus
                      select custnobill from custbuy where custnobill = custno)
        UNION
        select buy.custnobill
              ,decode(buy.custno
              ,buy.custnobill,buy.custno,buy.custnobill)
        from custbuy buy
            ,custbill bil
            ,cziftag ift
        where ift.czifstatus in ('E','W')
        and buy.custno = ift.czifref
        and bil.custnobill = buy.custnobill
        and bil.custnobill in
                    (select custnobill from custbuy
                     minus
                     select custnobill from custbuy where custnobill = custno)
/

i agree this sql is too big. (but have no option.) Is there any best way other than this.

pls help me with your suggestion. as i am getting delay with not in.

regards
Sai Kumar.

[Updated on: Wed, 05 September 2007 13:45]

Report message to a moderator

Re: Not in Vs Not exists [message #265194 is a reply to message #265193] Wed, 05 September 2007 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
especially "How to format your post?" section

Regards
Michel
Re: Not in Vs Not exists [message #265200 is a reply to message #265194] Wed, 05 September 2007 14:03 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
If I read you statement correctly, it comes down to:

SELECT ...
FROM   ...
WHERE  ...
AND    column NOT IN (SELECT STATEMENT)
UNION
SELECT ...
FROM   ...
WHERE  ...
AND    column IN (SAME SELECT STATEMENT)


What's the point of select-union-select with opposite IN/NOT IN clauses on both sides of the union?

Why don't you let us know what you're trying to accomplish what your problem is while doing so?

Re: Not in Vs Not exists [message #265272 is a reply to message #265200] Wed, 05 September 2007 23:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to Sabines remarks:
Quote:
,decode(buy.custno
,buy.custnobill,buy.custno,buy.custnobill)

To my best sql-knowledge, this always results in custnobill.
Re: Not in Vs Not exists [message #265331 is a reply to message #265194] Thu, 06 September 2007 02:37 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

revised the post.

regards
Sai Kumar.
Re: Not in Vs Not exists [message #265336 is a reply to message #265200] Thu, 06 September 2007 02:45 Go to previous messageGo to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

I am trying to do is that, for sql with not in, i am not manupulating custno, but for sql with in condition i am manipulating custno (i.e if custno <> custnobill then custno = custtnobill).

regards
Sai Kumar.
Re: Not in Vs Not exists [message #265349 is a reply to message #265272] Thu, 06 September 2007 02:55 Go to previous message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Agree with you, but my data alway wont be custno = custnobill, i always want to send output if custno <> custnobill then i would like to send coustno = custnobill.
Previous Topic: Select
Next Topic: How to alter CLOB column to VARCHAR column?
Goto Forum:
  


Current Time: Thu Dec 08 18:27:26 CST 2016

Total time taken to generate the page: 0.05148 seconds