FirebirdSQL logo
 COMMENTSInstructions de procédure SQL (PSQL) 

Connexions aux tables dérivées `LATERAL'.

Une table dérivée définie avec le mot-clé LATERAL est appelée table dérivée latérale.Si une table dérivée est définie comme une table latérale, elle est autorisée à faire référence à d’autres tables dans la même clause FROM, mais seulement à celles déclarées plus tôt dans la clause FROM.Sans LATERAL, chaque sous-requête est exécutée indépendamment et ne peut donc pas faire référence à d’autres éléments FROM.

L’élément LATERAL peut se trouver au niveau supérieur de la liste FROM ou dans l’arbre JOIN.Dans ce dernier cas, il peut également être lié à tout élément situé à gauche du JOIN à droite duquel il se trouve.

Lorsqu’un élément FROM contient des références LATERAL, alors la requête est exécutée de la manière suivante : d’abord les valeurs de toutes les colonnes dont dépend la table dérivée avec le mot-clé LATERAL, puis la table dérivée elle-même avec LATERAL pour chaque enregistrement résultant est calculée.Les lignes obtenues à partir de la table dérivée avec LATERAL sont jointes avec les lignes dont elles sont dérivées.

Les jointures suivantes sont autorisées : CROSS JOIN et LEFT OUTER JOIN.Une jonction interne est également autorisée, mais n’est pas recommandée car des problèmes peuvent survenir lors du calcul de la condition de jonction des fils.

A titre d’exemple, imprimez les résultats des chevaux et leurs dernières mesures.Si un cheval n’a pas de mensurations, il ne sera pas sorti :

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
CROSS JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M

une autre façon d’écrire cette demande

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE,
     LATERAL(SELECT
               *
             FROM MEASURE
             WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
             ORDER BY MEASURE.BYDATE DESC
             FETCH FIRST ROW ONLY) M

Si vous voulez sortir les chevaux sans tenir compte du fait qu’ils ont au moins une mesure, vous devez remplacer CROSS JOIN par LEFT JOIN :

SELECT
    HORSE.NAME,
    M.BYDATE,
    M.HEIGHT_HORSE,
    M.LENGTH_HORSE
FROM HORSE
LEFT JOIN LATERAL(SELECT
                       *
                   FROM MEASURE
                   WHERE MEASURE.CODE_HORSE = HORSE.CODE_HORSE
                   ORDER BY MEASURE.BYDATE DESC
                   FETCH FIRST ROW ONLY) M ON TRUE

WHERE

La clause WHERE'' a pour but de limiter le nombre de chaînes retournées à celles qui nous intéressent.La condition qui suit le mot-clé WHERE peut être aussi simple que la vérification de “AMOUNT = 3``”, ou peut être une expression complexe et alambiquée contenant des sous-requêtes, des prédicats, des appels de fonction, des instructions mathématiques et logiques, des variables de contexte, etc.

La condition dans une clause WHERE est souvent appelée une condition de recherche, une expression de recherche, ou simplement une recherche.

Dans DSQL et ESQL, une expression de recherche peut contenir des paramètres.Ceci est utile si la requête doit être répétée plusieurs fois avec des valeurs différentes des paramètres d’entrée.Dans la chaîne de requête SQL envoyée au serveur, les points d’interrogation sont utilisés comme caractères de remplacement pour les paramètres.Ils sont appelés paramètres positionnels car ils ne peuvent rien dire d’autre que la position dans la chaîne.Les bibliothèques d’accès prennent souvent en charge des paramètres nommés tels que :id, :amount, :a etc.C’est plus convivial, la bibliothèque se charge de traduire les paramètres nommés en paramètres de position avant de transmettre la requête au serveur.

Une condition de recherche peut également contenir des noms de variables locales (PSQL) ou hôtes (ESQL), précédés de deux points.

Syntaxe
SELECT ...
  FROM ...
  [...]
  WHERE <search-condition>
  [...]
Table 1. Paramètres de la clause WHERE
Paramètre Description

search-condition

Une expression logique retournant TRUE, FALSE et éventuellement UNKNOWN (NULL).

Seules les chaînes pour lesquelles la condition de recherche est vraie seront incluses dans le jeu de résultats. Faites attention aux valeurs NULL qui peuvent en résulter : si vous niez une expression donnant NULL avec NOT, le résultat d’une telle expression est toujours NULL et la chaîne ne passera pas. Ceci est démontré dans un des exemples ci-dessous.

Exemples
SELECT genus, species
FROM mammals
WHERE family = 'Felidae'
ORDER BY genus;

SELECT *
FROM persons
WHERE birthyear IN (1880, 1881)
   OR birthyear BETWEEN 1891 AND 1898;

SELECT name, street, borough, phone
FROM schools s
WHERE EXISTS (SELECT * FROM pupils p WHERE p.school = s.id)
ORDER BY borough, street;

SELECT *
FROM employees
WHERE salary >= 10000 AND position <> 'Manager';

SELECT name
FROM wrestlers
WHERE region = 'Europe'
  AND weight > ALL (SELECT weight FROM shot_putters
                    WHERE region = 'Africa');

SELECT id, name
FROM players
WHERE team_id = (SELECT id FROM teams
                 WHERE name = 'Buffaloes');

SELECT SUM (population)
FROM towns
WHERE name LIKE '%dam'
  AND province CONTAINING 'land';

SELECT pass
FROM usertable
WHERE username = current_user;

L’exemple suivant montre ce qui peut se passer si la condition de recherche est calculée comme NULL.

Supposons que vous ayez un tableau avec plusieurs noms d’enfants et le nombre de billes qu’ils possèdent.

CHILD MARBLES

Anita

23

Bob E.

12

Chris

<null>

Deirdre

1

Eve

17

Fritz

0

Gerry

21

Hadassah

<null>

Isaac

6

Tout d’abord, notez la différence entre NULL et 0. On sait que Fritz n’a aucune bille, mais Chris et Hadassah ont un nombre inconnu de billes.

Maintenant, si vous entrez cette instruction SQL :

SELECT LIST(child) FROM marbletable WHERE marbles > 10

vous obtiendrez les noms Anita, Bob E., Eve et Gerry. Tous ces enfants ont plus de 10 billes.

Si vous niez l’expression :

SELECT LIST(child) FROM marbletable WHERE NOT marbles > 10

retournera Deirdre, Fritz et Isaac. Chris et Hadassah ne seront pas inclus dans l’échantillon, car on ne sait pas s’ils ont 10 billes ou moins. Si vous modifiez la dernière requête comme suit

SELECT LIST(child) FROM  marbletable WHERE marbles <= 100

le résultat est le même, car l’expression NULL <= 10 donne UNKNOWN. Ce n’est pas la même chose que TRUE, donc Chris et Hadassah ne sont pas affichés. Si vous voulez que tous les enfants "pauvres" soient listés, modifiez la requête comme suit

SELECT LIST(child)
FROM marbletable
WHERE marbles <= 10 OR marbles IS NULL

Maintenant, la condition de recherche devient vraie pour Chris et Hadassah, parce que la condition "marbles is null retourne TRUE` dans ce cas.En fait, la condition de recherche ne peut être NULL pour aucun d’entre eux.

