FirebirdSQL logo

Tableaux de dérivés latéraux

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 latérale, elle est autorisée à faire référence à d’autres tables dans la même clause FROM, mais seulement à celles qui ont été déclarées avant dans la clause FROM.

Example 1. Requêtes avec des tables dérivées latéralement
select dt.population, dt.city_name, c.country_name
from (select distinct country_name from cities) AS c,
LATERAL (select first 1 city_name, population
         from cities
         where cities.country_name = c.country_name
         order by population desc) AS dt;
select salespeople.name,
       max_sale.amount,
       customer_of_max_sale.customer_name
from salespeople,
LATERAL ( select max(amount) as amount from all_sales
          where all_sales.salesperson_id = salespeople.id
         ) as max_sale,
LATERAL ( select customer_name from all_sales
          where all_sales.salesperson_id = salespeople.id
            and all_sales.amount = max_sale.amount
        ) as customer_of_max_sale;

Échantillonnage à partir d’expressions de tables communes (CTE)

Les expressions de tables communes sont une variante plus complexe et plus puissante des tables dérivées. Les CTE sont constituées d’un préambule commençant par le mot clé WITH qui définit une ou plusieurs expressions de tables communes (chacune d’entre elles peut avoir une liste d’alias de champs). La requête principale, qui suit le préambule, peut faire référence aux CTE comme s’il s’agissait de tableaux réguliers. Les CTE sont disponibles pour toute partie de la requête située en dessous de leur point de déclaration.

Les CTE sont décrits en détail dans CTE Common table expressions (WITH …​ AS …​ SELECT), et voici juste quelques utilisations en exemples.

La requête suivante présente notre exemple avec une variante de table dérivée pour les expressions de table génériques :

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
)
SELECT
  IIF (D >= 0, (-b - sqrt(D)) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrt(D)) / denom, NULL) AS sol_2
FROM vars

Ce n’est pas une grande amélioration par rapport à l’option des tableaux dérivés (sauf que les calculs sont effectués avant la requête principale). Nous pouvons encore améliorer la requête en éliminant le double calcul sqrt(D) pour chaque ligne :

WITH vars (b, D, denom) AS (
  SELECT b, b*b - 4*a*c, 2*a
  FROM coeffs
),
vars2 (b, D, denom, sqrtD) AS (
  SELECT
    b, D, denom,
    IIF (D >= 0, sqrt(D), NULL)
  FROM vars
)
SELECT
  IIF (D >= 0, (-b - sqrtD) / denom, NULL) AS sol_1,
  IIF (D > 0, (-b + sqrtD) / denom, NULL) AS sol_2
FROM vars2

Le texte de la requête semble plus complexe, mais il est devenu plus efficace (en supposant que l’exécution de la fonction SQRT prend plus de temps que le passage des valeurs des variables b, d et denom par un CTE supplémentaire).

Note

En fait, toutes les colonnes calculées dans le CTE seront recalculées autant de fois qu’elles sont spécifiées dans la requête principale. Cela peut conduire à des résultats inattendus lors de l’utilisation de fonctions non déterministes. L’exemple suivant montre ce qu’il en est :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              C1214CD3-423C-406D-B5BD-95BF432ED3E3
C3                              EB176C10-F754-4689-8B84-64B666381154

Vous pouvez utiliser la méthode suivante pour matérialiser le résultat de la fonction GEN_UUID :

WITH T(X)
AS (SELECT GEN_UUID()
    FROM RDB$DATABASE
    UNION ALL
    SELECT NULL FROM RDB$DATABASE WHERE 1=0)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

le résultat de cette demande sera

C1                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C2                              80AAECED-65CD-4C2F-90AB-5D548C3C7279
C3                              80AAECED-65CD-4C2F-90AB-5D548C3C7279

ou envelopper la fonction GEN_UUID dans une sous-requête

WITH T(X)
AS (SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
    FROM RDB$DATABASE)
SELECT
    UUID_TO_CHAR(X) as c1,
    UUID_TO_CHAR(X) as c2,
    UUID_TO_CHAR(X) as c3
FROM T;

Il s’agit d’une caractéristique de l’implémentation actuelle qui peut être modifiée dans les futures versions du serveur.

Bien entendu, nous pourrions également obtenir ce résultat en utilisant des tableaux dérivés, mais cela nécessiterait d’imbriquer les requêtes les unes dans les autres.

Connexions JOIN

Une jointure est effectuée pour chaque ligne et implique généralement la vérification de la condition de jointure pour déterminer quelles lignes doivent être jointes et se retrouver dans le jeu de données résultant.

Le résultat d’une jointure peut également être joint à un autre ensemble de données en utilisant la jointure suivante.

Il existe plusieurs types (INNER, OUTER) et classes (qualifiées, naturelles, etc.) de jointures, chacune ayant sa propre syntaxe et ses propres règles.

Syntaxe
SELECT
...
FROM <table-reference> [, <table-reference> ...]
[...]

<table-reference> ::= <table-primary> | <joined-table>

<table-primary> ::=
    <table-or-query-name> [[AS] correlation-name]
  | [LATERAL] <derived-table> [<correlation-or-recognition>]
  | <parenthesized-joined-table>

