SQL: Not in vs Not exist.

[thkernlk]

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.

[Rafiq]

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:

Select Count(*) from tablename
where columnname1 not in (select columnname2 from tablename)

The syntax for Not exist:

Select Count(*) from tablename
where columnname1 not in (select columnname2 from tablename where columnname1 = columnname2)

I hope this helps.

[Raffaele]

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.