Enfin, les deux exemples suivants de requêtes SELECT avec des paramètres dans la condition de recherche.La manière dont les paramètres de la requête sont définis et la possibilité de le faire dépendent de l’application.Notez que les requêtes de ce type ne peuvent pas être exécutées immédiatement, elles doivent être préparées à l’avance.Une fois qu’une requête paramétrée a été préparée, l’utilisateur (ou le code appelant) peut substituer des valeurs de paramètres et l’exécuter à plusieurs reprises, en substituant de nouvelles valeurs de paramètres avant chaque appel.La manière dont les valeurs des paramètres sont saisies et le fait qu’elles soient ou non prétraitées dépendent de l’application.Dans les environnements GUI, l’utilisateur saisit généralement les valeurs des paramètres dans une ou plusieurs zones de texte et clique sur le bouton "Exécuter", "Run" ou "Refresh".

SELECT name, address, phone
FROM stores
WHERE city = ? AND class = ?

SELECT *
FROM pants
WHERE model = :model AND size = :size AND color = :col

La dernière requête ne peut pas être envoyée directement au moteur du serveur, l’application doit la convertir dans un autre format, en faisant correspondre les paramètres nommés aux paramètres de position.

GROUP BY

Une clause GROUP BY joint les enregistrements qui ont la même combinaison de valeurs de champs spécifiés dans sa liste en un seul enregistrement.Les fonctions d’agrégation dans la liste de sélection s’appliquent à chaque groupe individuellement, et non à l’ensemble.

