COMMENTSInstructions de procédure SQL (PSQL)

Échantillonnage à partir d’une table dérivée (derived table)

Table dérivé — est la commande SELECT correcte, entre parenthèses, éventuellement marquée d’un alias de table et d’alias de champ.

Syntaxe

<derived table> ::=
  (<select-query>)
  [[AS] derived-table-alias]
  [(<derived-column-aliases>)]

<derived-column-aliases> := column-alias [, column-alias ...]

<lateral-derived-table> ::= LATERAL <derived-table>

L’ensemble de données retourné par une telle déclaration est une table virtuelle dans laquelle on peut effectuer des requêtes comme s’il s’agissait d’une table ordinaire.

La table dérivée dans la requête ci-dessous fournit une liste de noms de tables dans la base de données et le nombre de colonnes qu’elles contiennent. La requête de table dérivée fournit le nombre de champs et le nombre de tables avec ce nombre de champs.

SELECT
  FIELDCOUNT,
  COUNT(RELATION) AS NUM_TABLES
FROM (SELECT
        R.RDB$RELATION_NAME RELATION,
        COUNT(*) AS FIELDCOUNT
      FROM RDB$RELATIONS R
        JOIN RDB$RELATION_FIELDS RF
          ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
      GROUP BY RELATION)
GROUP BY FIELDCOUNT

Un exemple trivial démontrant l’utilisation d’un alias de table dérivé et d’une liste d’alias de colonne (tous deux facultatifs) :

SELECT
  DBINFO.DESCR, DBINFO.DEF_CHARSET
FROM (SELECT *
      FROM RDB$DATABASE) DBINFO (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

Note

Notes:

Les tables dérivées peuvent être imbriquées ;
Les tables dérivées peuvent être jointes et utilisées dans les jointures. Ils peuvent contenir des fonctions d’agrégation, des sous-requêtes et des jointures, et peuvent eux-mêmes être utilisés dans des fonctions d’agrégation, des sous-requêtes et des jointures. Il peut également s’agir de procédures stockées ou de requêtes à partir de celles-ci. Ils peuvent comporter des clauses du type "OR", "ORDRE BY" et "GROUPE BY", des instructions du type "FIRST", "SKIPE" , etc ;
Chaque colonne d’une table dérivée doit avoir un nom. Si ce n’est pas le cas par nature (par exemple parce qu’il s’agit d’une constante), vous devez alors aliaser ou ajouter une liste d’alias de colonnes à la spécification de la table dérivée de la manière habituelle ;
La liste des alias de colonnes est facultative, mais si elle est présente, elle doit être complète (c’est-à-dire qu’elle doit contenir un alias pour chaque colonne de la table dérivée) ;
L’optimiseur peut gérer les tables dérivées très efficacement. Toutefois, si la table dérivée est incluse dans une jointure interne et contient une sous-requête, aucun ordre de jointure ne peut être utilisé par l’optimiseur ;
Le mot-clé LATERAL permet à une table dérivée de faire référence à des champs de tables précédemment listées dans la liste de référence courante <table.Voir Linking with LATERAL derived tables pour plus de détails.

Voici un exemple de la manière dont l’utilisation de tableaux dérivés peut simplifier la solution d’un problème.

Supposons que nous ayons un tableau COEFFS contenant les coefficients d’une série d’équations quadratiques que nous allons résoudre. Il peut être défini comme suit :

CREATE TABLE coeffs (
  a DOUBLE PRECISION NOT NULL,
  b DOUBLE PRECISION NOT NULL,
  c DOUBLE PRECISION NOT NULL,
  CONSTRAINT chk_a_not_zero CHECK (a <> 0)
)

En fonction des valeurs des coefficients a, b et c, chaque équation peut avoir zéro, une ou deux solutions. Nous pouvons trouver ces solutions en utilisant une requête à un niveau dans la table COEFFS, mais le code d’une telle requête serait lourd et certaines valeurs (comme les discriminants) seraient calculées plusieurs fois dans chaque ligne.

Si nous utilisons un tableau dérivé, la requête peut être rendue beaucoup plus élégante :

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

Si nous voulons montrer les coefficients à côté des solutions des équations, nous pouvons modifier la requête comme suit :

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

Notez que dans la première requête, nous avons attribué des alias à tous les champs de la table dérivée sous forme de liste après la table, et que dans la seconde, nous ajoutons des alias dans la requête de la table dérivée selon les besoins. Ces deux méthodes sont correctes, car elles garantissent que chaque champ de la table dérivée a un nom unique lorsqu’elles sont appliquées correctement.

Note

En fait, toutes les colonnes calculées dans la table dérivée 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 qui a été dit :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE) 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 :

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT GEN_UUID() AS X
      FROM RDB$DATABASE
      UNION ALL
      SELECT NULL FROM RDB$DATABASE WHERE 1=0) 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

SELECT
    UUID_TO_CHAR(X) AS C1,
    UUID_TO_CHAR(X) AS C2,
    UUID_TO_CHAR(X) AS C3
