Thanks Naomi.

I read the blog post. A lot of replies to the post stated that they didn't agree, i.e. they thought the examples were 'mistakes' with poorly written sql - e.g. not using alias names to reference the columns. One person wrote that you should never have nulls in a key column. But who says the query has to be on a key? It seems to me that exists queries are just "better implemented". It seems like a deficiency in the query engine that it doesn't get the same result as an exists query. I would think that the "exists query" version given in the example would be functionally equivalent to the "in query"?

Most of the replies though addressed issues with failing to use aliases, but no one gave an explanation as to why the exists query would work differently than the in query.

I understand that "NULL + 1" = NULL, this makes sense, but the problem below doesn't, to me.


>>Regarding the query below, the value "A&P" is not in the acctnrlu table (in the luacctnm column). A&P is in curdata. So the query should return rows. However, it wasn't returning anything. There was one row with a null in acctnrlu.luacctnm. I didn't think this should matter. Yet when I removed the row with the null, the query began working. Is this the expected behavior?
>>
>>
>>SELECT accountnam FROM curdata WHERE accountnam NOT in (SELECT luacctnm FROM acctnrlu)
>
>Yes, this is the expected behavior. See this strong blog post
>Why you should never use IN/NOT IN in SQL