Quelle est la différence entre n'EXISTE PAS contre PAS contre une JOINTURE GAUCHE OÙ EST NULLE?

Il me semble que vous pouvez faire la même chose dans une requête SQL en utilisant soit n'EXISTE PAS, PAS EN, ou une JOINTURE GAUCHE OÙ EST NULL. Par exemple:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)

SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)

SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

Je ne suis pas sûr si je suis toute la syntaxe correcte, mais ce sont les techniques que j'ai vu. Pourquoi devrais-je choisir d'utiliser l'un plutôt que l'autre? N'performances diffèrent...? Lequel des deux est le plus rapide /la plus efficace? (Si elle dépend de la mise en œuvre, quand devrais-je utiliser?)

  • De nombreuses communes SQL moteurs de vous donner la possibilité de voir un plan d'exécution. Vous pouvez souvent apercevoir des différences significatives dans l'efficacité logiquement équivalent de cette façon des requêtes. Le succès de la méthode dépend de facteurs tels que la taille de la table, ce que les index sont présents, et d'autres.
  • Pour la n'EXISTE PAS, vous devez utiliser SELECT a FROM table1 WHERE NOT EXISTS (SELECT NULL FROM table2 WHERE table1.a = table2.a) de sorte qu'aucun ensemble de résultats doivent être créées pour la sous-requête.
  • aucune base de données ne se soucie de ce qu'est exactement vous revenez à l'intérieur de la EXISTS clause. Vous pouvez retourner *, NULL ou quoi que ce soit: tout cela sera optimisé loin.
  • pourquoi? Ici: techonthenet.com/sql/exists.php et ici: msdn.microsoft.com/en-us/library/ms188336.aspx semblent utiliser *...
  • Quassnoi, froadie, Quassnoi vous êtes en droit de le SGBD peut l'optimiser loin, mais pourquoi exprimer un intérêt à un résultat si vous n'en avez pas? Être explicite et dire que vous n'avez pas de soins, il est facile pour l'exécution de planificateur et de ne pas compter sur son optimisation, que vous n'avez pas besoin d'.
  • ce n'est pas sur "l'expression d'intérêt". C'est à propos de l'analyseur de requête de demande de vous mettre quelque chose entre SELECT et FROM. Et * est juste plus facile à taper. Oui, SQL n'ont une certaine ressemblance de langage naturel, mais il est interprété et exécuté par une machine, une machine programmée. Ce n'est pas qu'elle ne sera jamais se briser dans votre cabine et de crier "arrêtez exigeants pour les champs supplémentaires dans un EXISTS requête parce que je suis f**g malades de l'analyse puis de les jeter au large!". C'est OK avec un ordinateur, vraiment.
  • si vous avez écrit le code pour le seul but d'une machine à interpréter le code aura l'air horrible, et, malheureusement, très peu de gens comme ça. Toutefois, si vous écrivez du code dans une autre optique, l'écriture de code pour exprimer ce que vous voulez que la machine à faire comme un communiqué de vos pairs, vous allez écrire mieux et plus facile à gérer le code. Être intelligent, écrire du code pour les gens, pas pour l'ordinateur.
  • votre point était "le rendant facile pour l'exécution planner". Mon point est qu'il n'a pas vraiment d'importance: l'exécution planificateur peut gérer les deux avec la même facilité. Quant à moi, personnellement, j'utilise SELECT NULL dans le EXISTS requêtes — pas parce que c'est "plus efficace" (qui ne l'est pas) ou "plus lisible" (qui n'est pas de trop), mais de la beaucoup plus important de raison d'être utilisés pour cela.
  • Juste essayé dans mon propre cas sur ma base de données sous MS Access 2007 (T-SQL) - PAS DANS - environ 10 sec - N'EXISTE - environ 1,5 sec
  • En de rares occasions, j'ai couru dans les bases de données qui fait quelque chose avec l'instruction SELECT dans une clause Exists. Autant que je me souvienne, les anciennes versions d'Access serait effectivement de retour d'un enregistrement avant l'analyse de l'existence. Ainsi, SÉLECTIONNEZ * ne pas effectuer aussi bien que dire de SÉLECTIONNER 1. En outre, j'ai couru dans les bases de données (il ya de nombreuses lunes) qui permettrait de jeter les erreurs de compilateur lors de la EXISTE une clause GROUP BY et vous avez utilisé SELECT *. Moi-même, j'utilise SÉLECTIONNEZ 1 parce que je n'ai jamais couru dans une DB qui n'a pas les manipuler correctement, et ça me permet de rechercher et de détruire les utilisations de SELECT * dans le code.
  • Sélectionnez NULL serait évidemment servir le même but que de SÉLECTIONNER 1.
  • Pourriez vous s'il vous plaît partager quelques détails sur le contenu de la base de données?
  • Double Possible de What la différence entre les INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN?

