Sous-requêtes pour résoudre des requêtes
Une sous-requête est mieux définie comme une requête dans une requête. Les sous-requêtes vous permettent d'écrire des requêtes qui sélectionnent des lignes de données pour des critères qui sont réellement développés pendant l'exécution de la requête au moment de l'exécution. Plus formellement, il s'agit de l'utilisation d'une instruction SELECT à l'intérieur d'une des clauses d'une autre instruction SELECT. En fait, une sous-requête peut être contenue dans une autre sous-requête, qui est à l'intérieur d'une autre sous-requête, et ainsi de suite. Une sous-requête peut également être imbriquée dans des instructions INSERT, UPDATE et DELETE. Les sous-requêtes doivent être placées entre parenthèses.
Une sous-requête peut être utilisée n'importe où où une expression est autorisée à condition qu'elle renvoie une seule valeur. Cela signifie qu'une sous-requête qui renvoie une valeur unique peut également être répertoriée en tant qu'objet dans une liste de clauses FROM. Ceci est appelé une vue en ligne car lorsqu'une sous-requête est utilisée dans le cadre d'une clause FROM, elle est traitée comme une table ou une vue virtuelle. La sous-requête peut être placée dans la clause FROM, la clause WHERE ou la clause HAVING de la requête principale.
Oracle autorise une imbrication maximale de 255 niveaux de sous-requête dans une clause WHERE. Il n'y a pas de limite d'imbrication des sous-requêtes exprimées dans une clause FROM. En pratique, la limite de 255 niveaux n'est pas vraiment une limite car il est rare de rencontrer des sous-requêtes imbriquées au-delà de trois ou quatre niveaux.
Une instruction SELECT de sous-requête est très similaire à l'instruction SELECT utilisée pour commencer une requête normale ou externe.La syntaxe complète d'une sous-requête est:
( SELECT [DISTINCT] subquery_select_parameter
FROM {table_name | view_name}
{table_name | view_name} ...
[WHERE search_conditions]
[GROUP BY column_name [,column_name ] ...]
[HAVING search_conditions] )Types de sous-requêtes
Single Row Sub Query: Sous-requête qui renvoie une sortie sur une seule ligne. Ils marquent l'utilisation d'opérateurs de comparaison à une seule ligne, lorsqu'ils sont utilisés dans des conditions WHERE.
Multiple row sub query: Sous-requête renvoyant une sortie sur plusieurs lignes. Ils utilisent plusieurs opérateurs de comparaison de lignes comme IN, ANY, ALL. Il peut y avoir des sous-requêtes renvoyant également plusieurs colonnes.
Correlated Sub Query: Les sous-requêtes corrélées dépendent des données fournies par la requête externe. Ce type de sous-requête inclut également les sous-requêtes qui utilisent l'opérateur EXISTS pour tester l'existence de lignes de données répondant aux critères spécifiés.
Sous-requête à une seule ligne
Une sous-requête à une seule ligne est utilisée lorsque les résultats de la requête externe sont basés sur une seule valeur inconnue. Bien que ce type de requête soit formellement appelé «une seule ligne», le nom implique que la requête renvoie plusieurs colonnes, mais une seule ligne de résultats. Cependant, une sous-requête à une seule ligne ne peut renvoyer qu'une seule ligne de résultats constituée d'une seule colonne à la requête externe.
Dans la requête SELECT ci-dessous, le SQL interne ne renvoie qu'une seule ligne, c'est-à-dire le salaire minimum de l'entreprise. Il utilise à son tour cette valeur pour comparer le salaire de tous les employés et n'affiche que ceux dont le salaire est égal au salaire minimum.
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary)
FROM employees);Une clause HAVING est utilisée lorsque les résultats de groupe d'une requête doivent être restreints en fonction d'une condition. Si le résultat d'une sous-requête doit être comparé à une fonction de groupe, vous devez imbriquer la requête interne dans la clause HAVING de la requête externe.
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees)Requête de sous-requête sur plusieurs lignes
Les sous-requêtes à plusieurs lignes sont des requêtes imbriquées qui peuvent renvoyer plusieurs lignes de résultats à la requête parente. Les sous-requêtes à plusieurs lignes sont le plus souvent utilisées dans les clauses WHERE et HAVING. Puisqu'il renvoie plusieurs lignes, il doit être géré par des opérateurs de comparaison définis (IN, ALL, ANY) .Bien que l'opérateur IN ait la même signification que celle décrite dans le chapitre précédent, l'opérateur ANY compare une valeur spécifiée à chaque valeur retournée par la sous-requête tandis que ALL compare une valeur à chaque valeur renvoyée par une sous-requête.
La requête ci-dessous montre l'erreur lorsque la sous-requête à une seule ligne renvoie plusieurs lignes.
SELECT first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one rowUtilisation d'opérateurs à plusieurs lignes
[> ALL] Plus que la valeur la plus élevée renvoyée par la sous-requête
[<ALL] Inférieur à la valeur la plus basse renvoyée par la sous-requête
[<ANY] Inférieur à la valeur la plus élevée renvoyée par la sous-requête
[> ANY] Plus que la valeur la plus basse renvoyée par la sous-requête
[= ANY] Égal à toute valeur renvoyée par la sous-requête (identique à IN)
Ci-dessus, SQL peut être réécrit en utilisant l'opérateur IN comme ci-dessous.
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE LOCATION_ID = 100)Notez que dans la requête ci-dessus, IN correspond aux identifiants de service renvoyés par la sous-requête, le compare à celui de la requête principale et renvoie le nom de l'employé qui satisfait la condition.
Une jointure serait une meilleure solution pour la requête ci-dessus, mais à des fins d'illustration, une sous-requête a été utilisée.
Sous-requête corrélée
Contrairement à une sous-requête normale, où la requête externe dépend des valeurs fournies par la requête interne, une sous-requête corrélée est une sous-requête où la requête interne dépend des valeurs fournies par la requête externe. Cela signifie que dans une sous-requête corrélée, la requête interne est exécutée à plusieurs reprises, une fois pour chaque ligne pouvant être sélectionnée par la requête externe.
Les sous-requêtes corrélées peuvent produire des tableaux de résultats qui répondent à des questions de gestion complexes.
Considérez la requête SELECT ci-dessous. Contrairement aux sous-requêtes précédemment considérées, la sous-requête de cette instruction SELECT ne peut pas être résolue indépendamment de la requête principale. Notez que la requête externe spécifie que les lignes sont sélectionnées dans la table des employés avec un nom d'alias e1. La requête interne compare la colonne du numéro de service des employés (DepartmentNumber) de la table des employés avec l'alias e2 à la même colonne pour le nom de la table d'alias e1.
SELECT EMPLOYEE_ID, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary)
FROM EMP T
WHERE E.department_id = T.department_id)Sous-requête à plusieurs colonnes
Une sous-requête à plusieurs colonnes renvoie plusieurs colonnes à la requête externe et peut être répertoriée dans la clause FROM, WHERE ou HAVING de la requête externe. Par exemple, la requête ci-dessous montre les détails historiques de l'employé pour ceux dont le salaire actuel est compris entre 1000 et 2000 et travaillant dans le service 10 ou 20.
SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000
AND department_id BETWEEN 10 and 20)
ORDER BY first_name;Lorsqu'une sous-requête à plusieurs colonnes est utilisée dans la clause FROM de la requête externe, elle crée une table temporaire qui peut être référencée par d'autres clauses de la requête externe. Cette table temporaire est plus formellement appelée une vue en ligne. Les résultats de la sous-requête sont traités comme n'importe quelle autre table dans la clause FROM. Si la table temporaire contient des données groupées, les sous-ensembles groupés sont traités comme des lignes de données séparées dans une table. Considérez la clause FROM dans la requête ci-dessous. La vue en ligne formée par la sous-requête est la source de données de la requête principale.
SELECT *
FROM (SELECT salary, department_id
FROM employees
WHERE salary BETWEEN 1000 and 2000);