In the case of NOT IN (null)

Take a look at the example: https://dbtips.wordpress.com/2009/04/26/a-more-complex-sql-weakness-with-the-conditions-not-in-vs-not-exists/

Let’s analyse the result of the first example in the mentioned link:

The query: “Get the Bank names which doesn’t have ATMs in Madrid”

We have that the subquery (SELECT B.BNK FROM ATM B WHERE B.CITY = ‘Madrid’) generate the result null, and can we say that A.BANK isn’t in the list where there exists only null?.

 

Ovbiously not, what we can say is that it’s not known if any of the Banks in the table BANK exists in the list generated by the subquery, which has the only element null.

  A detailed explanation is found in my book: Lulu.com: http://stores.lulu.com/store.php?fAcctID=3161804

Amazon.com: http://tinyurl.com/amnz-sqlbook

Advertisements
%d bloggers like this: