Requête équivalente anti-semi jointure SQL gauche

J'analyse le concept d'anti semi-join dans le SQL Server,

La documentation Microsoft qui décrit le mot-clé ANTISEMIJOIN de la syntaxe U-SQL cite la requête suivante pour SQL:

SELECT * FROM A 
WHERE A.Key NOT IN (SELECT B.Key FROM B)

D'autres suggèrent la requête suivante :

SELECT * FROM A 
WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.Key = B.Key)

Les deux semblent renvoyer les mêmes enregistrements, la requête proposée par Microsoft semble avoir de meilleures performances, cependant j'ai trouvé un cas dans lequel les deux requêtes donnent des résultats différents et je ne sais pas laquelle d'entre elles est la bonne. Je le montre avec un exemple:

DECLARE @A TABLE ([OnA] [int], [DataA] [text])
DECLARE @B TABLE ([OnB] [int], [DataB] [text])
INSERT INTO @A VALUES (1, 'A1'), (2, 'A2'), (NULL, 'A3')
INSERT INTO @B VALUES (1, 'B1'), (3, 'B2'), (NULL, 'B3')
--LEFT SEMI-JOIN
SELECT * FROM @A 
WHERE OnA IN (SELECT OnB FROM @B) 
--Returns with ANSI_NULLS ON: 1 | A1 (Correct!) 
--Returns with ANSI_NULLS OFF: 1 | A1, NULL | A3 (Correct!)
SELECT * FROM @A 
WHERE EXISTS (SELECT 1 FROM @B WHERE OnA = OnB) 
--Returns with ANSI_NULLS ON: 1 | A1 (Correct!) 
--Returns with ANSI_NULLS OFF: 1 | A1 (Not correct!)("ANSI_NULLS OFF" insensitive!)
--LEFT ANTISEMI-JOIN
SELECT * FROM @A 
WHERE OnA NOT IN (SELECT OnB FROM @B) 
--Returns with ANSI_NULLS ON: empty (Not Correct and very dangerous!)  
--Returns with ANSI_NULLS OFF: 2 | A2 (Correct!)
SELECT * FROM @A 
WHERE NOT EXISTS (SELECT 1 FROM @B WHERE OnA = OnB) 
--Returns with ANSI_NULLS ON: 2 | A2, NULL | A3 (Correct!)
--Returns with ANSI_NULLS OFF: 2 | A2, NULL | A3 (Not correct!)("ANSI_NULLS OFF" insensitive!)

Il est évident qu'avec la présence de NULL dans la correspondance des colonnes clés, la requête antisemi-jointure gauche citée par Microsoft ne renvoie pas (en cas normal: lorsque ANSI_NULLS ON) le résultat complémentaire à la requête semi-jointure gauche et ceci est déjà un bug. De plus, zéro enregistrement revient toujours, c'est très sérieux!

D'autre part, les autres formules avec l'opérateur EXIST et NOT EXIST ne semblent pas non plus être cohérentes avec le paramètre "ANSI_NULLS OFF". Cette erreur affecte également les résultats des opérations de "semi jointure gauche/droite" !

Donc, je suppose que l'opération "semi join" est la preuve ANSI_NULLS dans T-SQL/SQL Server, mais cela ne devrait être fait que de cette manière:

--LEFT SEMI-JOIN
SELECT * FROM @A 
WHERE OnA IN (SELECT OnB FROM @B)

Bien que l'opération "antisemi join" ne soit pas sûre et ne soit pas une preuve ANSI_NULLS, nous avons donc la responsabilité d'utiliser la bonne variante dans des contextes certainement statiques :

--LEFT ANTISEMI-JOIN (when ANSI_NULLS is ON)
SELECT * FROM @A 
WHERE NOT EXISTS (SELECT 1 FROM @B WHERE OnA = OnB)
--LEFT ANTISEMI-JOIN (when ANSI_NULLS is OFF and it's possible that there is at least one record with NULL in key value)
SELECT * FROM @A 
WHERE OnA NOT IN (SELECT OnB FROM @B)

Êtes-vous tous d'accord avec moi?

Voici mes questions :

Serait-il juste de gérer l'éventualité "NullVsNull" avec SET ANSI_NULLS plutôt qu'une requête explicite ?

Quelle serait la meilleure requête unique pour refléter fidèlement le comportement ANTI SEMI JOIN quel que soit le paramètre ANSI_NULLS ?

Est-il possible que Microsoft et d' autres fassent tous les deux une erreur ?

Pourquoi l'opérateur EXIST semble-t-il insensible au paramètre "ANSI_NULLS OFF" ?

Pourquoi l'opérateur NOT IN renvoie-t-il toujours EMPTY dans le cinquième cas ?

Solution du problème

Vous comprenez déjà l'idée pourquoi cela se produit. J'ai trouvé un bon article dans le lien ci-dessous. Vérifie s'il te plaît.

Microsoft n'a peut-être pas fait d'erreur. Selon l'article ci-dessous, Microsoft SQL Server gère null différemment de la norme ANSI si vous ansi_nullsdésactivez.

Utilisez la ligne ci-dessous avant votre requête

set ansi_nulls off

Et not in()renverra le résultat souhaité bien qu'il y ait null dans la liste.

Pour aller plus loin:

https://www.sqlservercentral.com/articles/four-rules-for-nulls

Dans la documentation fournie par Microsoft, ils ont également mentionné :

Envisagez d'utiliser SEMIJOIN (U-SQL) dans les situations où vous utiliseriez une sous-requête avec IN dans SQL. Envisagez d'utiliser ANTISEMIJOIN (U-SQL) dans les situations où vous utiliseriez une sous-requête avec NOT IN dans SQL