FROM (SELECT
          (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
      FROM RDB$DATABASE) T;

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

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;

docnext count = 60

É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.

Voir aussi :

Expressions de table communes CTE (WITH … AS … SELECT).

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 :

CODE

Just some text

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>

Des connexions qualifiées

La syntaxe de jointure qualifiée requiert la spécification d’une condition pour la jointure des enregistrements. Cette condition est spécifiée explicitement dans la clause ON ou implicitement avec la clause USING.

Syntaxe

<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

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

<join-condition> ::= ON <condition> | USING (<column-list>)

Connexions avec des conditions explicites

Dans la syntaxe de jointure explicite, il y a une clause ON, avec une condition de jointure, qui peut contenir n’importe quelle expression logique, mais qui contient généralement une condition de comparaison entre les deux sources impliquées.

Très souvent, cette condition est un contrôle d’égalité (ou un ensemble de contrôles d’égalité réunis par l’instruction AND) utilisant l’instruction "=". De telles connexions sont appelées équi-connexions (les exemples du chapitre sur les connexions INNER et OUTER étaient des équi-connexions).

Exemples de connexions avec des conditions explicites :

/*
 * Un échantillon de tous les clients de la ville de Détroit qui ont
 * effectué un achat.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Idem, mais inclut dans l'échantillon les clients qui
 * n'ont pas effectué d'achat.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Pour que chaque femme choisisse un homme qui est plus petit qu'elle.
 * Les hommes qui ne correspondent pas,
 * ne seront pas inclus dans l'échantillon.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Un échantillon de tous les élèves, de leur classe et de leur tuteur.
 * Les élèves sans mentor seront inclus dans l'échantillon.
 * Les élèves sans classe ne seront pas inclus dans l'échantillon.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor

Connexions par colonnes nommées

Les connexions équivalentes comparent souvent des colonnes qui ont le même nom dans les deux tables. Pour ces connexions, nous pouvons utiliser un deuxième type de connexion explicite, appelé Named Columns Joins. Les jointures de colonnes nommées sont réalisées à l’aide de la fonction USING, qui énumère uniquement les noms des colonnes.

Note	Les connexions avec des colonnes nommées ne sont disponibles que dans le dialecte 3.

Ainsi, l’exemple suivant :

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

peut être réécrit comme suit :

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

qui est considérablement plus courte.Le jeu résultant est légèrement différent, du moins lorsqu’on utilise "SELECT *" :

Le jeu de résultats avec la condition de jointure explicite dans la clause ON contiendra chacune des colonnes SEA et `SHIP deux fois : une fois pour la table FLOTSAM et une fois pour la table JETSAM. Il est évident qu’ils les auront et qu’ils auront les mêmes valeurs ;
Le résultat d’une jointure de colonnes nommées, utilisant la clause USING, contiendra ces colonnes une fois.

Si vous voulez obtenir toutes les colonnes dans le résultat de la jointure de colonnes nommée, réécrivez la requête comme suit :

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Pour les connexions externes (OUTER) avec des colonnes nommées, il existe des nuances supplémentaires lors de l’utilisation de “SELECT *” ou d’un nom de colonne incomplet.Si une colonne d’une ligne d’une source ne correspond pas à une colonne d’une ligne d’une autre source, mais que le résultat doit quand même être inclus à cause des instructions LEFT, RIGHT ou FULL, alors la colonne fusionnée aura une valeur non NULL.C’est assez juste, mais maintenant vous ne pouvez pas dire de quel ensemble gauche, droite ou les deux proviennent cette valeur.Ceci est particulièrement trompeur lorsque les valeurs proviennent du côté droit de l’ensemble de données, car “*” affiche toujours pour les colonnes combinées les valeurs du côté gauche de l’ensemble de données, même si la connexion RIGHT est utilisée.

Le fait que ce soit un problème dépend de la situation.Si c’est le cas, utilisez l’approche “f.*, j.*” démontrée ci-dessus, où f et j sont les noms ou alias des deux sources.Ou bien il est préférable d’éviter complètement les * dans les requêtes sérieuses et de lister tous les noms de colonnes pour les ensembles à joindre.Cette approche présente l’avantage supplémentaire de vous obliger à réfléchir aux données que vous souhaitez récupérer et à leur origine.

Il vous incombe de vous assurer que les types de colonnes sont compatibles entre les sources liées, dont les noms sont listés dans l’instruction USING.Si les types sont compatibles mais pas égaux, Firebird les convertira en un type avec une gamme de valeurs plus large avant de les comparer.Ce sera également le type de données de la colonne fusionnée qui apparaîtra dans le jeu résultant si “SELECT *” ou un nom de colonne incomplet est utilisé.Les noms de colonnes complets conserveront toujours leur type de données d’origine.

Tip

Si vous utilisez les colonnes de jonction dans la condition de sélection WHERE lorsque vous liez des colonnes nommées, utilisez toujours les noms de colonnes spécifiés, sinon l’index de cette colonne ne sera pas utilisé.

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

mais

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- ou 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Le fait est que la colonne non spécifiée dans ce cas est implicitement remplacée par COALESCE(a.x, b.x). Cette astuce est utilisée pour éliminer l’ambiguïté dans les noms de colonnes, mais elle empêche également l’application de l’index.

Connexions naturels (`NATURAL JOIN`)

En prenant comme base les connexions de colonnes nommées, l’étape suivante est une connexion naturelle qui effectue une équipartition sur toutes les colonnes de table de droite et de gauche du même nom. Les types de données de ces colonnes doivent être compatibles.

Note	Les connexions naturels ne sont disponibles qu’en dialecte 3.

Syntaxe

<natural-join> ::= NATURAL [<join-type>] JOIN <source>

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

Deux tables sont donnés :

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

La connexion naturelle des tables TA et TB se fera par la colonne a et ins_date et les deux instructions suivants donneront le même résultat :

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Comme toutes les connexions, les connexions naturelles sont des connexions internes par défaut, mais vous pouvez les transformer en connexions externes en spécifiant LEFT, RIGHT ou FULL avant le mot-clé JOIN.

Important

Attention

Si aucune colonne du même nom n’est trouvée dans les deux tables sources, `CROSS JOIN' sera exécuté.

Connexion transversale (`CROSS JOIN`)

Connexion croisée ou produit cartésien.Chaque ligne du tableau de gauche est reliée à chaque ligne du tableau de droite.

Syntaxe

<cross-join> ::=
    <table-reference> [, <table-reference> ...]
  | <table-reference> CROSS JOIN <table-primary>

Notez que la syntaxe utilisant une virgule est obsolète.

La jonction croisée de deux ensembles est équivalente à leur jonction par une condition de tautologie (une condition qui est toujours vraie).

Les deux requêtes suivantes donneront le même résultat :

SELECT *
FROM TA
CROSS JOIN TB;

SELECT *
FROM TA
JOIN TB ON 1 = 1;

Les jointures croisées sont des jointures internes car elles sélectionnent les lignes pour lesquelles il existe une correspondance - il se trouve que toutes les lignes correspondent ! Une jointure croisée externe, si elle existait, n’ajouterait rien au résultat, car les jointures externes ajoutent des enregistrements pour lesquels il n’y a pas de correspondance, et ils n’existent pas dans la jointure croisée.

Les liaisons transversales sont rarement utiles, sauf si vous souhaitez obtenir une liste de toutes les combinaisons possibles de deux variables ou plus.Supposons que vous vendiez un produit disponible en différentes tailles, différentes couleurs et différents matériaux.Si les valeurs de chaque variable sont répertoriées dans leur propre tableau, cette requête renverra toutes les combinaisons :

SELECT
    m.name,
    s.size,
    c.name
FROM materials m
CROSS JOIN sizes s
CROSS JOIN colors c

Connexions implicites

En SQL-89, les tables impliquées dans la jointure sont spécifiées par une liste séparée par des virgules dans la clause FROM. Les conditions de jointure sont spécifiées dans la clause WHERE parmi d’autres conditions de recherche. De telles jointures sont dites implicites.

Seules les connexions internes peuvent être réalisées avec la syntaxe de jointure implicite.

Un exemple de jointure implicite :

/*
 * Un échantillon de tous les clients de la ville de Détroit qui ont
 * ont effectué un achat.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'

Important

L’utilisation de la syntaxe de connexion implicite n’est actuellement pas recommandée.

Mélange de connexion explicite et implicite

Le mélange de connexions explicites et implicites n’est pas recommandé, mais est autorisé. Certains types de mélange ne sont pas autorisés dans Firebird.

Par exemple, une requête comme celle-ci provoquera une erreur "Column does not belong to referenced table"

SELECT *
FROM
TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Cela est dû au fait que le JOIN explicite ne peut pas voir la table TA. Cependant, la requête suivante s’exécutera sans erreur car l’isolation n’est pas rompue.

SELECT *
FROM
TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

Noms de champs ambigus dans les sélections

Firebird rejette les noms de champs incomplets dans une requête si ces noms de champs existent dans plus d’un jeu de données impliqué dans l’union. Ceci est également vrai pour les équipoins internes où les noms de champs apparaissent dans la clause ON :

SELECT a, b, c
FROM TA
JOIN TB ON TA.a = TB.a

Il existe une exception à cette règle : les jointures de colonnes nommées et les jointures naturelles, qui utilisent un nom de champ incomplet dans le processus de correspondance, peuvent être utilisées légalement. Il en va de même pour les colonnes joignables homonymes. Pour les jointures de colonnes nommées, ces colonnes doivent être listées dans la clause USING. Pour les jointures naturelles, il s’agit de colonnes dont les noms sont présents dans les deux tables.Mais notez encore une fois que, surtout dans les jointures externes, le nom plat colname n’est pas toujours le même que left.colname ou right.colname. Les types de données peuvent être différents, et l’une des colonnes complètes peut avoir la valeur NULL, alors que l’autre ne l’a pas. Dans ce cas, la valeur de la colonne fusionnée et incomplète peut masquer le fait qu’une des valeurs originales est manquante.

Connexions aux procédures stockées

Si la connexion se fait à une procédure stockée qui n’est pas corrélée à d’autres flux de données via des paramètres d’entrée, il n’y a pas de particularités.

Sinon, il y a une particularité : les threads utilisés dans les paramètres d’entrée doivent être décrits avant la connexion à la procédure stockée :

SELECT *
FROM MY_TAB
JOIN MY_PROC(MY_TAB.F) ON 1 = 1

Une requête écrite comme suit provoquera une erreur

SELECT *
FROM MY_PROC(MY_TAB.F)
JOIN MY_TAB ON 1 = 1

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

CHILD

MARBLES

Anita

Bob E.

Chris

<null>

Deirdre

Eve

Fritz

Gerry

Hadassah

<null>

Isaac

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 :

Copiez l’expression textuelle de la liste de sélection, par exemple “class” ou “'D:' || upper(doccode)” ;
Spécifiez un alias s’il en existe un ;
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
…	…	…	…	…

CLASS

SEX

BOARDING_TYPE

ANUMBER

AVG_AGE

BOARDING

13.3

DAY

13.5

BOARDING

13.6

DAY

13.4

BOARDING

13.7

DAY

13.7

BOARDING

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.

HAVING

Comme la clause WHERE, la clause HAVING restreint les lignes du jeu de données à celles qui satisfont la condition de recherche, à la différence que la clause HAVING impose des restrictions sur les lignes agrégées du jeu groupé.La clause HAVING est facultative et ne peut être utilisée qu’en conjonction avec la clause GROUP BY.

La (les) condition(s) dans la clause 'HAVING' peuvent se référer à :

Toute colonne agrégée dans la liste de sélection SELECT. C’est le cas le plus couramment utilisé ;
Toute expression agrégée qui ne figure pas dans la liste de sélection SELECT, mais qui est autorisée dans le contexte de la requête. C’est parfois utile ;
Toute colonne de la liste GROUP BY. Cependant, il est plus efficace de filtrer les données non agrégées plus tôt dans la clause WHERE ;
Toute expression dont la valeur est indépendante du contenu de l’ensemble de données (telle qu’une constante ou une variable contextuelle). Cela est acceptable, mais n’a aucun sens, car une telle clause, qui n’a rien à voir avec l’ensemble de données lui-même, va soit supprimer l’ensemble entier, soit le laisser intact.

Une clause HAVING ne peut pas contenir :

Expressions de colonnes non agrégées qui ne sont pas dans la liste GROUP BY ;
Position de la colonne. Un nombre entier dans une clause HAVING est simplement un nombre entier ;
Les alias de colonnes — même s’ils apparaissent dans une clause GROUP BY.

Exemples

Reconstruire nos premiers exemples. Nous pouvons utiliser la clause "HAVING" pour exclure de petits groupes d’élèves :

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

Ne choisissez que les groupes dont l’écart d’âge minimum est de 1,2 an :

SELECT
    class,
    COUNT(*) AS num_boys,
    AVG(age) AS boys_avg_age
FROM students
WHERE sex = 'M'
GROUP BY class
HAVING MAX(age) - MIN(age) > 1.2

Notez que si vous êtes vraiment intéressé par cette information, c’est une bonne idée d’inclure min(age) et max(age) ou l’expression max(age) - min(age) dans la liste de sélection.

La requête suivante ne sélectionne que les élèves de 3ème année :

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

Cependant, il est bien mieux de déplacer cette condition dans la clause WHERE:

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

`WINDOW`

La clause WINDOW est destinée à spécifier des fenêtres nommées qui sont utilisées par window functions. Comme l’expression window peut être assez complexe, et utilisée de nombreuses fois, cette fonctionnalité peut être utile.

Syntaxe

<query spec> ::=
  SELECT
    [<first clause>] [<skip clause>]
    [<distinct clause>]
    <select list>
    <from clause>
    [<where clause>]
    [<group clause>]
    [<having clause>]
    [<named windows clause>]
    [<order clause>]
    [<rows clause>]
    [<offset clause>] [<limit clause>]
    [<plan clause>]

<named windows clause> ::=
  WINDOW <window definition> [, <window definition>] ...

<window definition> ::=
  window-name AS <window specification>

<window specification> ::=
   ([window-name] [<window partition>] [<window order>] [<window frame>])


<window partition> ::= PARTITION BY <expr> [, <expr> ...]

<window order> ::=
  ORDER BY <expr> [<direction>] [<nulls placement>]
        [, <expr> [<direction>] [<nulls placement>] ...]

<direction> ::= {ASC | DESC}

<nulls placement> ::= NULLS {FIRST | LAST}

<window frame> ::=
  {ROWS | RANGE} <window frame extent>

<window frame extent> ::=
  <window frame preceding> | <window frame between>

<window frame preceding> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window frame between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }
      AND { UNBOUNDED FOLLOWING | <expr> PRECEDING | <expr> FOLLOWING | CURRENT ROW }

Le nom d’une window peut être utilisé dans une clause OVER pour faire référence à une définition de window, et il peut également être utilisé comme window de base pour une autre window nommée ou intégrée (dans une clause OVER). Les window encadrées (avec les clause RANGE et ROWS) ne peuvent pas être utilisées comme window de base (mais peuvent être utilisées dans une clause OVER _nom_de_la_window). Une window qui utilise une référence à une window de base ne peut pas avoir de clause PARTITION BY et ne peut pas remplacer le tri par une clause ORDER BY.

Exemples

Example 1. Utilisation de window nommées

SELECT
    id,
    department,
    salary,
    count(*) OVER w1,
    first_value(salary) OVER w2,
    last_value(salary) OVER w2,
    sum(salary) over (w2 ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS s
FROM employee
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY salary)
ORDER BY department, salary;

Voir aussi :

Fonctions de window (analytique).

`PLAN`

La proposition PLAN permet à l’utilisateur de spécifier son propre plan d’exécution de la requête, en remplaçant le plan que l’optimiseur a généré automatiquement.

Syntaxe

PLAN <plan-expr>

<plan-expr> ::=
    (<plan-item> [, <plan-item> ...])
  | <sorted-item>
  | <joined-item>
  | <merged-item>
  | <hash-item>

<sorted-item> ::= SORT (<plan-item>)

<joined-item> ::= JOIN (<plan-item>, <plan-item> [, <plan-item> ...])

<merged-item> ::=
  [SORT] MERGE (<sorted-item>, <sorted-item> [, <sorted-item> ...])

<hash-item> ::= HASH (<plan-item>, <plan-item> [, <plan-item> ...])

<plan-item> ::= <basic-item> | <plan-expr>

<basic-item> ::= <relation> {
    NATURAL
  | INDEX (<indexlist>)
  | ORDER index [INDEX (<indexlist>)]
}

<relation> ::= table | view [table]

<indexlist> ::= index [, index ...]

Table 1. Paramètres de la clause PLAN
Paramètre	Description
table	Le nom de la table ou son alias.
view	Le nom de la vue.
index	Nom de l’index.

Chaque fois qu’un utilisateur envoie une requête au noyau Firebird, l’optimiseur calcule une stratégie d’extraction de données. La plupart des clients Firebird ont la possibilité d’afficher un plan d’extraction de données à l’utilisateur. Dans l’outil natif isql, ceci est fait avec la commande SET PLAN ON.Si vous souhaitez uniquement examiner le plan de requête sans l’exécuter, vous devez entrer la commande SET PLANONLY ON, qui récupérera les plans de requête sans les exécuter. Pour ramener isql en mode d’exécution de requête, entrez la commande SET PLANONLY OFF.

Note

Un plan plus détaillé peut être obtenu en activant le plan avancé. Dans isql, cela se fait avec la commande SET EXPLAIN ON. Ce plan fournit des informations plus détaillées sur les méthodes d’accès utilisées par l’optimiseur, mais il ne peut pas être inclus dans une requête. Une description du plan avancé sort du cadre de ce manuel.

Dans la plupart des cas, vous pouvez faire confiance à Firebird pour choisir le plan de requête le plus optimal. Cependant, si vos requêtes sont très complexes et que vous avez l’impression qu’elles ne s’exécutent pas efficacement, vous devez examiner le plan de requête et voir si vous pouvez l’améliorer.

Des plans simples

Les plans les plus simples se composent uniquement d’un nom de table et de la méthode d’extraction suivante. Par exemple, pour une sélection non triée à partir d’une seule table sans clause WHERE:

SELECT * FROM students
PLAN (students NATURAL)

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Full Scan

S’il y a une clause `WHERE', vous pouvez spécifier l’index à utiliser pour trouver des correspondances :

SELECT *
FROM students
WHERE class = '3C'
PLAN (students INDEX (ix_stud_class))

Plan sous forme EXPLAIN:

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Bitmap
              -> Index "IX_STUD_CLASS" Range Scan (full match)

La directive INDEX peut également être utilisée pour les conditions de connexion (qui seront discutées plus tard). Il contient une liste d’index séparés par des virgules.

La directive ORDER spécifie l’index qui est utilisé pour trier le jeu de données si les clause ORDER BY ou GROUP BY sont présentes :

SELECT *
FROM students
PLAN (students ORDER pk_students)
ORDER BY id

Plan sous forme EXPLAIN:

Select Expression
  -> Table "STUDENTS" Access By ID
      -> Index "PK_STUDENTS" Full Scan

Les instructions ORDER et INDEX peuvent être combinées :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER pk_students INDEX (ix_stud_class))
ORDER BY id

Plan sous forme d’EXPLAIN :Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "PK_STUDENTS" Full Scan
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Les instructions ORDER et INDEX sont autorisées à spécifier le même index :

SELECT *
FROM students
WHERE class >= '3'
PLAN (students ORDER ix_stud_class INDEX (ix_stud_class))
ORDER BY class

Plan sous forme d’EXPLAIN :

Select Expression
  -> Filter
      -> Table "STUDENTS" Access By ID
          -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Pour trier des ensembles de données lorsqu’un index ne peut pas être utilisé (ou que vous souhaitez supprimer son utilisation), supprimez l’instruction ORDER et faites précéder l’expression du plan de l’instruction SORT:

SELECT *
FROM students
PLAN SORT (students NATURAL)
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 128, key length: 56)
      -> Table "STUDENTS" Full Scan

Ou lorsque l’index est utilisé pour la recherche :

SELECT *
FROM students
WHERE class >= '3'
PLAN SORT (students INDEX (ix_stud_class))
ORDER BY name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 136, key length: 56)
      -> Filter
          -> Table "STUDENTS" Access By ID
              -> Bitmap
                  -> Index "IX_STUD_CLASS" Range Scan (lower bound: 1/1)

Notez que l’instruction SORT, contrairement à ORDER, est en dehors des parenthèses, ce qui reflète le fait que les lignes de données sont récupérées sans être triées et triées plus tard.

Lors d’une extraction à partir d’une vue, la vue elle-même et la table participante sont spécifiées. Par exemple, si vous avez une vue FRESHMEN qui ne sélectionne que les étudiants de première année :

SELECT *
FROM freshmen
PLAN (freshmen students NATURAL)

Plan sous forme d’EXPLAIN:

Select Expression
  -> Table "STUDENTS" as "FRESHMEN" Full Scan

Ou, par exemple.:

SELECT *
FROM freshmen
WHERE id > 10
PLAN SORT (freshmen students INDEX (pk_students))
ORDER BY name DESC

Plan sous forme d’EXPLAIN:

Select Expression
  -> Sort (record length: 144, key length: 24)
      -> Filter
          -> Table "STUDENTS" as "FRESHMEN" Access By ID
              -> Bitmap
                  -> Index "PK_STUDENTS" Range Scan (lower bound: 1/1)

Remarque : si vous avez attribué un alias à une table ou à une vue, vous devez utiliser l’alias, et non le nom original, dans la clause PLAN.

Plans composites

Si vous établissez une connexion, vous pouvez spécifier l’index à utiliser pour le mappage. Vous devez également utiliser la directive JOIN pour les deux threads du plan :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s NATURAL, c INDEX (pk_classes))

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Même connexion, triée par colonne indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN JOIN (s ORDER pk_students, c INDEX (pk_classes))
ORDER BY s.id

Plan sous forme d’EXPLAIN :

Select Expression
  ->  Nested Loop Join (inner)
      -> Table "STUDENTS" as "S" Access By ID
          -> Index "PK_STUDENTS" Full Scan
      -> Filter
          -> Table "CLASSES" as "C" Access By ID
              -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

Et une connexion triée par une colonne non indexée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
PLAN SORT (JOIN (S NATURAL, c INDEX (pk_classes))))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
      ->  Nested Loop Join (inner)
          -> Table "STUDENTS" as "S" Full Scan
          -> Filter
              -> Table "CLASSES" as "C" Access By ID
                  -> Bitmap
                      -> Index "PK_CLASSES" Unique Scan

Connexion avec une condition de recherche ajoutée :

SELECT s.id, s.name, s.class, c.mentor
FROM students s
JOIN classes c ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (s INDEX (fk_student_class), c INDEX (pk_classes)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 152, key length: 12)
     ->  Nested Loop Join (inner)
         -> Filter
            -> Table "STUDENTS" as "S" Access By ID
               -> Bitmap
                  -> Index "FK_STUDENT_CLASS" Range Scan (lower bound: 1/1)
         -> Filter
            -> Table "CLASSES" as "C" Access By ID
               -> Bitmap
                  -> Index "PK_CLASSES" Unique Scan

La même chose, mais en utilisant la connexion externe de gauche :

SELECT s.id, s.name, s.class, c.mentor
FROM classes c
LEFT JOIN students s ON c.name = s.class
WHERE s.class <= '2'
PLAN SORT (JOIN (c NATURAL, s INDEX (fk_student_class)))
ORDER BY s.name

Plan sous forme d’EXPLAIN :

Select Expression
  -> Sort (record length: 192, key length: 56)
      -> Filter
         ->  Nested Loop Join (outer)
             -> Table "CLASSES" as "C" Full Scan
             -> Filter
                -> Table "STUDENTS" as "S" Access By ID
                   -> Bitmap
                      -> Index "FK_STUDENT_CLASS" Range Scan (full match)

Si aucun index n’est disponible pour la condition de jointure (ou si vous ne voulez pas en utiliser un), il est possible de joindre des fils en utilisant la méthode HASH ou MERGE.

Pour une jointure HASH, le plan utilise la directive HASH au lieu de la directive JOIN. Dans ce cas, le plus petit thread (esclave) est lu complètement dans le tampon interne. Pendant le processus de lecture, une fonction de hachage est appliquée à chaque clé de lien et la paire {hash, pointeur dans le tampon} est écrite dans la table de hachage. Le thread maître est ensuite lu et sa clé de lien est validée dans la table de hachage.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN HASH (c NATURAL, s NATURAL)

Plan sous forme d’EXPLAIN :

Select Expression
    -> Filter
        -> Hash Join (inner)
            -> Table "STUDENTS" as "S" Full Scan
            -> Record Buffer (record length: 145)
                -> Table "CLASSES" as "C" Full Scan

Lors d’une jointure MERGE, le plan doit d’abord trier les deux threads par les colonnes à joindre, puis effectuer la fusion. Ceci est réalisé avec la directive SORT (que vous avez déjà vue) et MERGE utilisée à la place de JOIN.

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN MERGE (SORT (c NATURAL), SORT (s NATURAL))

L’ajout de la clause "ORDER BY" signifie que le résultat de la fusion doit également être trié :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
PLAN SORT (MERGE (SORT (c NATURAL), SORT (s NATURAL)))
ORDER BY c.name, s.id

Enfin, nous ajoutons une condition de recherche sur les deux colonnes indexées de la table STUDENTS :

SELECT *
FROM students s
JOIN classes c ON c.cookie = s.cookie
WHERE s.id < 10 AND s.class <= '2'
PLAN SORT (MERGE (SORT (c NATURAL),
                  SORT (s INDEX (pk_students, fk_student_class))))
ORDER BY c.name, s.id

Comme le suggère la définition formelle de la syntaxe, JOIN et MERGE peuvent combiner plus de deux threads dans un plan. De plus, chaque expression de plan peut être utilisée comme élément d’un plan englobant. Cela signifie que les plans de certaines requêtes complexes peuvent avoir différents niveaux d’imbrication.

Enfin, vous pouvez écrire SORT MERGE au lieu de MERGE. Comme cela n’a absolument aucune signification et peut créer une confusion avec la directive SORT (qui a une signification), il est probablement préférable de s’en tenir à la simple directive MERGE.

En plus du plan pour la requête principale, vous pouvez spécifier un plan pour chaque sous-requête. Par exemple, la requête suivante avec les plans spécifiés serait absolument correcte.

SELECT *
FROM COLOR
WHERE EXISTS(
             SELECT *
             FROM HORSE
             WHERE HORSE.CODE_COLOR = COLOR.CODE_COLOR
             PLAN (HORSE INDEX (FK_HORSE_COLOR)))
PLAN(COLOR NATURAL)

`UNION`

La clause UNION joint deux ou plusieurs ensembles de données, augmentant ainsi le nombre total de lignes mais pas de colonnes. Les ensembles de données participant à l’union doivent avoir le même nombre de colonnes. Cependant, les colonnes dans les positions respectives ne doivent pas nécessairement être du même type de données, elles peuvent être complètement indépendantes.

Par défaut, la fusion supprime les lignes en double.UNION ALL affiche toutes les chaînes de caractères, y compris les doublons. Le mot-clé optionnel DISTINCT rend le comportement par défaut explicite.

Syntaxe

<query-expression> ::=
  [<with-clause>]
  <query-expression-body>
  [<order-by-clause>]
  [<rows-clause> | {[<result-offset-clause>] [<fetch-first-clause>]}]

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body>
      [<order-by-clause>]
      [<result-offset-clause>] [<fetch-first-clause>]
    )

<query-specification> ::=
  SELECT
    <limit-clause>
    [{ ALL | DISTINCT }] <select-list>
  FROM <table-reference> [, <table-reference> ...]
  [WHERE <search-condition>]
  [GROUP BY <value-expression> [, <value-expression> ...]]
  [HAVING <search-condition>]
  [WINDOW <window-definition> [, <window-definition> ...]]
  [PLAN <plan-expression>]

Les jointures récupèrent les noms de colonnes de la première requête de sélection.Si vous voulez aliaser les colonnes à fusionner, faites-le pour la liste des colonnes dans la requête la plus haute de la sélection.Les alias dans d’autres échantillons participants sont autorisés, et peuvent même être utiles, mais ils ne se propageront pas au niveau de la fusion.

Si l’union a une clause ORDER BY, les seuls éléments de tri possibles sont des littéraux entiers pointant sur les positions des colonnes, pas nécessairement accompagnés des directives ASC | DESC et/ou NULLS {FIRST | LAST}.Cela signifie également que vous ne pouvez pas ordonner l’union par un élément qui n’est pas une colonne d’union.(Vous pouvez toutefois l’envelopper dans une table dérivée, qui vous donnera tous les paramètres de tri habituels).

Les fusions sont autorisées dans les sous-requêtes de tout type, et peuvent contenir des sous-requêtes à part entière.Ils peuvent également contenir des jointures, et peuvent prendre part à des jointures s’ils sont enveloppés dans une table dérivée.

Exemples

Cette requête présente des informations provenant de différentes collections de musique dans un seul ensemble de données en utilisant des associations :

SELECT id, title, artist, len, 'CD' AS medium
FROM cds
UNION
SELECT id, title, artist, len, 'LP'
FROM records
UNION
SELECT id, title, artist, len, 'MC'
FROM cassettes
ORDER BY 3, 2 -- artist, title

Si id, title, artist et length sont les seuls champs de toutes les tables participantes, la requête peut être écrite comme suit

SELECT c.*, 'CD' AS medium
FROM cds c
UNION
SELECT r.*, 'LP'
FROM records r
UNION
SELECT c.*, 'MC'
FROM cassettes c
ORDER BY 3, 2 -- artist, title

La clarification des " * " ( astérisque ) est nécessaire ici car elles ne sont pas le seul élément de la liste des colonnes. Notez que les alias "c" dans le premier et le troisième échantillon ne se mordent pas l’un l’autre. Ils n’ont pas de contexte d’association, mais s’appliquent uniquement aux requêtes individuelles de l’échantillon.

La requête suivante permet d’obtenir les noms et les numéros de téléphone des traducteurs et des correcteurs. Les traducteurs qui travaillent également comme correcteurs ne seront affichés qu’une seule fois dans le jeu résultant si leurs numéros de téléphone sont les mêmes dans les deux tables. Le même résultat peut être obtenu sans le mot-clé DISTINCT. Si le mot-clé ALL est spécifié au lieu de DISTINCT, ces personnes seront affichées deux fois.

SELECT name, phone
FROM translators
UNION DISTINCT
SELECT name, telephone
FROM proofreaders

Un exemple de l’utilisation de UNION dans une sous-requête :

SELECT name, phone, hourly_rate
FROM clowns
WHERE hourly_rate < ALL
  (SELECT hourly_rate FROM jugglers
   UNION
   SELECT hourly_rate FROM acrobats)
ORDER BY hourly_rate

L’utilisation d’expressions de requête entre parenthèses permet d’afficher les salariés les mieux payés et les moins bien payés :

(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

Syntaxe complète `SELECT`

Les sections précédentes ont utilisé des fragments incomplets ou simplifiés de la syntaxe de l’opérateur SELECT. La syntaxe complète est donnée ci-dessous.

Note

Dans la mesure du possible, la syntaxe ci-dessous utilise les noms de syntaxe de la norme SQL, qui ne sont pas nécessairement les mêmes que les noms de syntaxe dans le code source de Firebird. Dans certains cas, les représentations syntaxiques ont été regroupées parce que les représentations de la norme SQL sont détaillées et parce qu’elles sont également utilisées pour ajouter des règles ou des définitions supplémentaires à un élément syntaxique.

Bien que la syntaxe complète soit décrite ici, certaines représentations ne sont pas affichées (par exemple, <expression de valeur>) et sont supposées être claires pour le lecteur, et dans certains cas, nous utilisons des abréviations, telles que query-name ou column-alias pour les identificateurs dans la représentation syntaxique.

La syntaxe suivante n’inclut pas la syntaxe PSQL SELECT…. INTO, qui est essentiellement <spécification du curseur> INTO <liste de variables>.

Syntaxe complète de l’opérateur SELECT

<cursor-specification> ::=
  <query-expression> [<updatability-clause>] [<lock-clause>]

<query-expression> ::=
  [<with-clause>] <query-expression-body> [<order-by-clause>]
    [{ <rows-clause>
     | [<result-offset-clause>] [<fetch-first-clause>] }]

<with-clause> ::=
  WITH [RECURSIVE] <with-list-element> [, <with-list-element> ...]

<with-list-element> ::=
  query-name [(<column-name-list>)] AS (<query-expression>)

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

<query-expression-body> ::=
    <query-term>
  | <query-expression-body> UNION [{ DISTINCT | ALL }] <query-term>

<query-term> ::= <query-primary>

<query-primary> ::=
    <query-specification>
  | (<query-expression-body> [<order-by-clause>]
     [<result-offset-clause>] [<fetch-first-clause>])

<query-specification> ::=
  SELECT <limit-clause> [{ ALL | DISTINCT }] <select-list>
    FROM <table-reference> [, <table-reference> ...]
    [WHERE <search-condition>]
    [GROUP BY <value-expression> [, <value-expression> ...]]
    [HAVING <search-condition>]
    [WINDOW <window-definition> [, <window-definition> ...]]
    [PLAN <plan-expression>]

<limit-clause> ::= [FIRST <limit-expression>] [SKIP <limit-expression>]

<limit-expression> ::=
    <integer-literal>
  | <query-parameter>
  | (<value-expression>)

<select-list> ::= * | <select-sublist> [, <select-sublist> ...]

<select-sublist> ::=
    table-alias.*
  | <value-expression> [[AS] column-alias]

<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> ...]

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

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

<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>) }

<window-definition> ::=
  new-window-name AS (<window-specification-details>)

<window-specification-details> ::=
  [existing-window-name]
    [<window-partition-clause>]
    [<order-by-clause>]
    [<window-frame-clause>]

<window-partition-clause> ::=
  PARTITION BY <value-expression> [, <value-expression> ...]

<order-by-clause> ::=
  ORDER BY <sort-specification> [, <sort-specification> ...]

<sort-specification> ::=
  <value-expression> [<ordering-specification>] [<null-ordering>]

<ordering-specification> ::=
    ASC  | ASCENDING
  | DESC | DESCENDING

<null-ordering> ::=
    NULLS FIRST
  | NULLS LAST

<window-frame-clause> ::= { RANGE | ROWS } <window-frame-extent>

<window-frame-extent> ::=
    <window-frame-start>
  | <window-frame-between>

<window-frame-start> ::=
    UNBOUNDED PRECEDING
  | <value-expression> PRECEDING
  | CURRENT ROW

<window-frame-between> ::=
  BETWEEN { UNBOUNDED PRECEDING | <value-expression> PRECEDING
          | CURRENT ROW | <value-expression> FOLLOWING }
  AND { <value-expression> PRECEDING | CURRENT ROW
      | <value-expression> FOLLOWING | UNBOUNDED FOLLOWING }

<rows-clause> ::= ROWS <value-expression> [TO <value-expression>]

<result-offset-clause> :: =
  OFFSET <offset-fetch-expression> { ROW | ROWS }

<offset-fetch-expression> ::=
    <integer-literal>
  | <query-parameter>

<fetch-first-clause> ::=
  [FETCH { FIRST | NEXT }
   [<offset-fetch-expression>] { ROW | ROWS } ONLY]

<updatability-clause> ::= FOR UPDATE [OF <column-name-list>]

<lock-clause> ::= WITH LOCK [SKIP LOCKED]

`INSERT`

affectation

Insérer des données dans un tableau.

Disponible en

DSQL, ESQL, PSQL

Syntaxe

INSERT INTO target [(<column_list>)]
  [OVERRIDE {SYSTEM | USER} VALUE]
  {DEFAULT VALUES | <value_source>}
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col-name [, col-name ...]

<value_source> ::= VALUES (<value_list>) | <select_stmt>

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> :: = <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Paramètres de la clause `INSERT`
Paramètre	Description
target	Nom de la table ou de la vue dans laquelle la ou les nouvelles entrées sont insérées.
col-name	Le nom de la colonne de la table ou de la vue.
value_expression	Une expression dont la valeur est utilisée pour insérer dans une table ou retourner à `RETURNING`
literal	Litéral
context-variable	La variable de contexte.
other-single-value-expr	Toute autre expression qui renvoie une seule valeur du type de données Firebird ou `NULL`.
return_expression	Expression renvoyée dans une clause `RETURNING`.
collation	Nom de tri existant (pour les types de caractères uniquement).
alias	Alias pour l’expression retournée dans la clause `RETURNING`.
varname	Le nom de la variable PSQL.

L’instruction INSERT ajoute des lignes à la table ou à une ou plusieurs tables de présentation. Si les valeurs des colonnes sont spécifiées sous VALUES, une ligne sera insérée. Les valeurs des colonnes peuvent également être obtenues à partir de l’instruction SELECT, auquel cas zéro ou plusieurs lignes peuvent être insérées. Dans le cas de DEFAULT VALUES, les valeurs peuvent ne pas être spécifiées et une ligne sera insérée.

Note

Une colonne ne peut pas être spécifiée plus d’une fois dans la liste des colonnes ;
Lorsque l’on retourne les valeurs des colonnes d’un enregistrement inséré en utilisant INTO vers les variables de contexte NEW.columnname dans les triggersle préfixe deux points (“:”) n’a pas besoin d’être utilisé.

`INSERT … VALUES`

La liste VALUES doit spécifier des valeurs pour toutes les colonnes de la liste de colonnes dans le même ordre et compatibles en type. S’il n’y a pas de liste de colonnes, les valeurs doivent être spécifiées pour chaque colonne de la table ou de la vue (à l’exclusion des colonnes calculées).

Note

Introducer syntax permet de définir un jeu de caractères pour les valeurs constantes des chaînes de caractères (littéraux).La syntaxe d’entrée ne fonctionne que sur les littéraux de chaîne : elle ne peut pas être appliquée aux variables de chaîne, aux paramètres, aux références de colonnes ou de valeurs, ou aux expressions.

Example 1. Utiliser INSERT avec une clause VALUES.

INSERT INTO cars (make, model, byyear)
VALUES ('Ford', 'T', 1908);

INSERT INTO cars
VALUES ('Ford', 'T', 1908, 'USA', 850);

-- notez le préfixe '_' (introducer syntax)
INSERT INTO People
VALUES (_ISO8859_1 'Hans-Jörg Schäfer');

Mot clé `DEFAULT`

Dans la liste VALUES, le mot clé DEFAULT peut être utilisé à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci obtiendra la valeur NULL.

Si le mot-clé DEFAULT est spécifié pour une colonne définie comme GENERATED BY DEFAULT AS IDENTITY, la colonne obtiendra la valeur d’identification suivante, comme si cette colonne n’était pas du tout spécifiée dans la requête.

Example 1. Utilisation du mot clé DEFAULT dans l’instruction INSERT

CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- la colonne BYYEAR contiendra la valeur 1990
INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus');

-- la colonne id aura la valeur 2, comme si nous n'avions pas spécifié de valeur pour la colonne id.
INSERT INTO cars (id, byyear, name)
VALUES (DEFAULT, 1996, 'Ford Mondeo');

`INSERT … SELECT`

Dans ce cas, les colonnes de sortie de l’instruction SELECT doivent fournir des valeurs pour chaque colonne cible de la liste de colonnes, dans le même ordre et d’un type compatible. S’il n’y a pas de liste de colonnes, des valeurs doivent être fournies pour chaque colonne de la table ou de la vue (à l’exclusion des colonnes calculées).

Example 1. En utilisant l’instruction sous la forme INSERT … SELECT

INSERT INTO cars (make, model, byyear)
SELECT make, model, byyear
FROM new_cars;

INSERT INTO cars
SELECT *
FROM new_cars;

INSERT INTO Members (number, name)
SELECT number, name
FROM NewMembers
WHERE Accepted = 1
UNION ALL
SELECT number, name
FROM SuspendedMembers
WHERE Vindicated = 1

INSERT INTO numbers(num)
WITH RECURSIVE r(n) AS (
  SELECT 1 FROM rdb$database
  UNION ALL
  SELECT n+1 FROM r where n < 100
)
SELECT n FROM r

Bien entendu, les noms des colonnes de la table source ne doivent pas nécessairement être les mêmes que ceux de la table de destination.

Tout type d’instruction SELECT est autorisé, à condition que ses colonnes de sortie correspondent exactement aux colonnes d’insertion en nombre et en type. Il n’est pas nécessaire que les types soient exactement les mêmes, mais ils doivent être compatibles avec les affectations.

`INSERT … DEFAULT VALUES`

La clause DEFAULT VALUES vous permet d’insérer des enregistrements sans spécifier de valeurs du tout, soit directement (dans la clause VALUES), soit à partir de l’instruction SELECT. Cela n’est possible que si pour chaque champ NOT NULL et les champs auxquels d’autres restrictions sont appliquées, soit il existe des valeurs par défaut déclarées valides, soit ces valeurs sont définies dans le déclencheur BEFORE INSERT.

Example 1. Utilisation de "valeurs par défaut" dans l’instruction "INSERT".

INSERT INTO journal
DEFAULT VALUES
RETURNING entry_id

Directive `OVERRIDING`

Les valeurs des colonnes d’identité (GENERATED BY DEFAULT AS IDENTITY) peuvent être remplacées par les instructions INSERT, UPDATE OR INSERT, MERGE. Pour cela, il suffit de spécifier la valeur de la colonne dans la liste des valeurs. Cependant, pour les colonnes définies comme GENERATED ALWAYS, cela n’est pas autorisé.

La directive OVERRIDING SYSTEM VALUE permet de remplacer une valeur générée par le système par une valeur spécifiée par l’utilisateur. La directive OVERRIDING SYSTEM VALUE provoquera une erreur s’il n’y a pas de colonnes d’identité dans la table ou si elles sont définies comme GENERATED BY DEFAULT AS IDENTITY.

Example 1. Utilisation de la directive OVERRIDING SYSTEM VALUE dans l’instruction INSERT.

CREATE TABLE objects (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

-- la valeur avec le code 11 sera insérée
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (11, 'Laptop');

La directive OVERRIDE USER VALUE effectue la tâche inverse, c’est-à-dire qu’elle remplace la valeur spécifiée par l’utilisateur par la valeur générée par le système si la colonne d’identité est définie comme GENERATED BY DEFAULT AS IDENTITY. La directive OVERRIDING USER VALUE provoquera une erreur si la table ne contient pas de colonne d’identité ou si elle est définie comme GENERATED ALWAYS AS IDENTITY.

Example 2. Utilisation de la directive OVERRIDING USER VALUE dans l’instruction INSERT.

CREATE TABLE objects (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- la valeur 12 sera ignorée
INSERT INTO objects (id, name)
OVERRIDING SYSTEM VALUE
VALUES (12, 'Laptop');

Voir aussi :

Colonnes d’identitée (auto-incrémentation).

`RETURNING`

L’instruction INSERT peut inclure une clause RETURNING facultative pour retourner les valeurs de la ligne insérée. Si la clause est spécifiée, elle peut contenir toutes les colonnes spécifiées dans l’instruction ou d’autres colonnes et expressions. Vous pouvez spécifier un astérisque (*) au lieu d’une liste de colonnes pour retourner toutes les valeurs des colonnes de la table. Les valeurs retournées contiennent toutes les modifications apportées dans les déclencheurs BEFORE.

Important

En DML, le INSERT … SELECT avec RETURNING retourne un curseur (avant Firebird 5.0 ne pouvait retourner qu’un seul enregistrement).
Actuellement, les instructions avec une clause RETURNING ne peuvent pas être utilisées en conjonction avec une clause FOR pour boucler sur un curseur dans PSQL.

Ce comportement peut être modifié dans les versions futures de Firebird.* La fonction INSERT … VALUES avec une clause RETURNING retourne toujours un seul enregistrement. Si aucun enregistrement n’a été inséré, tous les champs de la chaîne retournée seront mis à NULL. Ce comportement peut être modifié ultérieurement. En PSQL, si aucun enregistrement n’est inséré, rien n’est retourné et toutes les variables cibles conservent leurs valeurs précédentes.

Example 1. Utilisation de la clause RETURNING dans l’instruction INSERT.

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING lastname, fullname, id;

INSERT INTO Scholars (firstname, lastname, address,
  phone, email)
VALUES (
  'Henry', 'Higgins', '27A Wimpole Street',
  '3231212', NULL)
RETURNING *;

INSERT INTO Dumbbells (first_name, last_name, iq)
SELECT fname, lname, iq
FROM Friends
ORDER BY iq ROWS 1
RETURNING id, first_name, iq
INTO :id, :fname, :iq;

Insertion de colonnes BLOB

L’insertion dans les colonnes BLOB n’est possible que dans les circonstances suivantes :

L’application cliente insère un BLOB via l’API de Firebird. Dans ce cas, cela dépend de l’application, et n’est pas couvert par ce manuel ; . La longueur des chaînes de caractères ne peut dépasser 65 533 octets (64K - 3).

Note	La limite de caractères est calculée au moment de l’exécution. Pour les jeux de caractères à plusieurs octets, cela peut être différent. Par exemple, pour une chaîne UTF8 (4 octets par caractère), la limite du littéral de chaîne serait probablement d’environ (floor (65533/4)) = 16383 caractères.

Si la source de données est une colonne BLOB ou une expression qui renvoie une BLOB. Par exemple, lorsque vous utilisez le formulaire INSERT … SELECT ou dans le code PSQL lorsqu’une variable BLOB est utilisée comme paramètre.

`UPDATE`

affectation

Mettre à jour les données dans une table.

Disponible en

DSQL, ESQL, PSQL

Syntaxe

UPDATE target [[AS] alias]
  SET col_name = <upd_value> [, col_name = <upd_value> ...]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<upd_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Paramètres de l’opérateur `UPDATE`
Paramètre	Description
target	Nom de la table ou de la vue dans laquelle les enregistrements sont mis à jour.
alias	L’alias d’une table ou d’une vue.
col_name	La colonne d’une table ou d’une vue.
upd_value	Expression de la nouvelle valeur de la colonne à mettre à jour dans la table ou la vue par l’instruction.
literal	Litéral
context-variable	La variable de contexte.
other-single-value-expr	Toute autre expression qui renvoie une seule valeur de type de données Firebird ou `NULL`.
search-conditions	Une condition de recherche limitant l’ensemble des lignes à mettre à jour.
cursorname	Le nom du curseur sur lequel se trouve l’enregistrement à mettre à jour.
plan_items	Parties du plan de requête.
sort_items	Les colonnes énumérées dans la clause `ORDER BY'.
m, n	Expressions entières pour limiter le nombre de lignes à mettre à jour.
return_expression	Expression retournée dans la clause `RETURNING`.
collation	Nom de tri existant (pour les types de caractères uniquement).
ret_alias	Alias pour l’expression retournée dans la clause `RETURNING`.
varname	Le nom de la variable PSQL.

L’instruction UPDATE modifie les valeurs des colonnes d’une table, ou d’une ou plusieurs tables sous-jacentes. Les nouvelles valeurs des colonnes sont spécifiées dans l’instruction SET. Les lignes affectées peuvent être limitées par les instructions WHERE et ROWS. Si ni WHERE ni ROWS n’existent, toutes les entrées de la table seront mises à jour.

Utilisation d’un alias

Si vous attribuez un alias à une table ou à une vue, vous doit utiliser l’alias pour spécifier les colonnes de la table.

Exemples

Utilisation correcte :

update Fruit set soort = 'pisang' where ...

update Fruit set Fruit.soort = 'pisang' where ...

update Fruit F set soort = 'pisang' where ...

update Fruit F set F.soort = 'pisang' where ...

Utilisation incorrecte :

update Fruit F set Fruit.soort = 'pisang' where ...

`SET`

Les colonnes à modifier sont spécifiées dans la clause SET. Les colonnes et leurs valeurs sont listées séparées par des virgules. Le nom de la colonne est à gauche, et la valeur ou l’expression à droite.

Il est autorisé d’utiliser les noms de colonnes dans les expressions de droite. Il utilisera toujours l’ancienne valeur de la colonne, même si l’affectation à cette colonne a déjà eu lieu plus tôt dans l’énumération SET. Une colonne ne peut être utilisée qu’une seule fois dans une construction SET.

Example 1. Utilisation de la clause `UPDATE

Données dans le table TSET :

A B
---
1 0
2 0

Après avoir exécuté l’instruction

update tset set a = 5, b = a

A B
---
5 1
5 2

Notez que les anciennes valeurs (1 et 2) sont utilisées pour mettre à jour la colonne b, même après qu’une nouvelle valeur (5) ait été attribuée à la colonne a.

Mot clé DEFAULT

Le mot-clé DEFAULT peut être utilisé dans la clause SET à la place de la valeur de la colonne. Dans ce cas, la colonne prendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

Example 1. Utilisation du mot clé DEFAULT dans l’instruction UPDATE.

CREATE TABLE cars (
  ID INTEGER NOT NULL,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

INSERT INTO cars (1, byyear, name)
VALUES (1, 1985, 'Ford Focus');

-- la colonne BYYEAR se verra attribuer la valeur 1990
UPDATE cars
SET BYYEAR = DEFAULT
WHERE ID = 1;

`WHERE`

La clause WHERE limite l’ensemble des enregistrements à mettre à jour à une condition donnée, ou — en PSQL — à la ligne courante du curseur nommé si la clause WHERE CURRENT OF est spécifiée.

Note	La clause `WHERE CURRENT OF' n’est utilisée qu’en PSQL car DSQL n’a pas d’instruction DSQL pour créer un curseur.

Les littéraux de type chaîne peuvent être précédés d’un nom de jeu de caractères, afin que Firebird comprenne comment interpréter les données.

Example 1. Utilisation de la clause "WHERE" dans l’instruction "UPDATE".

UPDATE addresses
SET city = 'Saint Petersburg', citycode = 'PET'
WHERE city = 'Leningrad';

UPDATE employees
SET salary = 2.5 * salary
WHERE title = 'CEO';

-- noter le préfixe '_'
UPDATE People
SET name = _ISO8859_1 'Hans-Jörg Schäfer'
WHERE id = 53662;

UPDATE employee e
SET salary = salary * 1.05
WHERE EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);

`PLAN`

La clause `PLAN' vous permet de spécifier manuellement un plan pour l’optimiseur.

Example 1. Utilisation de la clause PLAN dans une déclaration UPDATE.

UPDATE company c SET c.company_name =
    ( SELECT k.contact_name
      FROM contact k
      WHERE k.id = c.contact_id
      PLAN (K INDEX (CONTACT_ID)))
WHERE c.company_name IS NULL OR c.company_name = ''
PLAN (C NATURAL)

`ORDER BY` et `ROWS`

La clause ORDER BY vous permet de définir l’ordre dans lequel les enregistrements sont mis à jour, ce qui peut être utile dans certains cas.

La clause ROWS n’a de sens qu’avec la clause ORDER BY.Toutefois, il peut être utilisé séparément.

Avec un seul argument m, ROWS limite la mise à jour aux m premiers enregistrements.

Caractéristiques :

Si m est supérieur au nombre d’enregistrements de la table cible en cours de traitement, l’ensemble des lignes est mis à jour ;
Si m = 0, aucun enregistrement n’est mis à jour ;
Si m < 0, une erreur est émise.

Avec les deux arguments m et n, ROWS limite la mise à jour des enregistrements de m à n inclus.Les deux arguments sont des entiers et commencent par 1.

Caractéristiques :

Si m est supérieur au nombre d’enregistrements dans la table cible, aucun enregistrement n’est mis à jour ;
Si n est supérieur au nombre d’enregistrements dans la table cible, les enregistrements de m à la fin du jeu sont mis à jour ;
Si m < 1 ou n < 1, une erreur est émise ;
Si n = m - 1, aucun enregistrement n’est mis à jour ;
Si n < m - 1, une erreur est générée.

Example 1. Utilisation de la clause ROWS dans la déclaration UPDATE.

-- Accorder une augmentation aux 20 employés les moins bien payés
UPDATE employees
SET salary = salary + 50
ORDER BY salary ASC
ROWS 20;

`SKIP LOCKED`

Affectation

Ne pas tenir compte du blocage.

La proposition SKIP LOCKED force le moteur à sauter les enregistrements verrouillés par d’autres transactions,au lieu d’attendre ou de provoquer des erreurs de conflit.

En utilisant SKIP LOCKED, plusieurs threads de travail peuvent obtenir des éléments de travail exclusifs de la table sans conflit.

Note	Si la phrase `SKIP LOCKED` est utilisée en conjonction avec FIRST/SKIP/ROWS/OFFSET/FETCH, les enregistrements verrouillés sont ignorés en premier,puis les limiteurs FIRST/SKIP/ROWS/OFFSET/FETCH sont appliqués aux enregistrements restants.

Voir aussi: SELECT … SKIP LOCKED,DELETE FROM … SKIP LOCKED.

`RETURNING`

L’instruction UPDATE, peut inclure RETURNING pour retourner les valeurs des enregistrements mis à jour. L’instruction RETURNING peut inclure n’importe quelles colonnes, pas nécessairement seulement celles qui sont mises à jour.

Les valeurs retournées contiennent les changements effectués dans les déclencheurs BEFORE UPDATE mais pas dans les déclencheurs AFTER UPDATE. Les expressions OLD.fieldname et NEW.fieldname peuvent être utilisées comme noms de colonnes. Si OLD. ou NEW. n’est pas spécifié, les nouvelles valeurs de colonnes NEW. sont retournées.

Vous pouvez spécifier un astérisque (*) au lieu d’une liste de colonnes. Dans ce cas, toutes les valeurs des colonnes de la table seront retournées. L’astérisque peut être utilisé avec les spécificateurs NEW ou OLD.

Note

En DML, l’instruction UPDATE avec la clause RETURNING renvoie un curseur (avant Firebird 5.0, elle ne pouvait renvoyer qu’un seul enregistrement).
Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliquées avec la clause FOR pour boucler sur un curseur en PSQL.

Ce comportement peut être modifié dans les futures versions de Firebird.* Si les enregistrements n’ont pas été mis à jour par l’instruction, les valeurs de retour contiennent NULL.

INTO

La clause INTO permet de passer des valeurs aux variables locales. Elle n’est disponible que dans PSQL. Si les enregistrements n’ont pas été mis à jour, rien n’est retourné et les variables spécifiées dans RETURNING conserveront leurs valeurs précédentes.

Example 1. Utilisation de la clause "RETURNING" dans l’instruction "UPDATE".

UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING id, old.last_name, new.last_name;

Example 2. Utilisation de * dans une clause "RETURNING" dans une déclaration "UPDATE".

UPDATE Scholars
SET first_name = 'Hugh', last_name = 'Pickering'
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING old.*;

Mise à jour des colonnes BLOB

La mise à jour des colonnes BLOB modifie toujours complètement leur contenu.Même l’ID BLOB, qui est une référence aux données BLOB et qui est stocké dans la colonne, change.Les colonnes de type BLOB peuvent être modifiées si :

L’application client modifie le BLOB via l’API de Firebird. Dans ce cas, cela dépend de l’application et n’est pas couvert par ce manuel ;

La longueur des chaînes de caractères ne peut dépasser 65 533 octets (64K - 3).

Note	La limite de caractères est calculée au moment de l’exécution. Pour les jeux de caractères à plusieurs octets, cela peut être différent. Par exemple, pour une chaîne UTF8 (4 octets par caractère), la limite du littéral de chaîne serait probablement d’environ (floor (65533/4)) = 16383 caractères.

Si la source de données est une colonne de type BLOB ou une expression retournant BLOB.

`UPDATE OR INSERT`

affectation

Ajout d’une nouvelle entrée ou mise à jour d’une entrée existante dans une table.

Disponible en

DSQL, PSQL

Syntaxe

UPDATE OR INSERT INTO target [(<column_list>)]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [RETURNING <returning_list> [INTO <variables>]]

<column_list> ::= col_name [, col_name ...]

<value_list> ::= <ins_value> [, <ins_value> ...]

<ins_value> ::= <value_expression> | DEFAULT

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::= [:]varname [, [:]varname ...]

Table 1. Paramètres de l’instruction `UPDATE OR INSERT`.
Paramètre	Description
target	Nom de la table ou de la vue dont l’entrée sera mise à jour ou une nouvelle entrée sera insérée.
col_name	La colonne d’une table ou d’une vue.
ins_value	Une expression dont la valeur est utilisée pour insérer ou mettre à jour une table.
literal	Litéral.
context-variable	La variable de contexte.
other-single-value-expr	Toute autre expression qui renvoie une seule valeur du type de données Firebird ou `NULL`.
return_expression	Expression renvoyée dans une clause `RETURNING`.
alias	Alias pour l’expression retournée dans la clause `RETURNING`.
varname	Le nom de la variable PSQL.

L’instruction UPDATE OR INSERT insère ou met à jour un ou plusieurs enregistrements existants. L’action effectuée dépend des valeurs des colonnes dans l’instruction MATCHING (ou, si elle n’est pas spécifiée, des valeurs des colonnes de la clé primaire — PK). Si des enregistrements correspondant aux valeurs spécifiées sont trouvés, ils sont mis à jour. Sinon, un nouvel enregistrement est inséré.

Une correspondance est définie comme une correspondance complète des valeurs des colonnes MATCHING ou PK. La correspondance est vérifiée en utilisant IS NOT DISTINCT, ainsi NULL est mis en correspondance avec NULL.

Note

Limites

Si la table n’a pas de clé primaire, spécifier MATCHING est considéré comme obligatoire ;
Dans la liste MATCHING, tout comme dans la liste des colonnes de mise à jour/insertion, chaque colonne ne peut être mentionnée qu’une seule fois ;
La clause INTO n’est disponible que dans PSQL.

Mot clé `DEFAULT`

Note	Restriction Une colonne pour laquelle le mot-clé `DEFAULT` est utilisé au lieu d’une valeur ne peut pas être utilisée dans une clause `MATCHING`.

Example 1. Utilisation du mot-clé DEFAULT dans une instruction UPDATE OR INSERT.

CREATE TABLE cars (
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
  BYYEAR SMALLINT DEFAULT 1990 NOT NULL,
  NAME VARCHAR(45),
  CONSTRAINT pk_cars PRIMARY KEY (ID)
);

-- la colonne BYYEAR contiendra la valeur 1990
UPDATE OR INSERT INTO cars (byyear, name)
VALUES (DEFAULT, 'Ford Focus')
MATCHING (name);

`RETURNING`

La clause RETURNING peut contenir toutes les colonnes spécifiées dans la déclaration ou d’autres colonnes et expressions. Les valeurs de retour contiennent tous les changements effectués dans les déclencheurs BEFORE mais pas dans les déclencheurs AFTER. Les expressions OLD.fieldname et NEW.fieldname peuvent être utilisées comme valeurs de retour. Pour les noms de colonnes normaux, les nouvelles valeurs sont retournées.

Note	En DSQL, une déclaration avec `RETURNING` ne retourne toujours qu’une seule chaîne de caractères.

Example 1. Utilisation de la clause "RETURNING" dans une instruction "UPDATE OR INSERT".

UPDATE OR INSERT INTO Cows (Name, Number, Location)
VALUES ('Suzy Creamcheese', 3278823, 'Green Pastures')
MATCHING (Number)
RETURNING rec_id
INTO :id;

`DELETE`

affectation

Suppression des données d’une table.

Disponible en

DSQL, ESQL, PSQL

Syntaxe:

DELETE
  FROM target [[AS] alias]
  [WHERE {<search-conditions> | CURRENT OF cursorname}]
  [PLAN <plan_items>]
  [ORDER BY <sort_items>]
  [ROWS m [TO n]]
  [SKIP LOCKED]
  [RETURNING <returning_list> [INTO <variables>]]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]

Table 1. Paramètres de l’opérateur `DELETE`
Paramètre	Description
target	Nom de la table ou de la vue à partir de laquelle les enregistrements doivent être supprimés.
alias	L’alias d’une table ou d’une vue.
col-name	Le nom de la colonne de la table ou de la vue.
search-conditions	Une condition de recherche limitant le jeu d’enregistrements à supprimer.
cursorname	Le nom du curseur sur lequel est positionné l’enregistrement à supprimer.
plan_items	Proposition de plan.
sort_items	Tri de la proposition
m, n	Expressions entières pour limiter le nombre d’entrées à supprimer.
return_expression	Expression renvoyée dans une clause `RETURNING`.
literal	Litéral.
context-variable	La variable de contexte.
other-single-value-expr	Toute autre expression qui renvoie une seule valeur du type de données Firebird ou `NULL`.
collation	Nom de tri existant (pour les types de caractères uniquement).
ret_alias	Alias pour l’expression retournée dans la clause `RETURNING`.
varname	Nom de la variable PSQL.

L’instruction DELETE supprime les lignes d’une table ou d’une ou plusieurs tables de présentation.

Si un alias est spécifié pour une table, il doit être utilisé pour toutes les colonnes de la table.

`WHERE`

La condition de la clause WHERE limite le jeu d’enregistrements à supprimer. Seuls les enregistrements qui satisfont à la condition de recherche ou seulement l’enregistrement curseur nommé actuel sont supprimés.

La suppression avec WHERE CURRENT OF est appelée positioned delete, car elle supprime l’enregistrement à la position actuelle. La suppression avec `WHERE condition' est appelée searched delete, car Firebird recherche les enregistrements qui satisfont à la condition.

Note	En DSQL pur, l’expression `WHERE CURRENT OF` n’a aucun sens car DSQL n’a pas d’instruction pour créer un curseur.

Example 1. Utilisation de la clause WHERE dans l’instruction DELETE.

DELETE FROM People
WHERE first_name <> 'Boris' AND last_name <> 'Johnson';

DELETE FROM employee e
WHERE NOT EXISTS(
  SELECT *
  FROM employee_project ep
  WHERE e.emp_no = ep.emp_no);

DELETE FROM Cities
WHERE CURRENT OF Cur_Cities; -- uniquement en PSQL

`PLAN`

L’instruction `PLAN' vous permet de spécifier manuellement un plan pour l’optimiseur.

Example 1. Utilisation de la clause PLAN dans l’instruction DELETE.

DELETE FROM Submissions
WHERE date_entered < '1-Jan-2002'
PLAN (Submissions INDEX ix_subm_date)

`ORDER BY` et `ROWS`

La clause "ORDER BY" organise l’ensemble avant de le supprimer, ce qui peut être important dans certains cas.

La clause ROWS vous permet de limiter le nombre de lignes à supprimer.Elle n’a de sens qu’en combinaison avec la clause ORDER BY, mais est valable sans elle.

Les valeurs m et n peuvent être des expressions entières quelconques.

Avec un seul argument m, les m premiers enregistrements sont supprimés.L’ordre des enregistrements sans ORDER BY est indéfini (aléatoire).

Notes :

Si m est supérieur au nombre total d’entrées dans l’ensemble, l’ensemble entier est supprimé ;
Si m = 0, aucune suppression n’a lieu ;
Si m < 0, un message d’erreur est émis.

Si les arguments m et n sont spécifiés, la suppression est limitée au nombre d’entrées comprises entre m et n, inclusivement.La numérotation des enregistrements commence à partir de 1.

Note sur l’utilisation de deux arguments :

Si m est supérieur au nombre total de lignes de l’ensemble, aucune ligne n’est supprimée ;
Si m > 0 et ⇐ le nombre de lignes dans l’ensemble, et que n est en dehors de ces valeurs, les lignes de m à la fin de l’ensemble sont supprimées ;
Si m < 1 ou n < 1, un message d’erreur est émis ;
Si n = m - 1, aucune entrée n’est supprimée ;
Si n < m - 1, un message d’erreur est émis.

Example 1. Utilisation de "ORDER BY" et "ROWS" dans l’instruction "DELETE".

Suppression de l’achat le plus ancien

DELETE FROM Purchases ORDER BY ByDate ROWS 1

Suppression des commandes pour les 10 clients ayant les numéros les plus élevés

DELETE FROM Sales ORDER BY custno DESC ROWS 1 TO 10

Supprime tous les enregistrements des ventes, car aucun ROWS n’est spécifié.

DELETE FROM Sales ORDER BY custno DESC

Supprime une entrée "depuis la fin", c’est-à-dire depuis Z…

DELETE FROM popgroups ORDER BY name DESC ROWS 1

Supprime les cinq groupes les plus anciens

DELETE FROM popgroups ORDER BY formed ROWS 5

Aucun tri (ORDER BY) n’est spécifié, donc 8 enregistrements détectés seront supprimés, en commençant par le cinquième.

DELETE FROM popgroups ROWS 5 TO 12

`SKIP LOCKED`

Affectation

Ne pas tenir compte du blocage.

La proposition SKIP LOCKED fait en sorte que le moteur saute les enregistrements verrouillés par d’autres transactions, à la place,d’attendre ou de provoquer des erreurs lorsqu’un conflit se produit.

Cette fonctionnalité est utile pour mettre en œuvre des files d’attente de travail, dans lesquelles un ou plusieurs processus envoient des données à une table et génèrent un événement, tandis que les processus de travail écoutent ces événements et lisent/suppriment des éléments de la table.En utilisant SKIP LOCKED, plusieurs threads de travail peuvent obtenir des éléments de travail exclusifs de la table sans conflit.

Note	Si la phrase `SKIP LOCKED` est utilisée en conjonction avec FIRST/SKIP/ROWS/OFFSET/FETCH, les enregistrements verrouillés sont ignorés en premier,puis les limiteurs FIRST/SKIP/ROWS/OFFSET/FETCH sont appliqués aux enregistrements restants.

Voir aussi: SELECT … SKIP LOCKED,UPDATE … SKIP LOCKED.

Example 1. Utiliser la phrase SKIP LOCKED pour organiser une file d’attente

Préparation des métadonnées.

create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

Envoi de données par une application ou un sous-programme

insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

L’application cliente peut écouter l’événement EMAILS_QUEUE pour envoyer des courriels à l’aide de cette requête :

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

Plus d’une instance d’une application peut fonctionner, par exemple pour l’équilibrage de la charge.

`RETURNING`

L’opérateur DELETE peut contenir une construction RETURNING pour retourner les valeurs des enregistrements en cours de suppression.Toutes les colonnes et expressions peuvent être spécifiées dans RETURNING.Un astérisque (*) peut être spécifié à la place d’une liste de colonnes, auquel cas toutes les colonnes de l’enregistrement supprimé seront retournées.

Note

En DML, l’instruction DELETE avec la clause RETURNING retourne un curseur (avant Firebird 5.0, elle ne pouvait retourner qu’un seul enregistrement).Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliqués avec la clause FOR pour boucler sur le curseur dans PSQL.Ce comportement peut être modifié dans les futures versions de Firebird.Si les enregistrements n’ont pas été supprimés, les colonnes retournées contiennent NULL ;
En PSQL, si la ligne n’a pas été supprimée, rien n’est renvoyé et les variables cibles conservent leurs valeurs ;
La clause INTO n’est disponible que dans PSQL.

Example 1. Utilisation de la clause RETOURNER dans l’instruction DELETE.

DELETE FROM Scholars
WHERE first_name = 'Henry' AND last_name = 'Higgins'
RETURNING last_name, fullname, id

DELETE FROM Dumbbells
ORDER BY iq DESC
ROWS 1
RETURNING last_name, iq
INTO :lname, :iq;

DELETE FRMO TempSales ts
WHERE ts.id = tempid
RETURNING ts.qty
INTO new.qty;

`MERGE`

affectation

Fusionner les enregistrements source dans la table cible (ou la vue actualisable).

Disponible en

DSQL, PSQL

Syntaxe

MERGE
  INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join condition>
  <merge when> [<merge when> ...]
  [<plan clause>]
  [<order by clause>]
  [<returning clause>]

<source> ::= tablename | (<select_stmt>)

<merge when> ::=
    <merge when matched>
  | <merge when not matched by target>
  | <merge when not matched by source>

<merge when matched> ::=
  WHEN MATCHED [ AND <condition> ]
    THEN { UPDATE SET <assignment_list> | DELETE }

<merge when not matched by target> ::=
  WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
    THEN INSERT [ <left paren> <column_list> <right paren> ]
    VALUES <left paren> <value_list> <right paren>

<merge when not matched by source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
    { UPDATE SET <assignment list> | DELETE }

<assignment_list> ::=
  col_name = <m_value> [, colname = <m_value> ...]

<column_list> ::= col_name [, col_name ...]

<value_list> ::= <m_value> [, <m_value> ...]

<m_value> ::= <value_expression> | DEFAULT

<returning clause> ::= RETURNING <returning_list> [INTO <variable_list>]

<returning_list> ::= * | <output_column> [, <output_column]

<output_column> ::=
    target.* | NEW.* | OLD.*
  | <return_expression> [COLLATE collation] [[AS] ret_alias]

<return_expression> ::=
    <value_expression>
  | [target.]col_name
  | NEW.col_name
  | OLD.col_name

<value_expression> ::=
    <literal>
  | <context-variable>
  | <other-single-value-expr>

<variables> ::=
  [:]varname [, [:]varname ...]

Table 1. Paramètres de l’opérateur `MERGE`
Paramètre	Description
target	La table ou la vue actualisable cible.
source	Source de données : peut être une table, une vue, une procédure stockée ou une table dérivée.
target_alias	L’alias de la table ou de la vue cible.
source_alias	Alias de la source.
join condition	Une condition pour connecter la table cible et la source.
condition	Conditions de contrôle supplémentaires dans les clauses `WHEN MATCHED` ou `WHEN NOT MATCHED`.
col_name	La colonne de la table ou de la vue cible.
m_value	Une valeur assignée à une colonne dans la table cible. Une expression qui peut contenir des littéraux, des variables PSQL, des colonnes d’une source.
return_expression	Expression renvoyée dans une clause `RETURNING`.
ret_alias	Alias pour l’expression retournée dans la clause `RETURNING`.
varname	Le nom de la variable PSQL.

L’instruction MERGE fusionne les enregistrements de la table source et de la table cible (ou une vue actualisable).Lors de l’exécution de l’instruction MERGE, les enregistrements sources sont lus et INSERT, UPDATE ou DELETE sont exécutés pour la cible.en fonction des conditions.

La source peut être une table, une vue, une procédure stockée ou une table dérivée. Lorsque l’instruction MERGE est exécuté, une connexion est établie entre la table source (USING) et la table cible. Le type de connexion dépend de la présence de clauses WHEN NOT MATCHED :

<merge when not matched by target> and <merge when not matched by source> — FULL JOIN
<fusionner quand la source ne correspond pas> — JOINT DROIT
<merge quand il n’y a pas de correspondance avec la cible> — LEFT JOIN
seulement <fusionner en cas de correspondance> — INNER JOIN

L’action sur la table cible et la condition dans laquelle elle se produit sont décrites dans la clause WHEN. Les clauses multiples WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE sont autorisées.

Si la condition de la clause WHEN n’est pas remplie, Firebird la saute et passe à la clause suivante. Cela se produira jusqu’à ce que la condition de l’une des clauses WHEN ne soit pas satisfaite. Dans ce cas, l’action associée à la clause WHEN est exécutée et l’enregistrement suivant du résultat de la connexion entre la table source (USING) et la table cible est ignoré. Une seule action est effectuée pour chaque enregistrement de résultat de connexion.

Note

La clause WHEN NOT MATCHED [BY TARGET] est évaluée en fonction de la source, c’est-à-dire la table ou le jeu de données spécifié dans la clause USING. En effet, si l’enregistrement source ne correspond pas à l’enregistrement cible, la clause INSERT est exécutée. Bien entendu, si l’enregistrement cible ne correspond pas à l’enregistrement source, aucune action n’est effectuée.

Actuellement, la variable ROW_COUNT renvoie 1, même si plus d’un enregistrement a été modifié ou inséré. CORE-4400.

`WHEN MATCHED`

Spécifie que toutes les lignes target qui correspondent aux lignes retournées par <source> ON <condition de jonction> et qui satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou supprimées (claus DELETE) selon la clause <merge when matched>.

Plusieurs clause WHEN MATCHED peuvent être spécifiées. Si plus d’une clause WHEN MATCHED est spécifiée, elles doivent toutes être complétées par des conditions de recherche supplémentaires, sauf la dernière.

Une instruction MERGE ne peut pas mettre à jour une ligne plus d’une fois ou mettre à jour et supprimer la même ligne en même temps.

Note

Si la condition WHEN MATCHED est présente et que plusieurs enregistrements correspondent à des enregistrements de la table cible, une erreur sera émise.

Avant Firebird 4.0, UPDATE sera exécuté pour tous les enregistrements sources correspondants, et chaque mise à jour ultérieure écrasera la précédente. Ce comportement n’est pas conforme au standard SQL.

Dans la liste SET de la clause UPDATE, vous pouvez utiliser le mot clé DEFAULT à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

`WHEN NOT MATCHED [BY TARGET]`

Spécifie que toutes les lignes target qui ne correspondent pas aux lignes retournées par l’expression <source> ON <join condition> et qui satisfont à des conditions de recherche supplémentaires sont insérées dans la table cible (clause INSERT) selon la clause <merge when not matched by target>.

Plusieurs clauses "SANS CORRESPONDANCE [PAR LA CIBLE]" sont autorisées. Si plus d’une clause est spécifiéeLorsqu’il n’y a pas de correspondance [avec la cible], ils doivent tous être complétés par des termes de recherche supplémentaires, sauf le dernier.

Dans la liste VALUES d’une clause INSERT, le mot clé DEFAULT peut être utilisé à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible.S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

`WHEN NOT MATCHED BY SOURCE`

Indique que toutes les lignes target qui ne correspondent pas aux lignes renvoyées par <source> ON <join condition> et qui satisfont à des conditions de recherche supplémentaires, (clause`UPDATE`) ou sont supprimées (clause`DELETE`) selon la clause <merge when not matched by source>.

La clause WHEN NOT MATCHED BY SOURCE est disponible à partir de Firebird 5.0.

Plusieurs clause WHEN NOT MATCHED BY SOURCE peuvent être spécifiées. Si plus d’une clause est spécifiéeLes clauses WHEN NOT MATCHED BY SOURCE doivent toutes être complétées par des conditions de recherche supplémentaires, sauf la dernière.

Dans la liste SET de la clause`UPDATE`, le mot clé DEFAULT peut être utilisé à la place de la valeur de la colonne. Dans ce cas, la colonne obtiendra la valeur par défaut spécifiée lors de la définition de la table cible. S’il n’y a pas de valeur par défaut pour la colonne, celle-ci prendra la valeur NULL.

Note	Veuillez noter ! Dans la liste `SET` d’une clause `UPDATE`, il n’est pas judicieux d’utiliser des expressions faisant référence à <source>,car aucune des entrées de <source> ne correspond aux entrées de cible.

`Exemples`

Example 1. Les instructions simples `MERGE'.

MERGE INTO books b
USING purchases p
ON p.title = b.title AND p.booktype = 'bk'
WHEN MATCHED THEN
  UPDATE SET b.descr = b.descr || '; ' || p.descr
WHEN NOT MATCHED THEN
  INSERT (title, descr, bought)
  VALUES (p.title, p.descr, p.bought);

-- utiliser une table de dérivation
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
  UPDATE SET name = cd.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (cd.id, cd.name);

-- ainsi que la récurrence CTE
MERGE INTO numbers
USING (
  WITH RECURSIVE r(n) AS (
    SELECT 1 FROM rdb$database
    UNION ALL
    SELECT n+1 FROM r WHERE n < 200
  )
  SELECT n FROM r
) t
ON numbers.num = t.n
WHEN NOT MATCHED THEN
  INSERT(num) VALUES(t.n);

-- en utilisant la clause DELETE
MERGE INTO SALARY_HISTORY
USING (
  SELECT EMP_NO
  FROM EMPLOYEE
  WHERE DEPT_NO = 120) EMP
ON SALARY_HISTORY.EMP_NO = EMP.EMP_NO
WHEN MATCHED THEN DELETE

Example 2. Utilisation de l’instruction MERGE avec des conditions supplémentaires dans les clauses WHEN [NOT] MATCHED.

Dans l’exemple suivant, la table PRODUCT_INVENTORY est mise à jour quotidiennement en fonction des commandes traitées dans la table SALES_ORDER_LINE. Si le nombre de commandes pour un produit est tel que le niveau de stock du produit tombe à zéro ou devient encore plus bas, la ligne pour ce produit est supprimée de la table PRODUCT_INVENTORY.

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE

Example 3. Utilisation de l’instruction "MERGE" avec "WHEN NOT MATCHED BY SOURCE".

L’exemple suivant met à jour les enregistrements de la table cible s’ils sont trouvés dans la table source et les supprime s’ils ne sont pas trouvés.

MERGE
  INTO customers c
  USING new_customers nc
  ON (c.id = nc.id)
  WHEN MATCHED THEN
    UPDATE SET
	  name = cd.name
  WHEN NOT MATCHED BY SOURCE THEN
	DELETE

Voir aussi :

SELECT, INSERT, UPDATE, DELETE.

`RETURNING`

L’instruction MERGE peut contenir une construction RETURNING pour retourner les valeurs des lignes ajoutées, modifiées ou supprimées.Toutes les colonnes de la table cible (la vue mise à jour) et les expressions peuvent être spécifiées dans RETURNING.

Les valeurs de retour contiennent les changements effectués dans les déclencheurs BEFORE.

Les noms de colonnes peuvent être préfixés avec NEW et OLD pour spécifier quelle valeur de colonne vous voulez avant ou après la modification.

Un astérisque (*) peut être spécifié à la place d’une liste de colonnes, auquel cas toutes les colonnes de la table cible seront retournées.Les préfixes NEW et OLD peuvent être utilisés en conjonction avec un astérisque.

Pour les clauses WHEN MATCHED UPDATE et MERGE WHEN NOT MATCHED, les noms de colonne non spécifiés ou spécifiés parou leurs alias sont compris comme des colonnes préfixées avec NEW, pour les clauses MERGE WHEN MATCHED DELETE - avec le préfixe OLD.

Note

En DML, l’instruction MERGE avec la clause RETURNING retourne un curseur (avant Firebird 5.0, il ne pouvait retourner qu’un seul enregistrement).Actuellement, les instructions avec la clause RETURNING ne peuvent pas être appliqués avec la clause FOR pour boucler sur le curseur dans PSQL.Ce comportement peut être modifié dans les futures versions de Firebird.
La clause INTO n’est disponible que dans PSQL.

Example 1. Utilisation de l’instruction MERGE avec la clause RETOURNEMENT.

Modifions un peu notre exemple précédent pour qu’il ne concerne qu’une seule ligne, et ajoutons une instruction RETURNING qui renvoie l’ancienne et la nouvelle quantité de l’article et la différence entre ces valeurs.

MERGE INTO PRODUCT_IVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
    AND SL.ID_PRODUCT = :ID_PRODUCT
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY
INTO :OLD_QUANTITY, :NEW_QUANTITY, :DIFF_QUANTITY

`EXECUTE PROCEDURE`

affectation

Exécution d’une procédure stockée.

Disponible en

DSQL, ESQL, PSQL

Syntaxe

EXECUTE PROCEDURE procname
   [{ <inparam-list> | ( <inparam-list> ) }]
   [RETURNING_VALUES { <outvar-list> | ( <outvar-list> ) }]

<inparam-list> ::=
  <inparam> [, <inparam> ...]

<outvar-list> ::=
  <outvar> [, <outvar> ...]

<outvar> ::= [:]varname

Table 1. Paramètres pour l’instruction `EXECUTE PROCEDURE'.
Paramètre	Description
procname	Le nom de la procédure stockée.
inparam	Une expression dont le type est compatible avec le paramètre d’entrée de la procédure stockée.
varname	Une variable PSQL qui renvoie la valeur du paramètre de sortie de la procédure.

L’instruction EXECUTE PROCEDURE exécute une procédure stockée, en récupérant une liste d’un ou plusieurs paramètres d’entrée s’ils sont définis, et en renvoyant un ensemble de valeurs d’une seule ligne s’ils sont définis.

"Procédures stockées" exécutables

L’instruction EXECUTE PROCEDURE est le style d’appel de procédure stockée le plus couramment utilisé.pour modifier certaines données. Leur code ne contient pas l’instruction SUSPEND.Ces procédures stockées peuvent renvoyer un ensemble de données ne comprenant pas plus d’une chaîne de caractères.Cet ensemble peut être passé aux variables d’une autre procédure (appelante) en utilisant la clause RETURNING_VALUES.Les interfaces client ont généralement des wrappers API qui peuvent extraire les valeurs de sortie dans un tampon d’une seule ligne lorsque la procédure est appelée via EXECUTE PROCEDURE dans DSQL.

Lorsqu’un autre type de procédure (procédures sélectives) est appelé avec EXECUTE PROCEDURE, seule la première procédure est appelée.du jeu de résultats sera retourné, même si cette procédure est susceptible de retourner un résultat de plusieurs lignes.Les procédures stockées "sélectives" doivent être appelées avec l’instruction SELECT, auquel cas elles se comportent comme des tables virtuelles.

Note

Dans PSQL et DSQL, les paramètres d’entrée peuvent être n’importe quelle expression compatible avec le type ;
Bien que les parenthèses pour séparer la liste des paramètres à passer soient facultatives après le nom de la procédure stockée, il est conseillé de les utiliser ;
La clause RETURNING_VALUES n’est disponible que dans PSQL.

Example 1. Utilisation de l’instruction `EXECUTE PROCEDURE' dans PSQL

EXECUTE PROCEDURE MakeFullName(:First_Name, :Middle_Name, :Last_Name)
RETURNING_VALUES :FullName;

Dans cet instruction, l’utilisation des deux points (“:”) pour les paramètres d’entrée et de sortie est facultative.

Il est permis d’utiliser des expressions comme paramètres.

EXECUTE PROCEDURE MakeFullName
   ('Mr./Mrs. ' || First_Name, Middle_Name, upper(Last_Name))
RETURNING_VALUES FullName;

Example 2. Appel de l’instruction EXECUTE PROCEDURE dans isql.

EXECUTE PROCEDURE MakeFullName
  'J', 'Edgar', 'Hoover';

`EXECUTE BLOCK`

affectation

Exécution d’un bloc PSQL anonyme.

Disponible en

DSQL

Syntaxe

EXECUTE BLOCK [(<inparams>)]
  [RETURNS (<outparams>)]
  <psql-module-body>

<inparams> ::= <param_decl> = ? [, <inparams> ]

<outparams> ::= <param_decl> [, <outparams>]

<param_decl> ::= paramname <type> [NOT NULL] [COLLATE collation]

<type> ::=
    <non_array_datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<non_array_datatype> ::=
    <scalar_datatype> | <blob_datatype>

<scalar_datatype> ::= Voir Syntaxe des types de données scalaires.

<blob_datatype> ::= Voir syntaxe des types de données BLOB.

<psql-routine-body> ::=
  Voir syntaxe du corps du module.

Table 1. Paramètres de l’instruction `EXECUTE BLOCK`.
Paramètre	Description
param_decl	Une description du paramètre d’entrée ou de sortie.
paramname	Nom du paramètre d’entrée ou de sortie de la procédure. Peut contenir jusqu’à 63 caractères. Le nom du paramètre doit être unique parmi les paramètres d’entrée et de sortie de la procédure, ainsi que parmi ses variables locales.
non_array_datatype	Type de données SQL à l’exclusion des tableaux.
collation	Ordre de tri.
domain	Domaine.
rel	Le nom d’une table ou d’une vue.
col	Le nom de la colonne de la table ou de la vue.

Exécute un bloc de code PSQL comme s’il s’agissait d’une procédure stockée, éventuellement avec des paramètres d’entrée et de sortie et des variables locales. Cela permet à l’utilisateur d’exécuter PSQL "à la volée" dans un contexte DSQL.

Exemples:

Cet exemple saisit les nombres de 0 à 127 et leurs caractères ASCII correspondants dans la table ASCIITABLE :

EXECUTE BLOCK
AS
  DECLARE i INT = 0;
BEGIN
  WHILE (i < 128) DO
  BEGIN
    INSERT INTO AsciiTable VALUES (:i, ascii_char(:i));
    i = i + 1;
  END
END

L’exemple suivant calcule la moyenne géométrique de deux nombres et la renvoie à l’utilisateur :

EXECUTE BLOCK (
  x DOUBLE PRECISION = ?,
  y DOUBLE PRECISION = ?)
RETURNS (gmean DOUBLE PRECISION)
AS
BEGIN
  gmean = sqrt(x*y);
  SUSPEND;
END

Comme ce bloc a des paramètres d’entrée, il doit être préparé à l’avance.Il est alors possible de définir les paramètres et d’exécuter le bloc.La façon dont cela est fait, et si cela peut être fait du tout, dépend du logiciel client.Voir les notes ci-dessous.

Notre dernier exemple prend deux valeurs entières, la plus petite et la plus grande.Pour tous les nombres compris dans l’intervalle le plus petit…le plus grand, le bloc fournit le nombre lui-même, son carré, le cube et la quatrième puissance.

EXECUTE BLOCK (smallest INT = ?, largest INT = ?)
RETURNS (
  number INT,
  square BIGINT,
  cube BIGINT,
  fourth BIGINT)
AS
BEGIN
  number = smallest;
  WHILE (number <= largest) DO
  BEGIN
    square = number * number;
    cube = number * square;
    fourth = number * cube;
    SUSPEND;
    number = number + 1;
  END
END

Là encore, la manière dont vous pouvez définir les valeurs des paramètres dépend du logiciel client.

Voir aussi :

instructions linguistiques PSQL.

Paramètres d’entrée et de sortie

L’exécution d’un bloc sans paramètres d’entrée devrait être possible avec tout client Firebird qui permet à l’utilisateur de saisir ses propres instructions DSQL.S’il y a des paramètres d’entrée, les choses se compliquent : ces paramètres doivent recevoir leurs valeurs après la préparation de l’instruction, mais avant qu’elle ne puisse être exécutée.Cela nécessite des fonctionnalités spéciales que toutes les applications clientes ne possèdent pas (par exemple, isql ne propose pas une telle fonctionnalité).

Le serveur n’accepte que les points d’interrogation (" ?") comme caractères de remplacement pour les valeurs d’entrée, et non les " :[remplaçable]`a'", " :MyParam'" etc. ou les valeurs littérales.Le logiciel client peut prendre en charge la forme " :[remplaçable]xxx``", auquel cas la demande sera prétraitée avant d’être envoyée au serveur.

Si le bloc a des paramètres de sortie, vous devez utiliser SUSPEND sinon rien ne sera renvoyé.

La sortie est toujours renvoyée sous forme d’ensemble de données, comme avec l’instruction SELECT.Vous ne pouvez pas utiliser RETURNING_VALUES ou exécuter un bloc en retournant des valeurs dans certaines variables à l’aide de INTO, même si une seule chaîne est retournée.

Pour plus d’informations sur les paramètres et les déclarations de variables, [TYPE OF] [remplaçable] "domaine", TYPE OF COLUMN, etc.voir le chapitre DECLARE VARIABLE.

instruction de terminaison

Certains éditeurs d’instructions SQL — notamment l’utilitaire isql fourni avec Firebird, et peut-être certains éditeurs tiers — utilisent une convention interne qui exige que toutes les instructions soient terminées par un point-virgule.

Cela crée un conflit avec la syntaxe PSQL lors du codage dans ces environnements. Si vous n’êtes pas familier avec ce problème et sa solution, veuillez étudier les détails dans le chapitre PSQL dans la section intitulée Changer le terminateur dans isql.

Échantillonnage À Partir D'une Table Dérivée (Derived Table)

Échantillonnage à partir d’une table dérivée (derived table)

Tableaux de dérivés latéraux

docnext count = 60

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

Connexions JOIN

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

Des connexions qualifiées

Connexions avec des conditions explicites

Connexions par colonnes nommées

Connexions naturels (NATURAL JOIN)

Connexion transversale (CROSS JOIN)

Connexions implicites

Mélange de connexion explicite et implicite

Noms de champs ambigus dans les sélections

Connexions aux procédures stockées

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

WHERE

Exemples

GROUP BY

Exemples

HAVING

Exemples

WINDOW

Exemples

PLAN

Des plans simples

Plans composites

UNION

Exemples

Syntaxe complète SELECT

INSERT

INSERT …​ VALUES

Mot clé DEFAULT

INSERT …​ SELECT

INSERT …​ DEFAULT VALUES

Directive OVERRIDING

RETURNING

Insertion de colonnes BLOB

UPDATE

Utilisation d’un alias

Exemples

SET

Mot clé DEFAULT

WHERE

PLAN

ORDER BY et ROWS

SKIP LOCKED

RETURNING

INTO

Mise à jour des colonnes BLOB

UPDATE OR INSERT

Mot clé DEFAULT

RETURNING

DELETE

WHERE

PLAN

ORDER BY et ROWS

SKIP LOCKED

RETURNING

MERGE

WHEN MATCHED

WHEN NOT MATCHED [BY TARGET]

WHEN NOT MATCHED BY SOURCE

Exemples

RETURNING

EXECUTE PROCEDURE

"Procédures stockées" exécutables

EXECUTE BLOCK

Paramètres d’entrée et de sortie

instruction de terminaison

Connexions `JOIN`

Connexions naturels (`NATURAL JOIN`)

Connexion transversale (`CROSS JOIN`)

`WHERE`

`GROUP BY`

`WINDOW`

`PLAN`

`UNION`

Syntaxe complète `SELECT`

`INSERT`

`INSERT … VALUES`

Mot clé `DEFAULT`

`INSERT … SELECT`

`INSERT … DEFAULT VALUES`

Directive `OVERRIDING`

`RETURNING`

`UPDATE`

`SET`

`WHERE`

`PLAN`

`ORDER BY` et `ROWS`

`SKIP LOCKED`

`RETURNING`

`UPDATE OR INSERT`

Mot clé `DEFAULT`

`RETURNING`

`DELETE`

`WHERE`

`PLAN`

`ORDER BY` et `ROWS`

`SKIP LOCKED`

`RETURNING`

`MERGE`

`WHEN MATCHED`

`WHEN NOT MATCHED [BY TARGET]`

`WHEN NOT MATCHED BY SOURCE`

`Exemples`

`RETURNING`

`EXECUTE PROCEDURE`

`EXECUTE BLOCK`