Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 17-06-2009
Member
 
Join Date: May 2008
Posts: 29
SQL: Not in vs Not exist.

Hi,

I am learning SQL but I don't see any difference in the SQL Not in & Not Exist commands.

Please help me understand the exact difference between the both.
Reply With Quote
  #2  
Old 17-06-2009
Member
 
Join Date: Jan 2009
Posts: 43
Re: SQL: Not in vs Not exist.

EXISTS simply returns true or false depending on the results of a subquery.
When using ?NOT IN?, the query performs nested full table scans, whereas for ?NOT EXISTS?, query can use an index within the sub-query.

Syntax for not in:

Code:
Select Count(*) from tablename
where columnname1 not in (select columnname2 from tablename)
The syntax for Not exist:

Code:
Select Count(*) from tablename
where columnname1 not in (select columnname2 from tablename where columnname1 = columnname2)
I hope this helps.
Reply With Quote
  #3  
Old 17-06-2009
Member
 
Join Date: Jan 2009
Posts: 40
Re: SQL: Not in vs Not exist.

I don't think there is a difference in the query or result. The only difference what i understand is the execution time for the query to fetch the results over large databases.
I did a test of our students table, about 400,000 records.
EXISTS gets a list of enrolled students (~50,000) in
00:00:00.4545430 according to my unit test.
IN gets the same list in 00:00:01.0183649
I think not exist works more efficiently.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL: Not in vs Not exist."
Thread Thread Starter Forum Replies Last Post
PC is looking for an enabled drive that does not exist Dorothy Gale Hardware Peripherals 5 13-04-2011 06:56 AM
DNS name does not exist diegocrome Active Directory 1 28-08-2010 10:11 AM
ConverterApp.ear does not exist! Bigga Lexx Software Development 4 05-02-2010 04:45 AM
MSDE,SQL server does not exist in XP RasMus Software Development 3 16-06-2009 10:02 AM
Windows 7 Install.wim does not exist Abhav Operating Systems 3 14-04-2009 11:36 PM


All times are GMT +5.5. The time now is 02:08 AM.