<table-or-query-name> ::=
    table-name
  | query-name
  | [package-name.]procedure-name [(<procedure-args>)]

<procedure-args> ::= <value-expression [, <value-expression> ...]

<derived-table> ::= (<query-expression>)

<correlation-or-recognition> ::=
  [AS] correlation-name [(<column-name-list>)]

<column-name-list> ::= column-name [, column-name ...]

<parenthesized-joined-table> ::=
    (<parenthesized-joined-table>)
  | (<joined-table>)

<joined-table> ::=
    <cross-join>
  | <natural-join>
  | <qualified-join>

<cross-join> :: =
  <table-reference> CROSS JOIN <table-primary>

<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

<qualified-join> ::=
  <table-reference> [<join-type>] JOIN <table-primary>
  {   ON <search-condition>
    | USING (<column-name-list>) }
Table 1. Paramètres de JOIN
Paramètre Description

table-name

Le nom de la table ou de la vue.

query-name

Nom du CTE.

package-name

Nom du paquet.

procedure-name

Le nom de la procédure stockée sélective.

procedure-args

Arguments d’une procédure stockée sélective.

derived-table

Table dérivée

correlation-name

Un alias d’une source de données (table, vue, procédure stockée, CTE ou table dérivée).

column-name

Nom ou alias d’une colonne de source de données (table, vue, procédure stockée, CTE ou table dérivée).

select-statement

Requête de la demande.

search-condition

Condition de connexion.

column-name-list

Une liste d’alias de colonnes de la table dérivée ou une liste de colonnes équivalentes.

Connexions internes (`INNER') et externes (`OUTER')

Une jointure réunit toujours les lignes de deux ensembles de données (généralement appelés "gauche" et "droite"). Par défaut, seules les lignes qui satisfont la condition de jointure (c’est-à-dire qui correspondent à au moins une ligne de l’autre ensemble de lignes selon la condition appliquée) sont incluses dans l’ensemble de données résultant.Ce type de jointure est appelé jointure interne (INNER JOIN). Comme une jointure interne est un type de jointure silencieuse, le mot-clé INNER peut être omis.

Traduit avec www.DeepL.com/Translator (version gratuite)

Supposons que nous ayons deux tables :

Table A
ID S

87

Just some text

35

Silence

Table B
CODE X

-23

56.7735

87

416.0

Si nous joignons ces tables en utilisant cette requête

SELECT *
FROM A
JOIN B ON A.id = B.code

le résultat sera :

ID S CODE X

87

Just some text

87

416.0

Autrement dit, la première ligne de la table A a été jointe à la deuxième ligne de la table B parce qu’elles satisfont ensemble à la condition de jointure “`A.id = B.code`”. Les autres lignes ne correspondent pas et ne sont donc pas incluses dans la connexion. N’oubliez pas que la connexion par défaut est toujours interne (INNER).

Nous pouvons rendre cela explicite en spécifiant le type de connexion :

SELECT *
FROM A
INNER JOIN B ON A.id = B.code

mais généralement le mot "INNER" est omis.

Bien entendu, il peut y avoir des cas où une ligne de l’ensemble de données de gauche correspond à plusieurs lignes de l’ensemble de données de droite (ou vice versa).

Dans ce cas, toutes les combinaisons sont incluses dans l’ensemble de données résultant, et nous pouvons obtenir un résultat comme celui-ci :

ID S CODE X

87

Just some text

87

416.0

87

Just some text

87

-1.0

-23

Don’t know

-23

56.7735

-23

Still don’t know

-23

56.7735

-23

I give up

-23

56.7735

Il est parfois nécessaire d’inclure tous les enregistrements de l’ensemble de données de gauche ou de droite dans le résultat, qu’il existe ou non un enregistrement correspondant dans l’ensemble de données apparié. Dans ce cas, il est nécessaire d’utiliser des connexions externes.

La connexion externe de gauche (LEFT OUTER) comprend tous les enregistrements de l’ensemble de données de gauche et les enregistrements de l’ensemble de données de droite qui satisfont à la condition de connexion.

La connexion externe de droite (RIGHT OUTER) comprend tous les enregistrements de l’ensemble de données de droite et les enregistrements de l’ensemble de données de gauche qui satisfont à la condition de connexion.

FULL OUTER inclut toutes les entrées des deux ensembles de données.

Dans toutes les connexions externes, les champs vides (c’est-à-dire les champs de l’ensemble de données qui n’ont pas d’enregistrement correspondant) sont remplis avec NULL.

Les mots clés LEFT, RIGHT ou FULL avec le mot clé optionnel OUTER sont utilisés pour désigner la connexion externe.

Considérez diverses connexions externes en utilisant les exemples de requêtes avec les tables A et B ci-dessus :

SELECT *
FROM A
LEFT OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
LEFT JOIN B ON A.id = B.code
ID S CODE X

87

Just some text

87

416.0

235

Silence

<null>

<null>

SELECT *
FROM A
RIGHT OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
RIGHT JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

SELECT *
FROM A
FULL OUTER JOIN B ON A.id = B.code

identique

SELECT *
FROM A
FULL JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

235

Silence

<null>

<null>