Possible Duplicate:
SQL NOT IN not working
I am getting different results in my result set from NOT IN and NOT EXISTS. Here is the basic query:
The following query will return 300 rows.
select VendorID from Vendors
If I add a where clause to only return vendors that are also in the carriers table, I get 50 rows.
select VendorID from Vendors where VendorID in(select CarrierId from Carriers)
I would expect that a NOT IN would return the vendors that didn't show up in the query above. In other words, the 300 vendors minus the 50 that were included above.
select VendorId from Vendors where VendorId not in(select CarrierId from Carriers)
This is not the case. I get 0 rows with the Not In clause.
If I use NOT EXIST, I get the 250 rows I would expect.
select VendorID from Vendors where NOT EXISTS (select CarrierID from Carriers where CarrierID = VendorID)
What is the difference here between NOT IN and NOT EXISTS?
