Home » SQL & PL/SQL » SQL & PL/SQL » NOT IN Vs NOT EXIST
NOT IN Vs NOT EXIST [message #244929] Thu, 14 June 2007 11:27 Go to next message
t.guru
Messages: 5
Registered: May 2007
Junior Member
Dear All,

Whats the difference b/w NOT IN & NOT EXIST


Thanks & Regards
Gugan
Re: NOT IN Vs NOT EXIST [message #244931 is a reply to message #244929] Thu, 14 June 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NULLs.

Regards
Michel
Re: NOT IN Vs NOT EXIST [message #244937 is a reply to message #244931] Thu, 14 June 2007 12:20 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately.

CREATE TABLE EMP_MASTER
(
EMP_NBR NUMBER(10) NOT NULL PRIMARY KEY,
EMP_NAME VARCHAR2(20 CHAR),
MGR_NBR NUMBER(10) NULL
)
/

INSERT INTO EMP_MASTER VALUES (1, ‘DON’, 5);
INSERT INTO EMP_MASTER VALUES (2, ‘HARI’, 5);
INSERT INTO EMP_MASTER VALUES (3, ‘RAMESH’, 5);
INSERT INTO EMP_MASTER VALUES (4, ‘JOE’, 5);
INSERT INTO EMP_MASTER VALUES (5, ‘DENNIS’, NULL);
INSERT INTO EMP_MASTER VALUES (6, ‘NIMISH’, 5);
INSERT INTO EMP_MASTER VALUES (7, ‘JESSIE’, 5);
INSERT INTO EMP_MASTER VALUES (8, ‘KEN’, 5);
INSERT INTO EMP_MASTER VALUES (9, ‘AMBER’, 5);
INSERT INTO EMP_MASTER VALUES (10, ‘JIM’, 5);
COMMIT
/


NOT IN

SQL> select count(*) from emp_master where emp_nbr not in ( select mgr_nbr from emp_master );
COUNT(*)
———-
0


 NOT EXISTS

SQL> select count(*) from emp_master T1 where not exists ( select 1 from emp_master T2 where t2.mgr_nbr = t1.emp_nbr );

COUNT(*)
———-
9



http://decipherinfosys.wordpress.com/2007/01/21/32/
Previous Topic: Replication
Next Topic: Example code for procedure
Goto Forum:
  


Current Time: Fri Dec 02 14:04:01 CST 2016

Total time taken to generate the page: 0.08229 seconds