Comment utiliser des requêtes SQL imbriquées qui renvoient des jeux de lignes

Pour illustrer comment une requête SQL imbriqué renvoie un ensemble de lignes, imaginez que vous travaillez pour un intégrateur de systèmes de matériel informatique. Votre entreprise, Zetec Corporation, assemble des systèmes à partir de composants que vous achetez, puis il les vend aux entreprises et les organismes gouvernementaux. Vous gardez une trace de votre entreprise avec une base de données relationnelle.

La base de données se compose de plusieurs tableaux, mais maintenant vous êtes préoccupé avec seulement trois d'entre eux: le tableau des produits, la table COMP_USED, et la table de composant. Le tableau de produit contient une liste de tous vos produits standards.

La table de COMPOSANTE répertorie les composants qui entrent dans vos produits.

Les pistes de table COMP_USED composants qui entrent dans chaque produit.

Un composant peut être utilisé dans de nombreux produits, et un produit peut contenir plusieurs composants (un grand nombre de relations-to-many). Cette situation peut causer des problèmes d'intégrité. Pour contourner les problèmes, créer la table de liaison COMP_USED de relier COMPOSANT à PRODUIT. Un composant peut apparaître dans de nombreuses lignes de la table COMP_USED, mais chacune de ces lignes fera référence à un seul composant (une relation one-to-many).

De même, un produit peut apparaître dans de nombreuses lignes de COMP_USED, mais chacun références de ligne d'un seul produit (une autre relation un-à-plusieurs). Ajout de la table de liaison transforme une relation plusieurs-à-plusieurs gênant en deux relations relativement simples one-to-many. Ce procédé de réduction de la complexité de la relation est un exemple de normalisation.

Les sous-requêtes introduites par le mot-clé IN

Une forme d'une requête imbriquée compare une valeur unique à l'ensemble des valeurs renvoyées par un SELECT déclaration. Il utilise le DANS prédicat avec la syntaxe suivante:

SELECT column_listDE tableExpression WHERE IN (sous-requête) -

L'expression dans le OÙ article évalue à une valeur. Si cette valeur est DANS la liste renvoyée par la sous-requête, puis le OÙ clause renvoie une valeur True. Les colonnes spécifiées de la ligne de la table en cours de traitement sont ajoutés à la table de résultat. La sous-requête peut faire référence à la même table référencée par la requête externe, ou il peut faire référence à une table différente.

Dans l'exemple suivant, la base de données du Zetec est utilisé pour démontrer ce type de requête. Supposons qu'il ya une pénurie de moniteurs informatiques dans l'industrie de l'ordinateur, de sorte que lorsque vous exécutez des moniteurs, vous ne pouvez plus livrer des produits qui les contiennent. Vous voulez savoir quels produits sont touchés. Jetant un regard avec gratitude à votre propre moniteur, saisissez la requête suivante:

SELECT ModelFROM COMP_USEDWHERE compid IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor') -

SQL traite la requête la plus interne d'abord, il traite la table de COMPOSANTE, renvoyant la valeur de Compid pour chaque ligne où CompType est 'Monitor'. Le résultat est une liste de la ID les numéros de tous les moniteurs. La requête externe compare alors la valeur de Compid dans chaque ligne de la table COMP_USED contre la liste.

Si la comparaison est réussie, la valeur de la Modèle colonne pour cette ligne est ajoutée à la extérieure SELECTla table résultat de '. Le résultat est une liste de tous les modèles de produits qui comprennent un moniteur. L'exemple suivant montre ce qui se passe lorsque vous exécutez la requête:

Modèle ----- CX3000CX3010CX3020MB3030MX3020MX3030

Vous savez maintenant où les produits seront bientôt en rupture de stock. Il est temps d'aller à la force de vente et de leur dire de ralentir sur la promotion de ces produits.

Lorsque vous utilisez ce formulaire de requête imbriquée, la sous-requête doit préciser une seule colonne, et le type de données de cette colonne doit correspondre au type de l'argument de données précédant la DANS mot-clé.

Heureusement, vous souvenez-vous du principe KISS. Garder les choses simples est important quand vous avez affaire à des logiciels de tout genre, mais il est particulièrement important lorsqu'ils traitent avec le logiciel de base de données. Les déclarations qui contiennent imbriqués SELECTS peut être difficile d'obtenir le droit.

Une façon de les faire fonctionner comme ils le devraient consiste à exécuter l'intérieure SELECT par lui-même d'abord, puis vérifiez que le résultat que vous obtenez est le résultat que vous attendez. Lorsque vous êtes sûr que le intérieure SELECT fonctionne correctement, vous pouvez le joindre dans la partie extérieure de la déclaration et avoir une meilleure chance que le tout fonctionnera comme annoncé.

Les sous-requêtes introduites par le mot-clé NOT IN

Tout comme vous pouvez introduire une sous-requête avec le DANS mot-clé, vous pouvez faire l'inverse et l'introduire avec le PAS DEDANS mots-clés. En fait, il est maintenant un grand moment pour la gestion Zetec de faire une telle requête. Gestion de Zetec découvert ce que les produits de ne pas vendre. Voilà des informations précieuses, mais la gestion de ce Zetec veut vraiment savoir ce que les produits à vendre.

La direction veut mettre l'accent sur la vente de produits qui ne pas contenir des moniteurs. Une requête imbriquée avec une sous-requête introduite par le PAS DEDANS mots-clés fournit les informations demandées:

SELECT ModelFROM COMP_USEDWHERE compid NOT IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor')) -

Cette requête donne le résultat suivant:

Modèle ----- PX3040PB3050PX3040PB3050

Dans l'exemple, le nombre de lignes ne crée pas un problème parce que la table de résultats est courte. Dans le monde réel, un tel tableau de résultat peut avoir des centaines ou des milliers de lignes. Pour éviter toute confusion, il est préférable d'éliminer les doublons. Vous pouvez le faire facilement en ajoutant le DISTINCT mot-clé à la requête. Seules les lignes qui sont distinctes de toutes les lignes précédemment récupérés sont ajoutés à la table de résultat:

SELECT DISTINCT ModelFROM COMP_USEDWHERE compid NOT IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor')) -

Comme prévu, le résultat est le suivant:

Modèle ----- PX3040PB3050