Si la liste de sélection ne contient que des colonnes agrégées ou des colonnes dont les valeurs ne dépendent pas des lignes individuelles du jeu principal, la clause GROUP BY est facultative.Lorsque la clause GROUP BY est omise, le jeu de résultats se compose d’une seule ligne (à condition qu’au moins une colonne d’agrégation soit présente).

Si la liste de sélection contient à la fois des colonnes agrégées et des colonnes dont les valeurs dépendent des lignes sélectionnées, la clause GROUP BY devient obligatoire.

Syntaxe
SELECT ...
FROM ...
GROUP BY <grouping-item> [, <grouping-item> ...]
[HAVING <grouped-row-condition>] ...

<grouping-item> ::= <non-aggr-select-item> | <non-aggr-expression>

<non-aggr-select-item> ::=
    column-copy
  | column-alias
  | column-position
Table 1. Paramètres de la clause GROUP BY
Paramètre Description

non-aggr-expression

Toute expression non agrégée qui n’est pas incluse dans la liste d’échantillons, c’est-à-dire les colonnes non sélectionnées de l’ensemble source ou les expressions qui ne dépendent pas du tout de l’ensemble de données.

column-copy

Copie mot à mot d’une expression d’une liste de sélection qui ne contient pas de fonction d’agrégation.

column-alias

Alias d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.

column-position

Le numéro de position d’une expression (colonne) d’une liste de sélection qui ne contient pas de fonction d’agrégation.

La règle générale est que chaque colonne non agrégée dans une liste SELECT doit également être incluse dans une liste GROUP BY.Vous pouvez le faire de trois façons :

  1. Copiez l’expression textuelle de la liste de sélection, par exemple “class” ou “'D:' || upper(doccode)” ;

  2. Spécifiez un alias s’il en existe un ;

  3. Spécifiez la position de la colonne sous forme d’un nombre entier compris entre 1 et le nombre de colonnes de la liste SELECT. Les valeurs entières dérivées d’expressions, de paramètres ou simplement d’invariants seront utilisées comme telles dans le regroupement. Ils n’auront aucun effet car leur valeur est la même pour chaque ligne.

Important

Si vous regroupez par position de colonne ou par alias, l’expression correspondant à cette position (alias) sera copiée de la liste de sélection SELECT. Ceci s’applique aussi aux sous-requêtes, donc la sous-requête sera exécutée au moins deux fois.

En plus des éléments requis, la liste de regroupement peut également contenir :

  • Les colonnes de la table source qui ne sont pas incluses dans la liste de sélection SELECT, ou les expressions non agrégées basées sur de telles colonnes. L’ajout de telles colonnes peut encore fragmenter les groupes. Mais comme ces colonnes ne figurent pas dans la liste de sélection SELECT, vous ne pouvez pas savoir quelle valeur de colonne correspond à quelle valeur de ligne agrégée. Donc, si vous êtes intéressé par cette information, vous devez également inclure cette colonne ou expression dans la liste de sélection SELECT, ce qui vous ramène à la règle "chaque colonne non agrégée dans la liste de sélection SELECT doit être incluse dans la liste de regroupement GROUP BY ;

  • Expressions qui ne dépendent pas des données de l’ensemble principal, c’est-à-dire les constantes, les variables contextuelles, les sous-requêtes non corrélées qui renvoient une seule valeur, etc. Ceci n’est mentionné que pour compléter l’image, car l’ajout de ces éléments est complètement inutile, puisqu’ils n’affecteront pas du tout le regroupement. Les éléments "anodins mais inutiles" peuvent également apparaître dans la liste de sélection SELECT sans être copiés dans la liste de regroupement GROUP BY.