InformationsquelleAutor froadie | 2010-02-11
  1. 127

    En un mot:

    NOT IN est un peu différent: il ne correspond jamais avec si il n'y a qu'un seul NULL dans la liste.

    • Dans MySQL, NOT EXISTS est un peu moins efficace

    • Dans SQL Server, LEFT JOIN /IS NULL est moins efficace

    • Dans PostgreSQL, NOT IN est moins efficace

    • Dans Oracle, les trois méthodes sont les mêmes.

    • Merci pour les liens! Et merci pour la présentation rapide... Mon bureau est bloquant le lien pour une raison 😛 mais je vais vérifier dès que j'arrive à un ordinateur ordinaire.
    • Un autre point est que si table1 .a contient NULL la EXISTS requête ne sera pas de retour cette ligne mais la NOT IN requête si table2 est vide. PAS DANS vs n'EXISTE PAS Nullable Colonnes: SQL Server
    • PAS DANS () renvoie la valeur vrai (pas NULL), tout comme NOT EXISTS (NULL = column)
    • re, Bon point, obtenu que dans le mauvais sens. Le NOT EXISTS retournera toujours la ligne mais NOT IN fera uniquement si la sous-requête ne retourne aucune ligne.
    InformationsquelleAutor Quassnoi
  2. 5

    Si la base est bonne à l'optimisation de la requête, les deux premiers seront transformés en quelque chose de proche de la troisième.

    Pour les situations simples comme ceux de vous remettre en question, il devrait y avoir peu ou pas de différence, car ils seront exécutées que les jointures. Dans des requêtes plus complexes, la base de données peut ne pas être en mesure de faire une jointure de la not in et not exists queryes. Dans ce cas, les requêtes obtiendrez beaucoup plus lent. D'autre part, une jointure peut également effectuer mal si il n'y a aucun indice qui peut être utilisé, de sorte que juste parce que vous utilisez une jointure ne veut pas dire que vous êtes en sécurité. Vous devez examiner le plan d'exécution de la requête pour savoir si il peut y avoir aucun problème de performances.

    InformationsquelleAutor Guffa
  3. 2

    En supposant que vous êtes en évitant les valeurs null, ils sont tous des moyens de l'écriture d'un anti-rejoindre en utilisant le Standard SQL.

    Une omission évidente est l'équivalent de l'aide EXCEPT:

    SELECT a FROM table1
EXCEPT
SELECT a FROM table2

    Note dans Oracle, vous devez utiliser le MINUS opérateur (sans doute un meilleur nom):

    SELECT a FROM table1
