PostgreSQLLa base de données la plus sophistiquée au monde.

SELECT

SELECT, TABLE, WITH — récupère des lignes d'une table ou d'une vue

Synopsis

[ WITH [ RECURSIVE ] requête_with [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] nom_d_affichage ] [, ...]
    [ FROM éléments_from [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW nom_window AS ( définition_window ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
    [ ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { nombre | ALL } ]
    [ OFFSET début ] [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ total ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF nom_table [, ...] ] [ NOWAIT ] [...] ]

avec éléments_from qui peut être :

    [ ONLY ] nom_table [ * ] [ [ AS ] alias [ ( alias_colonne [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( alias_colonne [, ...] ) ]
    nom_requête_with [ [ AS ] alias [ ( alias_colonne [, ...] ) ] ]
    nom_fonction ( [ argument [, ...] ] ) [ AS ] alias [ ( alias_colonne [, ...] | définition_colonne [, ...] ) ]
    nom_fonction ( [ argument [, ...] ] ) AS ( définition_colonne [, ...] )
    éléments_from [ NATURAL ] type_jointure éléments_from [ ON condition_jointure | USING ( colonne_jointure [, ...] ) ]

et requête_with est :

    nom_requête_with [ ( nom_colonne [, ...] ) ] AS ( select )

TABLE { [ ONLY ] nom_table [ * ] | nom_requête_with }

Description

SELECT récupère des lignes de zéro ou plusieurs tables. Le traitement général de SELECT est le suivant :

  1. Toutes les requêtes dans la liste WITH sont évaluées. Elles jouent le rôle de tables temporaires qui peuvent être référencées dans la liste FROM. Une requête WITH qui est référencée plus d'une fois dans FROM n'est calculée qu'une fois (voir Clause WITH vi-dessous).

  2. Tous les éléments de la liste FROM sont calculés. (Chaque élément dans la liste FROM est une table réelle ou virtuelle.) Si plus d'un élément sont spécifiés dans la liste FROM, ils font l'objet d'une jointure croisée (cross-join). (Voir Clause FROM ci-dessous.)

  3. Si la clause WHERE est spécifiée, toutes les lignes qui ne satisfont pas les conditions sont éliminées de l'affichage. (Voir Clause WHERE ci-dessous.)

  4. Si la clause GROUP BY est spécifiée, l'affichage est divisé en groupes de lignes qui correspondent à une ou plusieurs valeurs. Si la clause HAVING est présente, elle élimine les groupes qui ne satisfont pas la condition donnée. (Voir Clause GROUP BY et Clause HAVING ci-dessous.)

  5. Les lignes retournées sont traitées en utilisant les expressions de sortie de SELECT pour chaque ligne sélectionnée. (Voir Liste SELECT ci-dessous.)

  6. En utilisant les opérateurs UNION, INTERSECT et EXCEPT, l'affichage de plusieurs instructions SELECT peut être combiné pour former un ensemble unique de résultats. L'opérateur UNION renvoie toutes les lignes qui appartiennent, au moins, à l'un des ensembles de résultats. L'opérateur INTERSECT renvoie toutes les lignes qui sont dans tous les ensembles de résultats. L'opérateur EXCEPT renvoie les lignes qui sont présentes dans le premier ensemble de résultats mais pas dans le deuxième. Dans les trois cas, les lignes dupliquées sont éliminées sauf si ALL est spécifié. (Voir Clause UNION, Clause INTERSECT et Clause EXCEPT ci-dessous.)

  7. Si la clause ORDER BY est spécifiée, les lignes renvoyées sont triées dans l'ordre spécifié. Si ORDER BY n'est pas indiqué, les lignes sont retournées dans l'ordre qui permet la réponse la plus rapide du système. (Voir Clause ORDER BY ci-dessous.)

  8. DISTINCT élimine les lignes dupliquées du résultat. DISTINCT ON élimine les lignes qui correspondent à toutes les expressions données. ALL (la valeur par défaut) renvoie toutes les lignes candidates, y compris les lignes dupliquées. (Voir Clause DISTINCT ci-dessous.)

  9. Si les clauses LIMIT (ou FETCH FIRST) ou OFFSET sont spécifiées, l'instruction SELECT ne renvoie qu'un sous-ensemble de lignes de résultats. (Voir Clause LIMIT ci-dessous.)

  10. Si la clause FOR UPDATE ou FOR SHARE est spécifiée, l'instruction SELECT verrouille les lignes sélectionnées contre les mises à jour concurrentes. (Voir Clause FOR UPDATE/FOR SHARE ci-dessous.)

Le droit SELECT sur chaque colonne utilisée dans une commande SELECT est nécessaire pour lire ses valeurs. L'utilisation de FOR UPDATE ou de FOR SHARE requiert en plus le droit UPDATE (pour au moins une colonne de chaque table sélectionnée).

Paramètres

Clause WITH

La clause WITH vous permet de spécifier une ou plusieurs sous-requêtes qui peuvent être utilisées par leur nom dans la requête principale. Les sous-requêtes se comportent comme des tables temporaires ou des vues pendant la durée d'exécution de la requête principale.

Un nom (sans qualification de schéma) doit être spécifié pour chaque requête WITH. Optionnellement, une liste de noms de colonnes peut être spécifié ; si elle est omise, les noms de colonnes sont déduites de la sous-requête.

Si RECURSIVE est spécifié, la sous-requête peut se référencer elle même. Une sous-requête de ce type doit avoir la forme

terme_non_récursif UNION [ ALL ] terme_récursif

où l'auto-référence récursive doit apparaître dans la partie droite de l'UNION. Seule une auto-référence récursive est autorisée par requête.

Un autre effet de RECURSIVE est que les requêtes WITH n'ont pas besoin d'être ordonnées : une requête peut en référencer une autre qui se trouve plus loin dans la liste (toutefois, les références circulaires, ou récursion mutuelle, ne sont pas implémentées). Sans RECURSIVE, les requêtes WITH ne peuvent référencer d'autres requêtes WITH soœurs que si elles sont déclarées avant dans la liste WITH.

Une propriété utile des requêtes WITH est qu'elles ne sont évaluées qu'une seule fois par exécution de la requête principale, même si la requête principale les utilise plus d'une fois.

Voir Section 7.8, « Requêtes WITH (Common Table Expressions) » pour plus d'informations.

Clause FROM

La clause FROM spécifie une ou plusieurs tables source pour le SELECT. Si plusieurs sources sont spécifiées, le résultat est un produit cartésien (jointure croisée) de toutes les sources. Mais habituellement, des conditions de qualification sont ajoutées pour restreindre les lignes renvoyées à un petit sous-ensemble du produit cartésien.

La clause FROM peut contenir les éléments suivants :

nom_table

Le nom (éventuellement qualifié par le nom du schéma) d'une table ou vue existante. Si ONLY est spécifié avant le nom de la table, seule cette table est parcourue. Dans le cas contraire, la table et toutes ses tables filles (s'il y en a) sont parcourues. En option, * peut être ajouté après le nom de la table pour indiquer explicitement que les tables filles sont inclues.

alias

Un nom de substitution pour l'élément FROM contenant l'alias. Un alias est utilisé par brièveté ou pour lever toute ambiguïté lors d'auto-jointures (la même table est parcourue plusieurs fois). Quand un alias est fourni, il cache complètement le nom réel de la table ou fonction ; par exemple, avec FROM foo AS, le reste du SELECT doit faire référence à cet élément de FROM par f et non pas par foo. Si un alias est donné, une liste d'alias de colonnes peut aussi être saisi comme noms de substitution pour différentes colonnes de la table.

select

Un sous-SELECT peut apparaître dans la clause FROM. Il agit comme si sa sortie était transformée en table temporaire pour la durée de cette seule commande SELECT. Le sous-SELECT doit être entouré de parenthèses et un alias doit lui être fourni. Une commande VALUES peut aussi être utilisée ici.

nom_requête_with

Une requête WITH est référencée par l'écriture de son nom, exactement comme si le nom de la requête était un nom de table (en fait, la requête WITH cache toutes les tables qui auraient le même nom dans la requête principale. Si nécessaire, vous pouvez accéder à une table réelle du même nom en précisant le schéma du nom de la table). Un alias peut être indiqué de la même façon que pour une table.

nom_fonction

Des appels de fonctions peuvent apparaître dans la clause FROM. (Cela est particulièrement utile pour les fonctions renvoyant des ensembles de résultats, mais n'importe quelle fonction peut être utilisée.) Un appel de fonction agit comme si la sortie était transformée en table temporaire pour la durée de cette seule commande SELECT. Un alias peut aussi être utilisé. Si un alias est donné, une liste d'alias de colonnes peut être ajoutée pour fournir des noms de substitution pour un ou plusieurs attributs du type composé de retour de la fonction. Si la fonction a été définie comme renvoyant le type de données record, alors un alias ou un mot clé AS doit être présent, suivi par une liste de définitions de colonnes de la forme ( nom_colonne type_données [, ... ] ). La liste de définitions de colonnes doit correspondre au nombre réel et aux types réels des colonnes renvoyées par la fonction.

type_jointure

Un des éléments

  • [ INNER ] JOIN

  • LEFT [ OUTER ] JOIN

  • RIGHT [ OUTER ] JOIN

  • FULL [ OUTER ] JOIN

  • CROSS JOIN

Pour les types de jointures INNER et OUTER, une condition de jointure doit être spécifiée, à choisir parmi NATURAL, ON condition_jointure ou USING (colonne_jointure [, ...]). Voir ci-dessous pour la signification. Pour CROSS JOIN, aucune de ces clauses ne doit apparaître.

Une clause JOIN combine deux éléments FROM. Les parenthèses peuvent être utilisées pour déterminer l'ordre d'imbrication. En l'absence de parenthèses, les JOIN sont imbriqués de gauche à droite. Dans tous les cas, JOIN est plus prioritaire que les virgules séparant les éléments FROM.

CROSS JOIN et INNER JOIN produisent un simple produit cartésien. Le résultat est identique à celui obtenu lorsque les deux éléments sont listés au premier niveau du FROM, mais restreint par la condition de jointure (si elle existe). CROSS JOIN est équivalent à INNER JOIN ON (TRUE), c'est-à-dire qu'aucune ligne n'est supprimée par qualification. Ces types de jointure sont essentiellement une aide à la notation car ils ne font rien de plus qu'un simple FROM et WHERE.

LEFT OUTER JOIN renvoie toutes les lignes du produit cartésien qualifié (c'est-à-dire toutes les lignes combinées qui satisfont la condition de jointure), plus une copie de chaque ligne de la table de gauche pour laquelle il n'y a pas de ligne à droite qui satisfasse la condition de jointure. La ligne de gauche est étendue à la largeur complète de la table jointe par insertion de valeurs NULL pour les colonnes de droite. Seule la condition de la clause JOIN est utilisée pour décider des lignes qui correspondent. Les conditions externes sont appliquées après coup.

À l'inverse, RIGHT OUTER JOIN renvoie toutes les lignes jointes plus une ligne pour chaque ligne de droite sans correspondance (complétée par des NULL pour le côté gauche). C'est une simple aide à la notation car il est aisément convertible en LEFT en inversant les entrées gauche et droite.

FULL OUTER JOIN renvoie toutes les lignes jointes, plus chaque ligne gauche sans correspondance (étendue par des NULL à droite), plus chaque ligne droite sans correspondance (étendue par des NULL à gauche).

ON condition_jointure

condition_jointure est une expression qui retourne une valeur de type boolean (comme une clause WHERE) qui spécifie les lignes d'une jointure devant correspondre.

USING (colonne_jointure [, ...])

Une clause de la forme USING ( a, b, ... ) est un raccourci pour ON table_gauche.a = table_droite.a AND table_gauche.b = table_droite.b .... De plus, USING implique l'affichage d'une seule paire des colonnes correspondantes dans la sortie de la jointure.

NATURAL

NATURAL est un raccourci pour une liste USING qui mentionne toutes les colonnes de même nom dans les deux tables.

Clause WHERE

La clause WHERE optionnelle a la forme générale

WHERE condition

condition est une expression dont le résultat est de type boolean. Toute ligne qui ne satisfait pas cette condition est éliminée de la sortie. Une ligne satisfait la condition si elle retourne vrai quand les valeurs réelles de la ligne sont substituées à toute référence de variable.

Clause GROUP BY

La clause GROUP BY optionnelle a la forme générale

GROUP BY expression [, ...]

GROUP BY condense en une seule ligne toutes les lignes sélectionnées qui partagent les mêmes valeurs pour les expressions regroupées. expression peut être le nom d'une colonne en entrée, le nom ou le numéro d'une colonne en sortie (élément de la liste SELECT), ou une expression quelconque formée de valeurs de colonnes en entrée. En cas d'ambiguïté, un nom de GROUP BY est interprété comme un nom de colonne en entrée, non en sortie.

Les fonctions d'agrégat, si utilisées, sont calculées pour toutes les lignes composant un groupe, produisant une valeur séparée pour chaque groupe (alors que sans GROUP BY, un agrégat produit une valeur unique calculée pour toutes les lignes sélectionnées). Quand GROUP BY est présent, les expressions du SELECT ne peuvent faire référence qu'à des colonnes groupées, sauf à l'intérieur de fonctions d'agrégat, la valeur de retour d'une colonne non-groupée n'étant pas unique.

Clause HAVING

La clause optionnelle HAVING a la forme générale

HAVING condition

condition est identique à celle spécifiée pour la clause WHERE.

HAVING élimine les lignes groupées qui ne satisfont pas à la condition. HAVING est différent de WHERE : WHERE filtre les lignes individuelles avant l'application de GROUP BY alors que HAVING filtre les lignes groupées créées par GROUP BY. Chaque colonne référencée dans condition doit faire référence sans ambiguïté à une colonne groupée, sauf si la référence apparaît dans une fonction d'agrégat.

Même en l'absence de clause GROUP BY, la présence de HAVING transforme une requête en requête groupée. Cela correspond au comportement d'une requête contenant des fonctions d'agrégats mais pas de clause GROUP BY. Les lignes sélectionnées ne forment qu'un groupe, la liste du SELECT et la clause HAVING ne peuvent donc faire référence qu'à des colonnes à l'intérieur de fonctions d'agrégats. Une telle requête ne produira qu'une seule ligne si la condition HAVING est réalisée, aucune dans le cas contraire.

Clause WINDOW

La clause optionnelle WINDOW a la forme générale

WINDOW nom_window AS ( définition_window ) [, ...]

nom_window est un nom qui peut être référencé par des clauses OVER ou des définitions de window, et définition_window est

[ nom_window_existante ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operateur ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ clause_frame ]

Si un nom_window_existante est spécifié, il doit se référer à une entrée précédente dans la liste WINDOW ; la nouvelle Window copie sa clause de partitionnement de cette entrée, ainsi que sa clause de tri s'il y en a. Dans ce cas, la nouvelle Window ne peut pas spécifier sa propre clause PARTITION BY, et ne peut spécifier de ORDER BY que si la Window copiée n'en a pas. La nouvelle Window utilise toujours sa propre clause frame ; la Window copiée ne doit pas posséder de clause frame.

Les éléments de la liste PARTITION BY sont interprétés à peu près de la même façon que des éléments de Clause GROUP BY, sauf qu'ils sont toujours des expressions simples et jamais le nom ou le numéro d'une colonne en sortie. Une autre différence est que ces expressions peuvent contenir des appels à des fonctions d'aggrégat, ce qui n'est pas autorisé dans une clause GROUP BY classique. Ceci est autorisé ici parce que le windowing se produit après le regroupement et l'aggrégation.

De façon similaire, les éléments de la liste ORDER BY sont interprétés à peu près de la même façon que les éléments d'un Clause ORDER BY, sauf que les expressions sont toujours pris comme de simples expressions et jamais comme le nom ou le numéro d'une colonne en sortie.

La clause clause_frame optionnelle définit la frame window pour les fonctions window qui dépendent de la frame (ce n'est pas le cas de toutes). Elle peut prendre une de ces valeurs :

RANGE UNBOUNDED PRECEDING
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ROWS UNBOUNDED PRECEDING
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Les deux premiers sont équivalents et sont la valeur par défaut : ils déclarent que la frame est constituée de tous les enregistrements du début de la partition jusqu'au dernier pair de l'enregistrement courant dans le tri défini par ORDER BY (ce qui signifie tous les enregistrements si il n'y a pas d'ORDER BY). Les options RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING et ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING sont aussi équivalentes : ils sélectionnent toujours tous les enregistrements de la partition. Finalement, ROWS UNBOUNDED PRECEDING ou son équivalent verbeux ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sélectionne tous les enregistrements de la partition jusqu'à l'enregistrement courant (sans se soucier des doublons). Soyez conscient que cette option peut produire des résultats dépendant de l'implémentation si le tri ORDER BY ne trie pas les enregistrements de façon unique.

L'utilité d'une clause WINDOW est de spécifier le comportement des fonctions window apparaissant dans la clause Liste SELECT ou la clause Clause ORDER BY de la requête. Ces fonctions peuvent référencer les entrées de clauses WINDOW par nom dans leurs clauses OVER. Toutefois, il n'est pas obligatoire qu'une entrée de clause WINDOW soit référencée quelque part ; si elle n'est pas utilisée dans la requête, elle est simplement ignorée. Il est possible d'utiliser des fonctions window sans aucune clause WINDOW puisqu'une fonction window peut spécifier sa propre définition de window directement dans sa clause OVER. Toutefois, la clause WINDOW économise de la saisie quand la même définition window est utilisée pour plus d'une fonction window.

Les fonctions window sont décrites en détail dans Section 3.5, « Fonctions de fenêtrage », Section 4.2.8, « Appels de fonction window » et Section 7.2.4, « Traitement de fonctions Window ».

Liste SELECT

La liste SELECT (entre les mots clés SELECT et FROM) spécifie les expressions qui forment les lignes en sortie de l'instruction SELECT. Il se peut que les expressions fassent référence aux colonnes traitées dans la clause FROM. En fait, en général, elles le font.

Comme pour une table, chaque colonne de sortie d'un SELECT a un nom. Dans un SELECT simple, ce nom est juste utilisé pour donner un titre à la colonne pour l'affichage, mais quand le SELECT est une sous-requête d'une requête plus grande, le nom est vu par la grande requête comme le nom de colonne de la table virtuelle produite par la sous-requête. Pour indiquer le nom à utiliser pour une colonne de sortie, écrivez AS nom_de_sortie après l'expression de la colonne. (Vous pouvez omettre AS seulement si le nom de colonne souhaité n'est pas un mot clé réservé par PostgreSQL™ (voir Annexe C, Mots-clé SQL). Pour vous protéger contre l'ajout futur d'un mot clé, il est recommandé que vous écriviez toujours AS ou que vous mettiez le nom de sortie entre guillemets. Si vous n'indiquez pas de nom de colonne, un nom est choisi automatiquement par PostgreSQL™. Si l'expression de la colonne est une simple référence à une colonne alors le nom choisi est le même que le nom de la colonne ; dans des cas plus complexes, un nom généré qui ressemblera à ?colonneN? est habituellement choisi.

Un nom de colonne de sortie peut être utilisé pour se référer à la valeur de la colonne dans les clauses ORDER BY et GROUP BY, mais pas dans la clauseWHERE ou HAVING ; à cet endroit, vous devez écrire l'expression.

* peut être utilisé, à la place d'une expression, dans la liste de sortie comme raccourci pour toutes les colonnes des lignes sélectionnées. De plus, nom_table.* peut être écrit comme raccourci pour toutes les colonnes de cette table. Dans ces cas, il est impossible de spécifier de nouveaux noms avec AS ; les noms des colonnes de sorties seront les même que ceux de la table.

Clause UNION

La clause UNION a la forme générale :

instruction_select UNION [ ALL ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR SHARE ou FOR UPDATE. (ORDER BY et LIMIT peuvent être attachés à une sous-expression si elle est entourée de parenthèses. Sans parenthèses, ces clauses s'appliquent au résultat de l'UNION, non à l'expression à sa droite.)

L'opérateur UNION calcule l'union ensembliste des lignes renvoyées par les instructions SELECT impliquées. Une ligne est dans l'union de deux ensembles de résultats si elle apparaît dans au moins un des ensembles. Les deux instructions SELECT qui représentent les opérandes directes de l'UNION doivent produire le même nombre de colonnes et les colonnes correspondantes doivent être d'un type de données compatible.

Sauf lorsque l'option ALL est spécifiée, il n'y a pas de doublons dans le résultat de UNION. ALL empêche l'élimination des lignes dupliquées. UNION ALL est donc significativement plus rapide qu'UNION, et sera préféré.

Si une instruction SELECT contient plusieurs opérateurs UNION, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent.

Actuellement, FOR UPDATE et FOR SHARE ne peuvent pas être spécifiés pour un résultat d'UNION ou pour toute entrée d'un UNION.

Clause INTERSECT

La clause INTERSECT a la forme générale :

instruction_select INTERSECT [ ALL ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR UPDATE ou FOR SHARE.

L'opérateur INTERSECT calcule l'intersection des lignes renvoyées par les instructions SELECT impliquées. Une ligne est dans l'intersection des deux ensembles de résultats si elle apparaît dans chacun des deux ensembles.

Le résultat d'INTERSECT ne contient aucune ligne dupliquée sauf si l'option ALL est spécifiée. Dans ce cas, une ligne dupliquée m fois dans la table gauche et n fois dans la table droite apparaît min(m,n) fois dans l'ensemble de résultats.

Si une instruction SELECT contient plusieurs opérateurs INTERSECT, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent. INTERSECT a une priorité supérieur à celle d'UNION. C'est-à-dire que A UNION B INTERSECT C est lu comme A UNION (B INTERSECT C).

Actuellement, FOR UPDATE et FOR SHARE ne peuvent pas être spécifiés pour un résultat d'INTERSECT ou pour une entrée d'INTERSECT.

Clause EXCEPT

La clause EXCEPT a la forme générale :

instruction_select EXCEPT [ ALL ] instruction_select

instruction_select est une instruction SELECT sans clause ORDER BY, LIMIT, FOR UPDATE ou FOR SHARE.

L'opérateur EXCEPT calcule l'ensemble de lignes qui appartiennent au résultat de l'instruction SELECT de gauche mais pas à celui de droite.

Le résultat d'EXCEPT ne contient aucune ligne dupliquée sauf si l'option ALL est spécifiée. Dans ce cas, une ligne dupliquée m fois dans la table gauche et n fois dans la table droite apparaît max(m-n,0) fois dans l'ensemble de résultats.

Si une instruction SELECT contient plusieurs opérateurs EXCEPT, ils sont évalués de gauche à droite, sauf si l'utilisation de parenthèses impose un comportement différent. EXCEPT a la même priorité qu'UNION.

Actuellement, FOR UPDATE et FOR SHARE ne peuvent pas être spécifiés dans un résultat EXCEPT ou pour une entrée d'un EXCEPT.

Clause ORDER BY

La clause optionnelle ORDER BY a la forme générale :

ORDER BY expression [ ASC | DESC | USING opérateur ] [ NULLS { FIRST | LAST } ] [, ...]

La clause ORDER BY impose le tri des lignes de résultat suivant les expressions spécifiées. Si deux lignes sont identiques suivant l'expression la plus à gauche, elles sont comparées avec l'expression suivante et ainsi de suite. Si elles sont identiques pour toutes les expressions de tri, elles sont renvoyées dans un ordre dépendant de l'implantation.

Chaque expression peut être le nom ou le numéro ordinal d'une colonne en sortie (élément de la liste SELECT). Elle peut aussi être une expression arbitraire formée à partir de valeurs des colonnes.

Le numéro ordinal fait référence à la position ordinale (de gauche à droite) de la colonne de résultat. Cette fonctionnalité permet de définir un ordre sur la base d'une colonne dont le nom n'est pas unique. Ce n'est pas particulièrement nécessaire parce qu'il est toujours possible d'affecter un nom à une colonne de résultat avec la clause AS.

Il est aussi possible d'utiliser des expressions quelconques dans la clause ORDER BY, ce qui inclut des colonnes qui n'apparaissent pas dans la liste résultat du SELECT. Ainsi, l'instruction suivante est valide :

SELECT nom FROM distributeurs ORDER BY code;

Il y a toutefois une limitation à cette fonctionnalité. La clause ORDER BY qui s'applique au résultat d'une clause UNION, INTERSECT ou EXCEPT ne peut spécifier qu'un nom ou numéro de colonne en sortie, pas une expression.

Si une expression ORDER BY est un nom qui correspond à la fois à celui d'une colonne résultat et à celui d'une colonne en entrée, ORDER BY l'interprète comme le nom de la colonne résultat. Ce comportement est à l'opposé de celui de GROUP BY dans la même situation. Cette incohérence est imposée par la compatibilité avec le standard SQL.

Un mot clé ASC (ascendant) ou DESC (descendant) peut être ajouté après toute expression de la clause ORDER BY. ASC est la valeur utilisée par défaut. Un nom d'opérateur d'ordre spécifique peut également être fourni dans la clause USING. Un opérateur de tri doit être un membre plus-petit-que ou plus-grand-que de certaines familles d'opérateur B-tree. ASC est habituellement équivalent à USING < et DESC à USING >. Le créateur d'un type de données utilisateur peut définir à sa guise le tri par défaut qui peut alors correspondre à des opérateurs de nom différent.

Si NULLS LAST est indiqué, les valeurs NULL sont listées après toutes les valeurs non NULL  si NULLS FIRST est indiqué, les valeurs NULL apparaissent avant toutes les valeurs non NULL. Si aucune des deux n'est présente, le comportement par défaut est NULLS LAST quand ASC est utilisé (de façon explicite ou non) et NULLS FIRST quand DESC est utilisé (donc la valeur par défaut est d'agir comme si les NULL étaient plus grands que les non NULL). Quand USING est indiqué, le tri des NULL par défaut dépend du fait que l'opérateur est un plus-petit-que ou un plus-grand-que.

Notez que les options de tri s'appliquent seulement à l'expression qu'elles suivent. Par exemple, ORDER BY x, y DESC ne signifie pas la même chose que ORDER BY x DESC, y DESC.

Les données de chaînes de caractères sont triées suivant l'ordre spécifique à la locale, ordre établi au moment de la création de la base de données.

Clause DISTINCT

Si DISTINCT est spécifié, toutes les lignes dupliquées sont supprimées de l'ensemble de résultats (une ligne est conservée pour chaque groupe de lignes dupliquées). ALL spécifie le contraire : toutes les lignes sont conservées ; c'est la valeur par défaut.

DISTINCT ON ( expression [, ...] ) ne conserve que la première ligne de chaque ensemble pour lesquels il y a identité des expressions. Les expressions DISTINCT ON sont interprétées en utilisant les mêmes règles que pour ORDER BY (voir ci-dessus). La « première ligne » de chaque ensemble n'est pas prévisible sauf si ORDER BY est utilisé. Par exemple :

  SELECT DISTINCT ON (emplacement) emplacement, heure, rapport
  FROM rapports_meteo
  ORDER BY emplacement, heure DESC;

récupère le rapport météo le plus récent de chaque emplacement. Si ORDER BY n'est pas utilisé pour forcer l'ordre descendant des valeurs heure de chaque emplacement, le rapport est ordonné suivant des temps aléatoires.

Le(s) expression(s) DISTINCT ON doi(ven)t correspondre à l'ordre des expression ORDER BY. La clause ORDER BY contient des expressions supplémentaires qui déterminent la précédence désirée des lignes à l'intérieur de chaque groupe DISTINCT ON.

Clause LIMIT

La clause LIMIT est constituée de deux sous-clauses indépendantes :

LIMIT { nombre | ALL }
OFFSET début

nombre spécifie le nombre maximum de lignes à renvoyer alors que début spécifie le nombre de lignes à passer avant de commencer à renvoyer des lignes. Lorsque les deux clauses sont spécifiées, début lignes sont passées avant de commencer à compter les nombre lignes à renvoyer.

Si l'expression de compte est évaluée à NULL, il est traité comme LIMIT ALL, c'est-à-dire sans limite. Si début est évalué à NULL, il est traité comme OFFSET 0.

SQL:2008 a introduit une sytaxe différente pour faire la même chose. PostgreSQL supporte aussi cette syntaxe.

OFFSET début { ROW | ROWS }
FETCH { FIRST | NEXT } [ compte ] { ROW | ROWS } ONLY

Les deux clauses sont optionnelles. Si elles sont présentes, la clause OFFSET doit précéder la clause FETCH. ROW et ROWS ainsi que FIRST et NEXT sont du bruit et n'ont pas d'influence sur les effets de ces clauses. Dans cette syntaxe, quand on utilise des expressions autres que des simples constantes pour début ou compte, des parenthèses seront nécessaires dans la plupart des cas. Si compte est omis dans FETCH, il prend 1 comme valeur par défaut.

Avec LIMIT, utiliser la clause ORDER BY permet de contraindre l'ordre des lignes de résultat. Dans le cas contraire, le sous-ensemble obtenu n'est pas prévisible -- rien ne permet de savoir à quel ordre correspondent les lignes retournées. Celui-ci ne sera pas connu tant qu'ORDER BY n'aura pas été précisé.

Lors de la génération d'un plan de requête, le planificateur tient compte de LIMIT. Le risque est donc grand d'obtenir des plans qui diffèrent (ordres des lignes différents) suivant les valeurs utilisées pour LIMIT et OFFSET. Ainsi, sélectionner des sous-ensembles différents d'un résultat à partir de valeurs différentes de LIMIT/OFFSET aboutit à des résultats incohérents à moins d'avoir figé l'ordre des lignes à l'aide de la clause ORDER BY. Ce n'est pas un bogue, mais une conséquence du fait que SQL n'assure pas l'ordre de présentation des résultats sans utilisation d'une clause ORDER BY.

Il est même possible pour des exécutions répétées de la même requête LIMIT de renvoyer différents sous-ensembles des lignes d'une table s'il n'y a pas de clause ORDER BY pour forcer la sélection d'un sous-ensemble déterministe. Encore une fois, ce n'est pas un bogue ; le déterminisme des résultats n'est tout simplement pas garanti dans un tel cas.

Clause FOR UPDATE/FOR SHARE

La clause FOR UPDATE a la forme :

FOR UPDATE [ OF nom_table [, ...] ] [ NOWAIT ]

La clause liée, FOR SHARE, a la forme :

FOR SHARE [ OF nom_table [, ...] ] [ NOWAIT ]

FOR UPDATE verrouille pour modification les lignes récupérées par l'instruction SELECT. Cela les empêche d'être modifiées ou supprimées par les autres transactions jusqu'à la fin de la transaction en cours. Les autres transactions qui tentent des UPDATE, DELETE ou SELECT FOR UPDATE sur ces lignes sont bloquées jusqu'à la fin de la transaction courante. De plus, si un UPDATE, DELETE ou SELECT FOR UPDATE a déjà verrouillé une ligne ou un ensemble de lignes à partir d'une autre transaction, SELECT FOR UPDATE attend la fin de l'autre transaction puis verrouille et renvoie la ligne modifiée (ou aucune ligne si elle a été supprimée). Pour plus d'informations, voir Chapitre 13, Contrôle d'accès simultané.

Pour éviter à l'opération d'attendre la validation des autres transactions, on utilise l'option NOWAIT. SELECT FOR UPDATE NOWAIT rapporte une erreur si une ligne sélectionnée ne peut pas être verrouillée immédiatement. Il n'y a pas d'attente. NOWAIT s'applique seulement au(x) verrou(x) niveau ligne -- le verrou niveau table ROW SHARE est toujours pris de façon ordinaire (voir Chapitre 13, Contrôle d'accès simultané). L'option NOWAIT de LOCK peut toujours être utilisée pour acquérir le verrou niveau table sans attendre.

FOR SHARE a un comportement similaire. La différence se situe dans le type de verrou acquis. Contrairement à FOR UPDATE qui pose un verrou exclusif, FOR SHARE pose un verrou partagé sur chaque ligne récupérée. Un verrou partagée bloque les instructions UPDATE, DELETE ou SELECT FOR UPDATE des transaction concurrentes accédant à ces lignes, mais il n'interdit pas les SELECT FOR SHARE.

Si des tables particulières sont nommées dans les clauses FOR UPDATE et FOR SHARE, alors seules les lignes provenant de ces tables sont verrouillées ; toute autre table utilisée dans le SELECT est simplement lue. Une clause FOR UPDATE ou FOR SHARE sans liste de tables affecte toute les tables utilisées dans la commande. Si FOR UPDATE ou FOR SHARE est appliquée à une vue ou à une sous-requête, cela affecte toutes les tables utilisées dans la vue ou la sous-requête. Néanmoins, FOR UPDATE/FOR SHARE ne s'appliquent pas aux requêtes WITH référencées par la clé primaire. Si vous voulez qu'un verrouillage de lignes intervienne dans une requête WITH, spécifiez FOR UPDATE ou FOR SHARE à l'intérieur de la requête WITH.

Plusieurs clauses FOR UPDATE et FOR SHARE peuvent être données si il est nécessaire de spécifier différents comportements de verrouillage pour différentes tables. Si la même table est mentionné (ou affectée implicitement) par les clauses FOR UPDATE et FOR SHARE, alors elle est traitée comme un simple FOR UPDATE. De façon similaire, une table est traitée avec NOWAIT si c'est spécifiée sur au moins une des clauses qui l'affectent.

FOR UPDATE et FOR SHARE nécessitent que chaque ligne retournée soit clairement identifiable par une ligne individuelle d'une table ; ces options ne peuvent, par exemple, pas être utilisées avec des fonctions d'aggrégats.

[Attention]

Attention

Évitez de verrouiller une ligne puis de la modifier après un nouveau point de sauvegarde ou après un bloc d'exception PL/pgSQL. L'annulation suivante pourrait causer la perte du verrou. Par exemple :

BEGIN;
SELECT * FROM ma_table WHERE cle = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE ma_table SET ... WHERE cle = 1;
ROLLBACK TO s;

Après le ROLLBACK, la ligne est réellement déverrouillée au lieu de retourner à son état avant le point de sauvegarde. Ceci peut arriver si une ligne verrouillée dans la transaction en cours est mise à jour ou supprimée, ou si un verrou partagé est passé en verrou exclusif : dans tous ces cas, l'état précédent du verrou est oublié. Si la transation est ensuite annulée à un état entre la commande de verrou initiale et la modification qui a suivi, la ligne n'apparaîtra plus verrouillée. Ceci est une déficience de l'implémentation qui sera corrigée dans une prochaine version de PostgreSQL™.

[Attention]

Attention

Il est possible qu'une commande SELECT qui utilise simultanément les clauses LIMIT et FOR UPDATE/SHARE retourne moins de lignes que le nombre spécifié par LIMIT. En effet, LIMIT est appliqué le premier. La commande sélectionne le nombre de lignes spécifiées, mais peut être bloquée en essayant d'obtenir un verrou sur l'une ou l'autre de ces lignes. Lorsque le SELECT est débloqué, la ligne peut avoir été supprimée ou modifiée de telle sorte qu'elle ne remplisse plus les conditions imposées par la clause WHERE. Dans ce cas, elle n'est pas retournée.

[Attention]

Attention

De façon similaire, il est possible qu'une commande SELECT utilisant ORDER BY et FOR UPDATE/SHARE renvoient les lignes dans un ordre différent. Ceci est dû au fait que ORDER BY est d'abord appliqué. La commande trie le résultat mais pourrait ensuite bloquer en essayant d'obtenir un verrou sur une ou plusieurs lignes. Une fois que le SELECT est débloqué, une des colonnes ordonnées peut avoir été modifiée et être renvoyée dans un ordre différent. Un contournement de ce problème revient à réaliser un SELECT ... FOR UPDATE/SHARE et ensuite un SELECT ... ORDER BY.

Commande TABLE

La commande

TABLE nom

est complètement équivalente à

SELECT * FROM nom

Elle peut être utilisée comme commande principale d'une requête, ou bien comme une variante syntaxique permettant de gagner de la place dans des parties de requêtes complexes.

Exemples

Joindre la table films avec la table distributeurs :

SELECT f.titre, f.did, d.nom, f.date_prod, f.genre
    FROM distributeurs d, films f
    WHERE f.did = d.did

       titre       | did |     nom      | date_prod  |   genre
-------------------+-----+--------------+------------+------------
 The Third Man     | 101 | British Lion | 1949-12-23 | Drame
 The African Queen | 101 | British Lion | 1951-08-11 | Romantique
 ...

Additionner la colonne longueur de tous les films, grouper les résultats par genre :

SELECT genre, sum(longueur) AS total FROM films GROUP BY genre;

   genre    | total
------------+-------
 Action     | 07:34
 Comédie    | 02:58
 Drame      | 14:28
 Musical    | 06:42
 Romantique | 04:38

Additionner la colonne longueur de tous les films, grouper les résultats par genre et afficher les groupes dont les totaux font moins de cinq heures :

SELECT genre, sum(longueur) AS total
    FROM films
    GROUP BY genre
    HAVING sum(longueur) < interval '5 hours';

   genre    | total
------------+-------
 Comedie    | 02:58
 Romantique | 04:38

Les deux exemples suivants représentent des façons identiques de trier les résultats individuels en fonction du contenu de la deuxième colonne (nom) :

SELECT * FROM distributeurs ORDER BY nom;
SELECT * FROM distributeurs ORDER BY 2;

 did |       nom
-----+------------------
 109 | 20th Century Fox
 110 | Bavaria Atelier
 101 | British Lion
 107 | Columbia
 102 | Jean Luc Godard
 113 | Luso films
 104 | Mosfilm
 103 | Paramount
 106 | Toho
 105 | United Artists
 111 | Walt Disney
 112 | Warner Bros.
 108 | Westward

L'exemple suivant présente l'union des tables distributeurs et acteurs, restreignant les résultats à ceux de chaque table dont la première lettre est un W. Le mot clé ALL est omis, ce qui permet de n'afficher que les lignes distinctes.

distributeurs:               acteurs:
 did |     nom               id |     nom
-----+--------------        ----+----------------
 108 | Westward               1 | Woody Allen
 111 | Walt Disney            2 | Warren Beatty
 112 | Warner Bros.           3 | Walter Matthau
 ...                         ...

SELECT distributeurs.nom
    FROM distributeurs
    WHERE distributeurs.nom LIKE 'W%'
UNION
SELECT actors.nom
    FROM acteurs
    WHERE acteurs.nom LIKE 'W%';

      nom
----------------
 Walt Disney
 Walter Matthau
 Warner Bros.
 Warren Beatty
 Westward
 Woody Allen

L'exemple suivant présente l'utilisation d'une fonction dans la clause FROM, avec et sans liste de définition de colonnes :

CREATE FUNCTION distributeurs(int) RETURNS SETOF distributeurs AS $$
    SELECT * FROM distributeurs WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributeurs(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributeurs_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributeurs WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributeurs_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

Cet exemple montre comment utiliser une clause WITH simple:

WITH t AS (
    SELECT random() as x FROM generate_series(1, 3)
  )
SELECT * FROM t
UNION ALL
SELECT * FROM t

         x          
--------------------
  0.534150459803641
  0.520092216785997
 0.0735620250925422
  0.534150459803641
  0.520092216785997
 0.0735620250925422

Notez que la requête WITH n'a été évaluée qu'une seule fois, ce qui fait qu'on a deux jeux contenant les mêmes trois valeurs.

Cet exemple utilise WITH RECURSIVE pour trouver tous les subordonnés (directs ou indirects) de l'employée Marie, et leur niveau de subordination, à partir d'une table qui ne donne que les subordonnés directs :

WITH RECURSIVE recursion_employes(distance, nom_employe, nom_manager) AS (
    SELECT 1, nom_employe, nom_manager
    FROM employe
    WHERE nom_manager = 'Marie'
  UNION ALL
    SELECT er.distance + 1, e.nom_employe, e.nom_manager
    FROM recursion_employes er, employe e
    WHERE er.nom_employe = e.nom_manager
  )
SELECT distance, nom_employe FROM recursion_employes;

Notez la forme typique des requêtes récursives : une condition initiale, suivie par UNION, suivis par la partie récursive de la requête. Assurez-vous que la partie récursive de la requête finira par ne plus retourner d'enregistrement, sinon la requête bouclera indéfiniment (Voir Section 7.8, « Requêtes WITH (Common Table Expressions) » pour plus d'exemples).

Compatibilité

L'instruction SELECT est évidemment compatible avec le standard SQL. Mais il y a des extensions et quelques fonctionnalités manquantes.

Clauses FROM omises

PostgreSQL™ autorise l'omission de la clause FROM. Cela permet par exemple de calculer le résultat d'expressions simples :

SELECT 2+2;

 ?column?
----------
        4

D'autres bases de données SQL interdisent ce comportement, sauf à introduire une table virtuelle d'une seule ligne sur laquelle exécuter la commande SELECT.

S'il n'y a pas de clause FROM, la requête ne peut pas référencer les tables de la base de données. La requête suivante est, ainsi, invalide :

SELECT distributors.* WHERE distributors.name = 'Westward';

Les versions antérieures à PostgreSQL™ 8.1 acceptaient les requêtes de cette forme en ajoutant une entrée implicite à la clause FROM pour chaque table référencée. Ce n'est plus le comportement par défaut, d'une part parce qu'il n'est pas compatible avec le standard SQL et, d'autre part, parce que considéré par beaucoup comme générateur d'erreurs. Pour des raisons de compatibilité avec les applications utilisant ce comportement, la variable de configuration add_missing_from peut toujours être activée.

Omettre le mot clé AS

Dans le standard SQL, le mot clé AS peut être omis devant une colonne de sortie à partir du moment où le nouveau nom de colonne est un nom valide de colonne (c'est-à-dire, différent d'un mot clé réservé). PostgreSQL™ est légèrement plus restrictif : AS est nécessaire si le nouveau nom de colonne est un mot clé quel qu'il soit, réservé ou non. Il est recommandé d'utiliser AS ou des colonnes de sortie entourées de guillemets, pour éviter tout risque de conflit en cas d'ajout futur de mot clé.

Dans les éléments de FROM, le standard et PostgreSQL™ permettent que AS soit omis avant un alias qui n'est pas un mot clé réservé. Mais c'est peu pratique pour les noms de colonnes, à causes d'ambiguïtés syntaxiques.

ONLY et l'héritage

Le standard SQL impose des parenthèses autour du nom de table après la clause ONLY, comme dans SELECT * FROM ONLY (tab1), ONLY (tab2) WHERE .... PostgreSQL™ considère les parenthèses comme étant optionnelles.

PostgreSQL™ autorise une * en fin pour indiquer explicitement le comportement opposé de la clause ONLY (donc inclure les tables filles). Le standard ne le permet pas.

(Ces points s'appliquent de la même façon à toutes les commandes SQL supportant l'option ONLY.)

Espace logique disponible pour GROUP BY et ORDER BY

Dans le standard SQL-92, une clause ORDER BY ne peut utiliser que les noms ou numéros des colonnes en sortie, une clause GROUP BY que des expressions fondées sur les noms de colonnes en entrée. PostgreSQL™ va plus loin, puisqu'il autorise chacune de ces clauses à utiliser également l'autre possibilité. En cas d'ambiguïté, c'est l'interprétation du standard qui prévaut. PostgreSQL™ autorise aussi l'utilisation d'expressions quelconques dans les deux clauses. Les noms apparaissant dans ces expressions sont toujours considérés comme nom de colonne en entrée, pas en tant que nom de colonne du résultat.

SQL:1999 et suivant utilisent une définition légèrement différente, pas totalement compatible avec le SQL-92. Néanmoins, dans la plupart des cas, PostgreSQL™ interprète une expression ORDER BY ou GROUP BY en suivant la norme SQL:1999.

Restrictions sur la clause WINDOW

Le standard SQL fournit des options additionnelles pour la clause_frame des window. PostgreSQL™ ne supporte à ce jour que les options mentionnées précédemment.

LIMIT et OFFSET

Les clauses LIMIT et OFFSET sont une syntaxe spécifique à PostgreSQL™, aussi utilisée dans MySQL™. La norme SQL:2008 a introduit les clauses OFFSET ... FETCH {FIRST|NEXT}... pour la même fonctionnalité, comme montré plus haut dans Clause LIMIT, et cette syntaxe est aussi utilisée par IBM DB2™. (Les applications écrites pour Oracle™ contournent fréquemment le problème par l'utilisation de la colonne autogénérée rownum pour obtenir les effets de ces clauses. Ce n'est pas disponible sous PostgreSQL,)

Clauses non standard

La clause DISTINCT ON n'est pas définie dans le standard SQL.