Exemples

Lorsque la liste de sélection SELECT ne contient que des colonnes agrégées, la clause GROUP BY est facultative :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'

Cette requête renvoie une seule ligne avec le nombre d’étudiants masculins et leur âge moyen. L’ajout d’une expression indépendante des lignes de la table STUDENTS ne change rien :

SELECT COUNT(*), AVG(age), current_date
FROM students
WHERE sex = 'M'

La ligne de résultat comportera désormais une colonne supplémentaire indiquant la date du jour, mais à part cela, rien de fondamental n’a changé. Le regroupement n’est toujours pas nécessaire.

Cependant, dans les deux exemples ci-dessus, cela est autorisé, ce qui est parfaitement valable pour la requête également :

SELECT COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

et retournera le résultat pour chaque classe qui a des garçons, en listant le nombre de garçons et leur âge moyen dans cette classe particulière. Si vous laissez également le champ CURRENT_DATE, cette valeur sera répétée sur chaque ligne, ce qui n’est pas intéressant.

Cette requête a un inconvénient important, bien qu’elle vous donne des informations sur les différentes classes, elle ne vous dit pas quelle ligne appartient à quelle classe. Pour obtenir cette information supplémentaire, la colonne non agrégée CLASS doit être ajoutée à la liste de sélection SELECT :

SELECT class, COUNT(*), AVG(age)
FROM students
WHERE sex = 'M'
GROUP BY class

Nous avons maintenant une requête utile. Notez que l’ajout de la colonne CLASS rend la clause GROUP BY obligatoire. Nous ne pouvons pas enlever cette clause, ni enlever la colonne CLASS de la liste des colonnes.

Le résultat de la dernière requête ressemblera à ceci :

CLASS COUNT AVG

2A

12

13.5

2B

9

13.9

3A

11

14.6

3B

12

14.4

…​

…​

…​

Les rubriques “COUNT” et “AVG” ne sont pas très informatives. Dans le cas le plus simple, vous pouvez contourner ce problème, mais il est préférable de leur donner des noms significatifs en utilisant des alias :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class

Comme vous vous souvenez de la syntaxe formelle de la liste des colonnes, le mot clé AS est facultatif.

L’ajout d’autres colonnes non agrégées (ou plus précisément dépendantes des chaînes de caractères) nécessite de les ajouter également aux clauses GROUP BY. Par exemple, vous voulez que les informations ci-dessus concernant les filles soient les mêmes, et vous voulez voir la différence entre les étudiants internes et les étudiants à plein temps :

SELECT
    class,
    sex,
    boarding_type,
    COUNT(*) AS anumber,
    AVG(age) AS avg_age
FROM students
GROUP BY class, sex, boarding_type
CLASS SEX BOARDING_TYPE ANUMBER AVG_AGE

2A

F

BOARDING

9

13.3

2A

F

DAY

6

13.5

2A

M

BOARDING

7

13.6

2A

M

DAY

5

13.4

2B

F

BOARDING

11

13.7

2B

F

DAY

5

13.7

2B

M

BOARDING

6

13.8

…​

…​

…​

…​

…​

Chaque ligne du jeu résultant correspond à une combinaison particulière des variables CLASS, SEX et BOARDING_TYPE. Les résultats agrégés — nombre et âge moyen — sont donnés pour chacun des groupes spécifiés séparément. Vous ne pouvez pas voir les résultats agrégés pour les garçons séparément ou pour les étudiants à temps plein séparément comme résultat de la requête.Il faut donc trouver un compromis. Plus vous ajoutez de colonnes non agrégées, plus vous instanciez les groupes, et plus vous perdez de vue la vue d’ensemble. Bien sûr, vous pouvez toujours obtenir des agrégats "plus grands", en utilisant des requêtes séparées.