Difference between IN with <> (not equal) and NOT IN with = (equal)

Question

I would like to know what is the difference between these two code blocks, because for me it looks like the same result will show up.

We are looking for all records in the "commande" table that do not have an article with "stylo" in their designation.

SELECT Commande.Numero, Commande.ClientNo
FROM Commande
WHERE (Commande.Numero, Commande.ClientNo) IN (
    SELECT LigneCommande.CommandeNo, LigneCommande.ClientNo
    FROM LigneCommande
    INNER JOIN Article AS A
    ON A.Numero = LigneCommande.ArticleNo
    WHERE A.Designation <> 'Stylo')

SELECT Commande.Numero, Commande.ClientNo
FROM Commande
WHERE (Commande.Numero, Commande.ClientNo) NOT IN (
    SELECT LigneCommande.CommandeNo, LigneCommande.ClientNo
    FROM LigneCommande
    INNER JOIN Article AS A
    ON A.Numero = LigneCommande.ArticleNo
    WHERE A.Designation = 'Stylo')

Answer 1

The two are very different, but the nuance might be subtle. If all keys in the table are unique and non-NULL, then both are the same.

But, if there are duplicates, then strange things can happen. For a given pair, one row might have A.Designation = 'Stylo' and the other not. The first query will return a match, based on the "other row". The second does not return a match.

Another difference is when either Numero or ClientNo are NULL. A NULL value in the list of a NOT IN never returns true. The logic either returns FALSE or NULL. In this case, the second query will never return a value -- regardless of the presence or absence of matches.

I would suggest that you generate some simpler sample data (only using one key). Then try out the different scenarios to see how they differ.