NOT IN vs NOT EXISTS

Question

Laquelle de ces requêtes est la plus rapide ?

N'EXISTE PAS :

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

Ou PAS DANS :

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

Le plan d'exécution de la requête indique que les deux font la même chose. Si c'est le cas, quelle est la forme recommandée ?

Ces données sont basées sur la base de données NorthWind.

[Edit]

Je viens de trouver cet article utile : http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Je pense que je vais m'en tenir à NOT EXISTS.

Answer 1

Je choisis toujours par défaut NOT EXISTS .

Les plans d'exécution peuvent être les mêmes pour le moment mais si l'une ou l'autre des colonnes est modifiée à l'avenir pour permettre NULL s le NOT IN devra faire plus de travail (même si aucune NULL sont effectivement présents dans les données) et la sémantique de l'option NOT IN si NULL s sont présents ont peu de chances d'être ceux que vous voulez de toute façon.

Lorsque ni l'un ni l'autre Products.ProductID ou [Order Details].ProductID permettre NULL s le NOT IN sera traitée de manière identique à la requête suivante.

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

Le plan exact peut varier, mais pour les données de mon exemple, j'obtiens ce qui suit.

Une idée fausse assez courante semble être que les sous-requêtes corrélées sont toujours "mauvaises" par rapport aux jointures. Elles peuvent certainement l'être lorsqu'elles forcent un plan de boucles imbriquées (sous-requête évaluée ligne par ligne) mais ce plan inclut un opérateur logique anti-semi-joint. Les anti-semi-jonctions ne sont pas limitées aux boucles imbriquées mais peuvent aussi utiliser des jointures de hachage ou de fusion (comme dans cet exemple).

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

Si [Order Details].ProductID es NULL -La requête devient alors

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

La raison en est que la sémantique correcte si [Order Details] contient tout NULL ProductId est de ne retourner aucun résultat. Voir le spool extra anti semi joint et row count pour vérifier cela qui est ajouté au plan.

Si Products.ProductID est également modifié pour devenir NULL -La requête devient alors

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

La raison de celle-ci est qu'un NULL Products.ProductId ne doit pas être retourné dans les résultats excepto si le NOT IN sous-requête ne renvoie aucun résultat (c'est-à-dire que l'option [Order Details] est vide). Dans ce cas, il devrait le faire. Dans le plan pour mon échantillon de données, ceci est implémenté en ajoutant une autre jointure anti semi comme ci-dessous.

L'effet de ce phénomène est illustré dans l'article du blog déjà cité par Buckley . Dans cet exemple, le nombre de lectures logiques passe d'environ 400 à 500 000.

En outre, le fait qu'un seul NULL peut réduire le nombre de lignes à zéro rend l'estimation de la cardinalité très difficile. Si le serveur SQL suppose que cela se produira, mais qu'en fait il n'y a pas eu d'erreur de calcul. NULL rangs dans les données, le reste du plan d'exécution peut être catastrophiquement pire, s'il ne s'agit que d'une partie d'une requête plus importante, avec des boucles imbriquées inappropriées entraînant l'exécution répétée d'un sous-arbre coûteux, par exemple .

Ce n'est pas le seul plan d'exécution possible d'une NOT IN sur un NULL -Toutefois, cette colonne peut être utilisée. Cet article en montre un autre pour une requête contre le AdventureWorks2008 base de données.

Pour le NOT IN sur un NOT NULL ou la colonne NOT EXISTS contre une colonne nullable ou non nullable, il donne le plan suivant.

Lorsque la colonne devient NULL -d'activer le NOT IN Le plan ressemble maintenant à

Il ajoute un opérateur de jointure interne supplémentaire au plan. Cet appareil est expliqué ici . Tout est là pour convertir l'ancienne recherche d'index unique corrélé sur Sales.SalesOrderDetail.ProductID = <correlated_product_id> à deux recherches par rangée extérieure. La recherche supplémentaire se fait sur WHERE Sales.SalesOrderDetail.ProductID IS NULL .

Comme il s'agit d'une jointure anti-demi, si celle-ci renvoie des lignes, la deuxième recherche n'aura pas lieu. Cependant, si Sales.SalesOrderDetail ne contient pas de NULL ProductID s, cela doublera le nombre d'opérations de recherche nécessaires.

Answer 2

Sachez également que NOT IN n'est pas équivalent à NOT EXISTS lorsqu'il s'agit de null.

Ce post l'explique très bien

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Lorsque la sous-requête renvoie ne serait-ce qu'un seul null, NOT IN ne correspondra à aucune ligne. rangs.

La raison de cet état de fait peut être trouvée en regardant les détails de ce que les NOT IN signifie réellement.

Disons, à titre d'illustration, qu'il y a 4 lignes dans le tableau appelé t. table appelée t, il y a une colonne appelée ID avec les valeurs 1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

est équivalent à

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1)
AND SomeValue != (SELECT AVal FROM t WHERE ID=2)
AND SomeValue != (SELECT AVal FROM t WHERE ID=3)
AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

Disons encore que AVal est NULL où ID = 4. Par conséquent, la comparaison != renvoie UNKNOWN. La table de vérité logique pour AND indique que INCONNU et VRAI est INCONNU, INCONNU et FAUX est FAUX. Il n'existe aucune valeur qui peut être ET avec INCONNU pour produire le résultat VRAI.

Par conséquent, si une ligne de cette sous-requête renvoie NULL, l'opérateur NOT IN aura pour valeur FALSE ou NULL et aucun enregistrement ne sera retourné

Answer 3

Si le planificateur d'exécution dit qu'ils sont identiques, ils sont identiques. Utilisez celle qui rendra votre intention plus évidente -- dans ce cas, la seconde.

Answer 4

En fait, je crois que ce serait le plus rapide :

SELECT ProductID, ProductName 
    FROM Northwind..Products p  
          outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null

Answer 5

Dans votre exemple spécifique, ils sont identiques, car l'optimiseur a compris que ce que vous essayez de faire est le même dans les deux exemples. Mais il est possible que dans des exemples non triviaux, l'optimiseur ne fasse pas cela, et dans ce cas, il y a des raisons de préférer l'un à l'autre à l'occasion.

NOT IN est préférable si vous testez plusieurs lignes dans votre sélection externe. La sous-requête à l'intérieur de la NOT IN peut être évaluée au début de l'exécution, et la table temporaire peut être vérifiée par rapport à chaque valeur de la sélection externe, plutôt que de réexécuter la sous-sélection à chaque fois, comme cela serait nécessaire avec l'instruction NOT EXISTS déclaration.

Si la sous-requête doit soit corrélée avec la sélection extérieure, alors NOT EXISTS peut être préférable, car l'optimiseur peut découvrir une simplification qui évite la création de tables temporaires pour remplir la même fonction.