What is the difference between EXISTS & IN (in TSQL)?

Question

I have a list of ID's in a table and wish to flag errors in data when the ID is not in the ID table

should I use:

' WHERE [ID] NOT IN (SELECT ID FROM TABLE)'
or
' WHERE NOT EXISTS ( SELECT ID FROM TABLE) '

and why?

I'm struggling to get my head around the difference between the two!!

SYNTAX CORRECTION
***********************************
select FPN from incomingdata
where dbo.provFPN(FPN)
not in (select hosprov from vwneonates)

VS.

select FPN from incomingdata
where not exists (select hosprov from vwneonates
where dbo.provFPN(FPN)=Hosprov)

Answer 1

They are used for pretty much the same purpose, but I have noticed when the table has A LOT of data, then using NOT IN executes the results faster. We did an explain on both uses, and it seems that NOT IN compared to NOT EXISTS when you are trying to query from a large table, will bring back faster results.

Answer 2

****************************
****************************
I got your email but Yahoo Answers won't let me respond, it gives me an error saying your email address can't be found. ??

If you want more information, email me again and provide your email address so I don't have to come back to this thread.

This is my response to your email:

It is the old style of writing an outer join, but it is a left join. Both work. All of the old style SQL works in every tool I've used (up to Oracle 8i, SQLServer 2000, and Access 2003).

What is it you are trying to do exactly?

I've written a LOT of SQL. Too much. :)

This is totally off topic, but is a little known feature in SQL that I show beginners so they put it in their bag of tricks. Read up on correlated subqueries after you finish what you're working on. You don't have to understand it all, but it is nice to know they exist. I've only used them a few times, but they are perfect when you need them. http://www.databasejournal.com/features/mssql/article.php/3485291.

Another trick when you are trying to see whether a field is blank or has a space is to put literals around them like this: select "*" + c1 + "*".

****************************
****************************

exists condition is met if one row is returned. Not in is looking for a match.

Also, that syntax is bad anyway. Do this instead, it is infinitely faster:

select * from t1, t2
where t1.id*=t2.id
and t2.id is null

Edit: I should clarify that NOT IN as you wrote it will search the table for every row in the the outer table even after it makes the match. Although it uses an index if possible, it still has to do essentially a cartesian result set. That is what makes it take so long. The NOT EXISTS is looking for a single instance to be returned for each row and then stops so it still has to run a separate query. It is faster, but not the fastest. My way relates them and runs through the tables only once.

Answer 3

According to the help in SQL both queries return the same information.

Answer 4

Actually Using IN is easier.

Using EXISTS requires you to refer to the table references in the FROM CLAUSE

SELECT
ClientID,
ClientName
FROM
clients
WHERE EXISTS (
SELECT
*
FROM
PhoneNumbers
WHERE
ClientID = clients.ClientID
)

Check this out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

Answer 5

They basically do the same but the make the comparisons in a different way