Question PAS EN vs PAS EXISTE


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 des requêtes indique qu'ils font tous deux la même chose. Si tel est le cas, quel est le formulaire recommandé?

Ceci est basé sur la base de données NorthWind.

[Modifier]

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

Je pense que je resterai avec NOT EXISTS.


431
2017-10-06 02:19


origine


Réponses:


Je par défaut toujours à NOT EXISTS.

Les plans d’exécution peuvent être les mêmes pour le moment, mais si l’une ou l’autre colonne est modifiée pour permettre NULLs le NOT IN la version devra faire plus de travail (même si NULLs sont effectivement présents dans les données) et la sémantique de NOT IN si NULLs sont le cadeau est peu susceptible d’être celui que vous voulez de toute façon.

Quand ni Products.ProductID ou [Order Details].ProductID permettre NULLs le NOT IN sera traité de la même manière que 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 mon exemple de données, j'obtiens les informations suivantes.

Neither NULL

Une idée fausse raisonnablement répandue semble être que les sous-requêtes corrélées sont toujours "mauvaises" par rapport aux jointures. Ils peuvent certainement être quand ils 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 jointure. Les jointures anti-semi ne sont pas limitées aux boucles imbriquées mais peuvent également utiliser des hachages ou des fusions (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 est NULL-able 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  ProductIds ne renvoie aucun résultat. Reportez-vous au spool supplémentaire anti-jointure et comptage de lignes pour vérifier que cela est ajouté au plan.

One NULL

Si Products.ProductID est également changé pour devenir NULL-able 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 en est que un NULL  Products.ProductId ne devrait pas être retourné dans les résultats sauf si la NOT IN sous-requête ne devait pas retourner de résultats du tout (c.-à-d. le [Order Details] la table est vide). Dans ce cas, il devrait. Dans le plan pour mes exemples de données, cela est implémenté en ajoutant une autre jointure anti-semi comme ci-dessous.

Both NULL

L'effet de ceci est montré dans le blog déjà lié par Buckley. Dans l'exemple, le nombre de lectures logiques passe de 400 à 500 000 environ.

De plus, 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 SQL Server suppose que cela se produira mais en fait il n'y avait pas NULL lignes 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 provoquant 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 pour un NOT IN sur un NULLcolonne -able cependant. 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 colonne ou le NOT EXISTS par rapport à une colonne nullable ou non nullable, elle donne le plan suivant.

Not EXists

Lorsque la colonne change en NULL-able le NOT IN le plan ressemble maintenant

Not In - Null

Il ajoute un opérateur de jointure interne supplémentaire au plan. Cet appareil est expliqué ici. Il est tout là pour convertir le seul index corrélé précédent chercher sur Sales.SalesOrderDetail.ProductID = <correlated_product_id> à deux cherche par rangée externe. Le supplémentaire est sur WHERE Sales.SalesOrderDetail.ProductID IS NULL.

Comme il s'agit d'une jointure anti semi si celle-ci renvoie des lignes, la seconde recherche n'aura pas lieu. Toutefois, si Sales.SalesOrderDetail ne contient aucun NULL  ProductIDs il doublera le nombre d'opérations de recherche requises.


584
2018-06-17 20:10



Sachez également que NOT IN n'est pas équivalent à NOT EXISTS quand 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 même une valeur nulle, NOT IN ne correspondra à aucun   lignes

La raison de ceci peut être trouvée en regardant les détails de ce que le   NOT IN opération signifie réellement.

Disons, à des fins d'illustration qu'il y a 4 lignes dans le   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. D'où ça! =   la comparaison renvoie INCONNU. La table de vérité logique pour les états AND   que UNKNOWN et TRUE est UNKNOWN, UNKNOWN et FALSE est FALSE. Il y a   aucune valeur pouvant être AND avec UNKNOWN pour produire le résultat TRUE

Par conséquent, si une ligne de cette sous-requête renvoie NULL, l'ensemble NOT IN   L'opérateur évaluera FALSE ou NULL et aucun enregistrement ne sera   revenu


67
2018-05-09 12:23



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


23
2017-10-06 02:21



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

13
2017-10-06 02:40



J'ai une table qui contient environ 120 000 enregistrements et doit sélectionner uniquement ceux qui n'existent pas (avec une colonne varchar) dans quatre autres tables avec un nombre de lignes d'environ 1500, 4000, 40000, 200. Toutes les tables impliquées ont un index unique sur les concernés Varchar colonne.

NOT IN a pris environ 10 minutes, NOT EXISTS a pris 4 secondes.

J'ai une requête récursive qui pourrait avoir une section non réglée qui pourrait avoir contribué aux 10 minutes, mais l'autre option prenant 4 secondes explique, au moins pour moi NOT EXISTS est bien mieux ou au moins que IN et EXISTS ne sont pas exactement les mêmes et valent toujours une vérification avant d'aller de l'avant avec le code.


8
2017-07-07 17:12



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 le fasse pas, et dans ce cas il y a des raisons de préférer l'un à l'autre à l'occasion.

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

Si la sous-requête doit être corrélé avec le select externe, alors NOT EXISTS peut être préférable, car l'optimiseur peut découvrir une simplification qui empêche la création de tables temporaires pour effectuer la même fonction.


6
2017-10-06 02:54



J'utilisais

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

et a constaté qu'il donnait des résultats erronés (par erreur, je veux dire pas de résultats). Comme il y avait un NULL dans TABLE2.Col1.

En changeant la requête à

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

m'a donné les bons résultats.

Depuis lors, j'ai commencé à utiliser NOT EXISTS partout.


3
2018-06-13 15:02



Ils sont très similaires mais pas vraiment les mêmes.

En termes d'efficacité, j'ai trouvé la jointure à gauche est nulle déclaration plus efficace (quand une abondance de lignes doit être sélectionnée)


1
2018-03-19 08:27