MINUS
SELECT a FROM table2

    Parlant de syntaxe propriétaire, il peut également être non-Standard équivalents mérite d'être étudié en fonction du produit que vous utilisez par exemple OUTER APPLY dans SQL Server (quelque chose comme):

    SELECT t1.a
  FROM table1 t1
       OUTER APPLY 
       (
        SELECT t2.a
          FROM table2 t2
         WHERE t2.a = t1.a
       ) AS dt1
 WHERE dt1.a IS NULL;
    InformationsquelleAutor onedaywhen
  4. 0

    Quand le besoin d'insérer des données dans la table avec des multi-champ de clé primaire, considèrent qu'il est beaucoup plus rapide (j'ai essayé dans l'Accès mais je pense que toute Base de données) de ne pas vérifier que "n'existe pas d'enregistrements avec 'ces' valeurs dans la table", plutôt qu'à insérer dans la table, et l'excès de dossiers (par la clé) ne sera pas inséré deux fois.

    InformationsquelleAutor baleks
  5. 0

    Point de vue des performances toujours éviter en utilisant l'inverse des mots-clés comme PAS, n'EXISTE PAS, ...
    Parce que pour vérifier l'inverse des éléments SGBD doivent s'exécute par le biais de tous les services disponibles et de la chute de l'inverse de la sélection.

    • Et que proposez-vous comme solution de contournement lorsque vous avez réellement besoin NOT?
    • Bien quand il n'y a pas d'option de cause, nous avons besoin de ne PAS utiliser les opérations et c'est pourquoi ils sont existe. La meilleure pratique est à éviter quand on a aucune autre alternative de solutions.
    • si un optimiseur transforme une requête et retourne le résultat erroné alors c'est un bug
    • oui et si vous contactez l'équipe SQL Server et ils reconnaissent le bug, mais refuser de le corriger car ils disent de le faire peut faire des requêtes s'exécutent plus lentement, alors c'est un bug vous devez traiter avec.
    • Ce n'était pas un scénario hypothétique je suppose 🙂 Avez-vous, par hasard, rappelez-vous, le bug de détails?
    • Non, c'est malheureusement quelque chose de l'équipe SQL Server n'sur le principe et dans la pratique. Je pense que c'est spécifiquement ce bogue je suis tombé sur: "nous avons par défaut à "optimiser les performances" - ce qui est bon pour 99% des cas... Cette valeur par défaut à "optimiser les performances" encore une fois, est établi depuis longtemps, et elle a accepté. (Oui, ce n'est pas la position choisie par les compilateurs pour les langages de programmation traditionnels, mais ainsi soit-il)... Espérons que cette explication permet de clarifier nos raisons de la fermeture de ce bug comme "ne sera pas résolu".'
    • Merci pour la référence. Je suis familier avec ce problème, et à mon avis honnête, ce n'est pas un bug. Je demande cela pour les raisons suivantes:(1) le standard SQL ne force pas une mise en œuvre spécifique de la clause. Il ne dit pas si vous devez exécuter la requête une fois, d'enregistrer les résultats et de les réutiliser si nécessaire ou exécuter encore et encore à chaque fois qu'il est fait référence. L'optimiseur Oracle par exemple, est en train de faire à la fois. (2) newid(), par définition DOIT renvoyer une valeur différente à chaque fois qu'elle est invoquée. Le seul problème est que le CTE doc n'est pas claire.
    • BTW, il n'est même pas un CTE question. Comment à ce sujet? sélectionnez cas étage (rand()*10) lorsque 0 à 0 lorsque 0 puis 1 lorsque 0 puis 2 lorsque 0 puis 3 lorsque 0 puis 4 lorsque 0 puis 5 lorsque 0 puis 6 lorsque 0 puis 7 lorsque 0, 8 0, 9 d'autre -1 fin Mais même ici, il est difficile de réclamation pour un bug, puisque nulle part il n'est garanti que le CAS de l'expression sera évaluée qu'une seule fois.
    • Ne pas s'enliser dans les détails de l'affaire, mais regardez la priorité d'emploi dans la détermination de la peine, m'lud. Il y a beaucoup de SQL Server bugs là-bas si vous ne pensez pas que celle-ci est digne 🙂
    • Je vous suggère vais lui donner une 2ème pensée. Vous suggérez moyen inefficace de mettre en œuvre la négation quand il y a d'autres moyens efficaces pour atteindre les mêmes résultats.

    InformationsquelleAutor Lahiru Cooray