informer

...
C'est une traduction G de la page Whether to EXISTS or NOT EXISTS ... écrite par Ben Clothier


Un peu de théorie

La différence entre IN() et EXISTS() est que IN() renvoie un ensemble de résultats qui est ensuite évalué par une requête principale. EXISTS() renvoie simplement une valeur booléenne sans renvoyer aucune donnée sur la sous-requête. Pour illustrer, considérons cette déclaration*:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT *
FROM tblOrders
WHERE CustomerID IN (
SELECT CustomerID
FROM tblCustomers
WHERE State = 'IL'
);

Supposons que nous ayons 3 clients dans l'état de l'Illnois. Le IN() peut être considéré comme équivalent à ceci*:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM tblOrders
WHERE CustomerID IN (4, 8, 15);

C'est-à-dire que la sous-requête renvoie une liste de valeurs possibles qui est ensuite évaluée par la requête externe pour déterminer si la commande doit être sortie ou non.

Voyons comment nous pouvons exprimer la même chose en utilisant EXISTS.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT *
FROM tblOrders
WHERE EXISTS (
SELECT NULL
FROM tblCustomers
WHERE tblOrders.CustomerID = tblCustomers.CustomerID
AND tblCustomers.State = 'IL'
);

Il existe un certain nombre de différences. Examinons-les un par un.

Tout d'abord, vous remarquerez que nous ne disons pas "CustomerID = EXISTS(…)". EXISTS() lui-même ne renvoie qu'une valeur booléenne et n'a donc pas besoin d'être comparé à une colonne et pour cette raison, il se tient seul sans aucune comparaison.

Deuxièmement, nous relions la requête interne en corrélant tblOrders.CustomerID de la requête externe à tblCustomers.CustomerID. Certains d'entre vous se demandent peut-être «*mais les sous-requêtes corrélées ne sont-elles pas mauvaises*?*» Pas toujours le cas. Par exemple, SQL Server a tendance à traiter un EXISTS comme une "semi-jointure" et l'évalue donc assez efficacement.

Troisièmement, nous appliquons les mêmes critères que ceux que nous avons utilisés dans l'exemple d'origine, en filtrant uniquement les clients qui se trouvent dans l'Illinois. Par conséquent, pour tous les clients dont l'ID est, disons, 3 et qui est dans l'état CA, la sous-requête évaluerait logiquement les résultats à zéro, ce qui fait que EXISTS() renvoie false. Cela nous amène au point suivant.

Enfin, le contenu de la sous-requête est totalement hors de propos. J'ai écrit "SELECT NULL" car il n'y a rien qui sera évalué dans le SELECT. Si vous n'êtes pas convaincu, essayez ceci*:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
... WHERE EXISTS (
SELECT 1 / 0
FROM tblCustomers
);

La division par zéro devrait sûrement entraîner l'échec de la requête avec une erreur, n'est-ce pas*? Du tout . Cela retourne comme on le voit des enregistrements de la table tblCustomers et donc il retourne vrai. Cela nous fait gagner une étape par rapport à IN() qui nécessite l'évaluation de la sortie de IN(). La seule chose qui devrait importer est de savoir s'il y a des enregistrements nuls ou non nuls correspondants à la suite de la sous-requête. C'est pourquoi nous avons 2 critères; correspondance sur le CustomerID avec le tblOrders.CustomerID en plus des critères d'état.

Et maintenant deux exemples

Les exemples ont vocation a illustrer la théorie et en raison de leur simplicité correspondent respectivement à un :

• INNER JOIN
• LEFT JOIN

Soit

• La table tblTout
• La table tblUnePartie2Tout

Les valeurs pour tblTout
field1 Field2
aeze1 aeze1
aeze2 aeze2
aeze3 aeze3
aeze4 aeze4
aeze5 aeze5
aeze6 aeze6
aeze7 aeze7
aeze8 aeze8
aeze9 aeze9

Les valeurs pour tblUnePartie2Tout
field1 Field2
aeze7 aeze7
aeze8 aeze8
aeze9 aeze9

Pour obtenir les lignes de tblTout qui existent dans les 2 tables

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM tblTout WHERE EXISTS ( SELECT NULL FROM tblUnePartie2Toutwhere  tblTout.field1 = tblUnePartie2Tout.field1 )

Résultat
field1 Field2
aeze7 aeze7
aeze8 aeze8
aeze9 aeze9

Pour obtenir les lignes de tblTout qui n'existent que dans la table tblTout

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM tblTout WHERE NOT EXISTS ( SELECT NULL FROM tblUnePartie2Toutwhere  tblTout.field1 = tblUnePartie2Tout.field1 )

Résultat
field1 Field2
aeze1 aeze1
aeze2 aeze2
aeze3 aeze3
aeze4 aeze4
aeze5 aeze5
aeze6 aeze6

Pour terminer, de EXISTS or IN, lequel est le plus rapide ?

De manière générale, les jointures doivent être la première chose à essayer lors de la création de la requête. Nous envisagerions d'utiliser IN() ou EXISTS() si nous voulions comparer les données d'autres tables sans modifier la sortie comme le fait la jointure. Cela a tendance à dépendre davantage du schéma et du contenu de la requête - parfois, IN() est le plus performant car il est capable d'évaluer l'ensemble de la sous-requête à la fois, puis utilisé comme 2ème étape. Parfois, EXISTS() est préférable car il n'y a pas de jeu de résultats à évaluer. Pour les requêtes plus petites, vous ne verrez pas de différences significatives dans les performances des deux méthodes. Pour les requêtes plus volumineuses ou lorsque les requêtes sont exécutées fréquemment, vous souhaiterez peut-être comparer et voir ce qui est le plus adapté au travail. Notez en outre que différents moteurs de base de données ont des «*préférences*» différentes*-*Considérez ces deux approches différentes que nous pouvons utiliser pour afficher uniquement les commandes qui n'ont aucun client associé à la commande*:

jointure forcée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 SELECT *
FROM tblOrders
LEFT JOIN tblCustomers
ON tblOrders.CustomerID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerID IS NULL;

Notez comment la jointure est «*forcée*» par le prédicat «*tblCustomers.CustomerID IS NULL*», qui limite la sortie aux seules commandes qui n'ont pas de clients

Contrôle d'existence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT *
FROM tblOrders
WHERE NOT EXISTS (
SELECT NULL
FROM tblCustomers
WHERE tblCustomers.CustomerID = tblOrders.CustomerID
);

De même, nous corrélons le CustomerID entre deux tables dans la clause EXISTS et notons NOT pour inverser le résultat qui serait généré.

Les deux approches donneraient un résultat identique, c'est-à-dire que nous obtenons une liste de commandes auxquelles aucun client n'est associé. Le moteur de base de données Access est plus susceptible de préférer la jointure "forcée" alors que SQL Server tend vers la vérification d'existence.

Différents moteurs de base de données ont également leurs «*préférences*», donc pour les requêtes où les performances sont cruciales pour vous, il est généralement utile de comparer différentes expressions de SQL et de voir laquelle fonctionne le mieux pour votre requête spécifique.

Comme mentionné précédemment, le schéma et le contenu de la requête peuvent fortement influencer les performances, ils doivent donc être la première chose à considérer. Mais lorsque vous devez travailler avec le schéma tel qu'il est, il est très utile de reconnaître les modèles SQL courants pour le même problème afin d'avoir plus d'outils à votre disposition pour tirer le meilleur parti de la requête la plus compliquée.