Différence entre EXISTS et IN en SQL?

Quelle est la différence entre la clause EXISTS et la clause IN dans SQL?

Quand devrions-nous utiliser EXISTS et quand devrions-nous utiliser IN ?

Le mot-clé exist peut être utilisé de cette manière, mais en réalité, il s’agit d’un moyen d’éviter de compter:

 --this statement needs to check the entire table select count(*) from [table] where ... --this statement is true as soon as one match is found exists ( select * from [table] where ... ) 

C’est très utile là où vous avez if instructions conditionnelles, telles qu’elles exists peuvent être beaucoup plus rapides que count .

Le in est mieux utilisé lorsque vous avez une liste statique à passer:

  select * from [table] where [field] in (1, 2, 3) 

Lorsque vous avez une table dans une instruction in , il est plus judicieux d’utiliser une join , mais la plupart du temps, cela ne devrait pas avoir d’importance. L’optimiseur de requêtes doit renvoyer le même plan dans les deux cas. Dans certaines implémentations (la plupart du temps plus anciennes, telles que Microsoft SQL Server 2000), les requêtes obtiendront toujours un plan de jointure nested , tandis que les requêtes de join utiliseront des fonctions nestedes, de fusion ou de hachage, selon le cas. Des implémentations plus modernes sont plus intelligentes et peuvent ajuster le plan même lorsqu’elles sont utilisées.

EXISTS vous indiquera si une requête a renvoyé des résultats. par exemple:

 SELECT * FROM Orders o WHERE EXISTS ( SELECT * FROM Products p WHERE p.ProductNumber = o.ProductNumber) 

IN est utilisé pour comparer une valeur à plusieurs, et peut utiliser des valeurs littérales, comme ceci:

 SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100) 

Vous pouvez également utiliser les résultats de requête avec la clause IN , comme ceci:

 SELECT * FROM Orders WHERE ProductNumber IN ( SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0) 

Basé sur l’ optimiseur de règles :

  • EXISTS est beaucoup plus rapide que IN , lorsque les résultats de la sous-requête sont très importants.
  • IN est plus rapide EXISTS , lorsque les résultats de la sous-requête sont très faibles.

Basé sur l’ optimiseur de coûts :

  • Il n’y a pas de différence.

Je suppose que vous savez ce qu’ils font, et sont donc utilisés différemment, alors je vais comprendre votre question sous la forme: Quand serait-il judicieux de réécrire le SQL pour utiliser IN au lieu de EXISTS, ou vice versa.

Est-ce une hypothèse juste?


Edit : La raison pour laquelle je pose la question est que, dans de nombreux cas, vous pouvez réécrire un code SQL basé sur IN pour utiliser plutôt EXISTS, et inversement, et pour certains moteurs de firebase database, l’optimiseur de requêtes traitera les deux différemment.

Par exemple:

 SELECT * FROM Customers WHERE EXISTS ( SELECT * FROM Orders WHERE Orders.CustomerID = Customers.ID ) 

peut être réécrit pour:

 SELECT * FROM Customers WHERE ID IN ( SELECT CustomerID FROM Orders ) 

ou avec une jointure:

 SELECT Customers.* FROM Customers INNER JOIN Orders ON Customers.ID = Orders.CustomerID 

Donc, ma question est toujours posée, est-ce que l’affiche originale s’interroge sur ce que fait IN et EXISTS, et donc comment l’utiliser, ou demande-t-il de réécrire un SQL utilisant IN pour utiliser EXISTS?

  1. EXISTS est beaucoup plus rapide que IN lorsque les résultats de la sous-requête sont très importants.
    IN est plus rapide EXISTS lorsque les résultats de la sous-requête sont très faibles.

     CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT) GO CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20)) GO INSERT INTO t1 SELECT 1, 'title 1', 5 UNION ALL SELECT 2, 'title 2', 5 UNION ALL SELECT 3, 'title 3', 5 UNION ALL SELECT 4, 'title 4', 5 UNION ALL SELECT null, 'title 5', 5 UNION ALL SELECT null, 'title 6', 5 INSERT INTO t2 SELECT 1, 1, 'data 1' UNION ALL SELECT 2, 1, 'data 2' UNION ALL SELECT 3, 2, 'data 3' UNION ALL SELECT 4, 3, 'data 4' UNION ALL SELECT 5, 3, 'data 5' UNION ALL SELECT 6, 3, 'data 6' UNION ALL SELECT 7, 4, 'data 7' UNION ALL SELECT 8, null, 'data 8' UNION ALL SELECT 9, 6, 'data 9' UNION ALL SELECT 10, 6, 'data 10' UNION ALL SELECT 11, 8, 'data 11' 
  2. Requête 1

     SELECT FROM t1 WHERE not EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id) 

    Requête 2

     SELECT t1.* FROM t1 WHERE t1.id not in (SELECT t2.t1id FROM t2 ) 

    Si dans t1 votre identifiant a une valeur nulle, la requête 1 les trouvera, mais la requête 2 ne peut pas trouver les parameters nuls.

    Je veux dire que IN ne peut rien comparer à null, donc il n’y a pas de résultat pour null, mais EXISTS peut tout comparer à null.

Si vous utilisez l’opérateur IN , le moteur SQL parsingra tous les enregistrements extraits de la requête interne. Par contre, si nous utilisons EXISTS , le moteur SQL arrête le processus d’parsing dès qu’il a trouvé une correspondance.

Le mot clé Exists évalue true ou false, mais le mot clé IN compare toutes les valeurs de la colonne de sous-requête correspondante. Un autre Select 1 peut être utilisé avec la commande Exists . Exemple:

 SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...) 

Mais IN est moins efficace donc Exists plus vite.

IN ne prend en charge que les relations d’égalité (ou d’inégalité lorsqu’elles sont précédées de NOT ).
C’est un synonyme de = any / = some , par exemple

 select * from t1 where x in (select x from t2) ; 

EXISTS prend en charge les types de relations variables, qui ne peuvent pas être exprimés en utilisant IN , par exemple –

 select * from t1 where exists (select null from t2 where t2.x=t1.x and t2.y>t1.y and t2.z like '℅' || t1.z || '℅' ) ; 

Et sur une autre note –

Les prétendues différences de performances et techniques entre EXISTS et IN peuvent résulter de l’implémentation / des limitations / des bogues spécifiques d’un fournisseur, mais souvent, elles ne sont que des mythes dus au manque de compréhension des composants internes des bases de données.

La définition des tableaux, la précision des statistiques, la configuration de la firebase database et la version de l’optimiseur ont toutes un impact sur le plan d’exécution et donc sur les indicateurs de performance.

Je pense,

  • EXISTS est lorsque vous devez faire correspondre les résultats de la requête avec une autre sous-requête. Les résultats de la requête n ° 1 doivent être récupérés lorsque les résultats de SubQuery correspondent. Genre d’une jointure .. Par exemple, sélectionnez la table des clients n ° 1 qui a également placé la table des commandes n ° 2

  • IN consiste à récupérer si la valeur d’une colonne spécifique se trouve dans une liste (1,2,3,4,5). Par exemple, sélectionnez les clients qui se trouvent dans les codes postaux suivants, par exemple les valeurs zip_code dans la liste (….).

Quand utiliser l’un sur l’autre … quand vous sentez qu’il se lit correctement (communique mieux l’intention).

La différence réside ici:

 select * from abcTable where exists (select null) 

La requête ci-dessus renverra tous les enregistrements alors que l’autre renverra vide.

 select * from abcTable where abcTable_ID in (select null) 

Essayez-le et observez le résultat.

À ma connaissance, lorsqu’une sous-requête renvoie une valeur NULL , l’instruction entière devient NULL . Dans ce cas, nous utilisons le mot clé EXITS . Si nous voulons comparer des valeurs particulières dans des sous-requêtes, nous utilisons le mot-clé IN .

Le plus rapide dépend du nombre de requêtes extraites par la requête interne:

  • Lorsque votre requête interne récupérant des milliers de lignes, alors EXIST serait le meilleur choix
  • Lorsque votre requête interne récupère quelques lignes, alors IN sera plus rapide

EXIST évalue sur true ou false mais IN compare plusieurs valeurs. Lorsque vous ne savez pas que le dossier existe ou non, vous devez choisir EXIST

La raison en est que l’opérateur EXISTS fonctionne sur la base du principe «au moins trouvé». Il renvoie true et arrête l’parsing de la table dès qu’au moins une ligne correspondante a été trouvée.

D’autre part, lorsque l’opérateur IN est combiné avec une sous-requête, MySQL doit d’abord traiter la sous-requête, puis utiliser le résultat de la sous-requête pour traiter toute la requête.

La règle générale est que si la sous-requête contient un grand volume de données, l’opérateur EXISTS fournit de meilleures performances.

Toutefois, la requête qui utilise l’opérateur IN fonctionnera plus rapidement si le jeu de résultats renvoyé par la sous-requête est très petit.

Je crois comprendre que les deux devraient être les mêmes tant que nous ne traitons pas les valeurs NULL.

La même raison pour laquelle la requête ne renvoie pas la valeur pour = NULL vs est NULL. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Comme pour l’argument boolean vs comparator, générer une valeur booléenne des deux valeurs doit être comparé et c’est comme ça que n’importe quelle condition fonctionne. Donc, je ne comprends pas comment IN et EXISTS se comportent différemment.

Si une sous-requête renvoie plusieurs valeurs, vous devrez peut-être exécuter la requête externe si les valeurs de la colonne spécifiée dans la condition correspondent à une valeur quelconque du jeu de résultats de la sous-requête. Pour effectuer cette tâche, vous devez utiliser le mot-clé in .

Vous pouvez utiliser une sous-requête pour vérifier si un ensemble d’enregistrements existe. Pour cela, vous devez utiliser la clause exist avec une sous-requête. Le mot-clé exist renvoie toujours la valeur true ou false.

Je crois que cela a une réponse simple. Pourquoi ne pas le vérifier auprès des personnes qui ont développé cette fonction dans leurs systèmes?

Si vous êtes un développeur MS SQL, voici la réponse directement de Microsoft.

IN :

Détermine si une valeur spécifiée correspond à une valeur dans une sous-requête ou dans une liste.

EXISTS :

Spécifie une sous-requête pour tester l’existence de lignes.

J’ai trouvé que l’utilisation du mot clé EXISTS est souvent très lente (c’est très vrai dans Microsoft Access). J’utilise plutôt l’opérateur de jointure de cette manière: should-i-use-the-keyword-sent-in-sql

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403

EXISTS est plus rapide en performance que IN. Si la plupart des critères de filtre sont en sous-requête, alors mieux vaut utiliser IN et Si la plupart des critères de filtrage sont dans la requête principale, alors mieux vaut utiliser EXISTS.

Si vous utilisez l’opérateur IN, le moteur SQL parsingra tous les enregistrements extraits de la requête interne. Par contre, si nous utilisons EXISTS, le moteur SQL arrête le processus d’parsing dès qu’il a trouvé une correspondance.