Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Select from where not in query fails when there are null
Message
De
18/02/2011 15:31:29
 
 
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
01500770
Message ID:
01500778
Vues:
44
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
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform