Sélectionner (SQL)

L' instruction SQL SELECT renvoie un ensemble de résultats de lignes provenant d'une ou de plusieurs tables .

Une instruction SELECT récupère zéro ou plusieurs lignes d'une ou plusieurs tables ou vues de base de données . Dans la plupart des applications, il s'agit de la commande de langage de manipulation de donnéesSELECT (DML) la plus couramment utilisée . SQL étant un langage de programmation déclaratif , les requêtes spécifient un ensemble de résultats, mais ne précisent pas comment le calculer. La base de données traduit la requête en un « plan de requête » qui peut varier selon les exécutions, les versions de base de données et le logiciel de base de données. Cette fonctionnalité est appelée « optimiseur de requête » car elle est chargée de trouver le meilleur plan d'exécution possible pour la requête, dans les limites des contraintes applicables. SELECT

L'instruction SELECT comporte de nombreuses clauses facultatives :

• SELECTlist est la liste des colonnes ou des expressions SQL à renvoyer par la requête. Il s'agit approximativement de l' opération de projection d'algèbre relationnelle .
• ASfournit éventuellement un alias pour chaque colonne ou expression de la SELECTliste. Il s'agit de l' opération de renommage de l'algèbre relationnelle .
• FROMspécifie à partir de quelle table obtenir les données.
• WHEREspécifie les lignes à récupérer. Il s'agit approximativement de l' opération de sélection d'algèbre relationnelle .
• GROUP BYregroupe les lignes partageant une propriété afin qu'une fonction d'agrégation puisse être appliquée à chaque groupe.
• HAVINGsélectionne parmi les groupes définis par la clause GROUP BY.
• ORDER BYspécifie comment ordonner les lignes renvoyées.

SELECTest l'opération la plus courante dans SQL, appelée « la requête ». SELECTrécupère des données à partir d'une ou plusieurs tables ou expressions. SELECTLes instructions standard n'ont aucun effet persistant sur la base de données. Certaines implémentations non standard de SELECTpeuvent avoir des effets persistants, comme la SELECT INTOsyntaxe fournie dans certaines bases de données.

Les requêtes permettent à l'utilisateur de décrire les données souhaitées, laissant le système de gestion de base de données (SGBD) effectuer la planification , l'optimisation et l'exécution des opérations physiques nécessaires pour produire ce résultat comme il le souhaite.

Une requête inclut une liste de colonnes à inclure dans le résultat final, généralement immédiatement après le SELECTmot clé. Un astérisque (" *") peut être utilisé pour spécifier que la requête doit renvoyer toutes les colonnes de toutes les tables interrogées. SELECTest l'instruction la plus complexe de SQL, avec des mots clés et des clauses optionnels qui incluent :

• La FROMclause qui indique les tables à partir desquelles récupérer les données. La FROMclause peut inclure JOINdes sous-clauses facultatives pour spécifier les règles de jointure des tables.
• La WHEREclause inclut un prédicat de comparaison, qui limite les lignes renvoyées par la requête. La WHEREclause élimine toutes les lignes de l'ensemble de résultats où le prédicat de comparaison n'est pas évalué comme étant vrai.
• La GROUP BYclause projette les lignes ayant des valeurs communes dans un ensemble plus petit de lignes. GROUP BYElle est souvent utilisée en conjonction avec les fonctions d'agrégation SQL ou pour éliminer les lignes en double d'un ensemble de résultats. La WHEREclause est appliquée avant la GROUP BYclause.
• La HAVINGclause inclut un prédicat utilisé pour filtrer les lignes résultant de la GROUP BYclause. Comme il agit sur les résultats de la GROUP BYclause, des fonctions d'agrégation peuvent être utilisées dans le HAVINGprédicat de la clause.
• La ORDER BYclause identifie les colonnes à utiliser pour trier les données résultantes et dans quel sens les trier (croissant ou décroissant). Sans ORDER BYclause, l'ordre des lignes renvoyées par une requête SQL n'est pas défini.
• Le DISTINCTmot clé élimine les données en double.

L'exemple de SELECTrequête suivant renvoie une liste de livres coûteux. La requête récupère toutes les lignes de la table Book dans lesquelles la colonne price contient une valeur supérieure à 100,00. Le résultat est trié par ordre croissant par title . L'astérisque (*) dans la liste de sélection indique que toutes les colonnes de la table Book doivent être incluses dans le jeu de résultats.

SÉLECTIONNEZ * DANS LIVRE OÙ prix > 100 . 00 COMMANDER PAR titre ;

L'exemple ci-dessous illustre une requête de plusieurs tables, de regroupement et d'agrégation, en renvoyant une liste de livres et le nombre d'auteurs associés à chaque livre.

SÉLECTIONNEZ Livre . titre AS Titre , comptez ( * ) AS Auteurs DE Livre JOIN Livre_auteur SUR Livre . isbn = Livre_auteur . isbn GROUP BY Livre . titre ;

Un exemple de sortie pourrait ressembler à ce qui suit :

Titre Auteurs ---------------------- ------- Exemples et guide SQL 4 La joie de SQL 1 Introduction à SQL 2 Les pièges de SQL 1 

Sous la condition que isbn soit le seul nom de colonne commun aux deux tables et qu'une colonne nommée title n'existe que dans la table Book , on pourrait réécrire la requête ci-dessus sous la forme suivante :

SÉLECTIONNEZ titre , comptez ( * ) AS Auteurs DE Livre NATUREL JOIN Livre_auteur GROUPE PAR titre ;

Cependant, de nombreux fournisseurs ne prennent pas en charge cette approche ou exigent certaines conventions de dénomination de colonnes pour que les jointures naturelles fonctionnent efficacement.

SQL inclut des opérateurs et des fonctions pour calculer des valeurs sur des valeurs stockées. SQL permet d'utiliser des expressions dans la liste de sélection pour projeter des données, comme dans l'exemple suivant, qui renvoie une liste de livres qui coûtent plus de 100,00 $ avec une colonne sales_tax supplémentaire contenant un chiffre de taxe de vente calculé à 6 % du prix .

SÉLECTIONNEZ isbn , titre , prix , prix * 0 . 06 AS sales_tax FROM Livre OÙ prix > 100 . 00 ORDER BY titre ;

Les requêtes peuvent être imbriquées de manière à ce que les résultats d'une requête puissent être utilisés dans une autre requête via un opérateur relationnel ou une fonction d'agrégation. Une requête imbriquée est également appelée sous-requête . Alors que les jointures et autres opérations de table fournissent des alternatives informatiques supérieures (c'est-à-dire plus rapides) dans de nombreux cas (tout dépend de l'implémentation), l'utilisation de sous-requêtes introduit une hiérarchie dans l'exécution qui peut être utile ou nécessaire. Dans l'exemple suivant, la fonction d'agrégation AVGreçoit en entrée le résultat d'une sous-requête :

SÉLECTIONNEZ isbn , titre , prix DANS Livre OÙ prix < ( SÉLECTIONNEZ AVG ( prix ) DANS Livre ) COMMANDEZ PAR titre ;

Une sous-requête peut utiliser des valeurs de la requête externe, auquel cas elle est appelée sous-requête corrélée .

Depuis 1999, la norme SQL autorise les clauses WITH, c'est-à-dire des sous-requêtes nommées souvent appelées expressions de table communes (nommées et conçues d'après l'implémentation IBM DB2 version 2 ; Oracle appelle ces sous-requêtes factorisation ). Les CTE peuvent également être récursives en se référant à elles-mêmes ; le mécanisme résultant permet des parcours d'arbres ou de graphes (lorsqu'ils sont représentés sous forme de relations), et plus généralement des calculs à point fixe .

Une table dérivée est une sous-requête dans une clause FROM. Essentiellement, la table dérivée est une sous-requête qui peut être sélectionnée ou à laquelle on peut joindre des éléments. La fonctionnalité de table dérivée permet à l'utilisateur de référencer la sous-requête en tant que table. La table dérivée est également appelée vue en ligne ou liste de sélection dans la liste .

Dans l'exemple suivant, l'instruction SQL implique une jointure de la table initiale Books à la table dérivée « Sales ». Cette table dérivée capture les informations sur les ventes de livres associées en utilisant l'ISBN pour joindre la table Books. En conséquence, la table dérivée fournit l'ensemble de résultats avec des colonnes supplémentaires (le nombre d'articles vendus et la société qui a vendu les livres) :

SÉLECTIONNEZ b . isbn , b . titre , b . prix , ventes . articles_vendus , ventes . société_nm À PARTIR DU LIVRE b JOIN ( SÉLECTIONNEZ SUM ( Articles_vendus ) Articles_vendus , Société_Nm , ISBN À PARTIR DU LIVRE_VENTES GROUP BY Société_Nm , ISBN ) ventes SUR ventes . isbn = b . isbn

Étant donné une table T, la requête affichera tous les éléments de toutes les lignes de la table. SELECT*FROMT

Avec la même table, la requête affichera les éléments de la colonne C1 de toutes les lignes de la table. Cela est similaire à une projection en algèbre relationnelle , sauf que dans le cas général, le résultat peut contenir des lignes en double. Ceci est également connu sous le nom de partition verticale dans certains termes de base de données, limitant la sortie de la requête à l'affichage uniquement des champs ou des colonnes spécifiés. SELECTC1FROMT

Avec la même table, la requête affichera tous les éléments de toutes les lignes dont la valeur de la colonne C1 est « 1 » – en termes d'algèbre relationnelle , une sélection sera effectuée, en raison de la clause WHERE. Ceci est également connu sous le nom de partition horizontale, limitant les lignes générées par une requête selon des conditions spécifiées. SELECT*FROMTWHEREC1=1

Avec plus d'une table, le résultat sera constitué de toutes les combinaisons de lignes. Ainsi, si deux tables sont T1 et T2, le résultat sera toutes les combinaisons de lignes T1 avec toutes les lignes T2. Par exemple, si T1 a 3 lignes et T2 a 5 lignes, alors 15 lignes seront obtenues. SELECT*FROMT1,T2

Bien que cela ne soit pas standard, la plupart des SGBD permettent d'utiliser une clause select sans table en faisant semblant d'utiliser une table imaginaire avec une seule ligne. Cette méthode est principalement utilisée pour effectuer des calculs où une table n'est pas nécessaire.

La clause SELECT spécifie une liste de propriétés (colonnes) par nom ou par le caractère générique (« * ») pour signifier « toutes les propriétés ».

Il est souvent pratique d'indiquer un nombre maximal de lignes à renvoyer. Cela peut être utilisé à des fins de test ou pour éviter de consommer des ressources excessives si la requête renvoie plus d'informations que prévu. L'approche à adopter varie souvent selon le fournisseur.

Dans ISO SQL:2003 , les ensembles de résultats peuvent être limités en utilisant

• curseurs , ou
• en ajoutant une fonction de fenêtre SQL à l'instruction SELECT

La norme ISO SQL:2008 a introduit la FETCH FIRSTclause.

Selon la documentation de PostgreSQL v.9, une fonction de fenêtre SQL « effectue un calcul sur un ensemble de lignes de table qui sont en quelque sorte liées à la ligne actuelle », d'une manière similaire aux fonctions d'agrégation. les fonctions de fenêtre de traitement de signal . Un appel de fonction de fenêtre contient toujours une clause OVER .

ROW_NUMBER() OVERpeut être utilisé pour un tableau simple sur les lignes renvoyées, par exemple pour ne pas renvoyer plus de dix lignes :

SÉLECTIONNEZ * DE ( SÉLECTIONNEZ NUMÉRO_RANGÉE () SUR ( ORDER BY clé_de_tri ASC ) COMME numéro_ligne , colonnes DE nom_table ) COMME foo OÙ numéro_ligne <= 10

ROW_NUMBER peut être non déterministe : si sort_key n'est pas unique, à chaque exécution de la requête, il est possible d'obtenir des numéros de ligne différents attribués à toutes les lignes où sort_key est le même. Lorsque sort_key est unique, chaque ligne obtiendra toujours un numéro de ligne unique.

La RANK() OVERfonction de fenêtre agit comme ROW_NUMBER, mais peut renvoyer plus ou moins de n lignes en cas de conditions d'égalité, par exemple pour renvoyer les 10 personnes les plus jeunes :

SÉLECTIONNEZ * DE ( SÉLECTIONNEZ RANG ( ) SUR ( ORDRE PAR âge ASC ) AS classement , person_id , person_name , âge DE personne ) AS foo OÙ classement <= 10

Le code ci-dessus peut renvoyer plus de dix lignes, par exemple s'il y a deux personnes du même âge, il peut renvoyer onze lignes.

Depuis la norme ISO SQL:2008, les limites de résultats peuvent être spécifiées comme dans l'exemple suivant à l'aide de la FETCH FIRSTclause.

SÉLECTIONNEZ * DANS T RÉCUPÉRER LES 10 PREMIÈRES LIGNES UNIQUEMENT

Cette clause est actuellement prise en charge par CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c et Mimer SQL .

Microsoft SQL Server 2008 et versions ultérieures prennent en charge FETCH FIRST, mais cette fonction est considérée comme faisant partie de la ORDER BYclause. Les clauses ORDER BY, OFFSETet FETCH FIRSTsont toutes requises pour cette utilisation.

SÉLECTIONNEZ * DANS T ORDER BY une colonne DESC OFFSET 0 LIGNES RÉCUPÉRER LES 10 PREMIÈRES LIGNES UNIQUEMENT

Certains SGBD proposent une syntaxe non standard, soit à la place, soit en plus de la syntaxe standard SQL. Vous trouverez ci-dessous les variantes de la requête de limite simple pour différents SGBD :

La pagination des lignes est une approche utilisée pour limiter et afficher uniquement une partie des données totales d'une requête dans la base de données. Au lieu d'afficher des centaines ou des milliers de lignes en même temps, le serveur ne demande qu'une seule page (un ensemble limité de lignes, par exemple seulement 10 lignes), et l'utilisateur commence à naviguer en demandant la page suivante, puis la suivante, et ainsi de suite. C'est très utile, en particulier dans les systèmes Web, où il n'y a pas de connexion dédiée entre le client et le serveur, de sorte que le client n'a pas à attendre pour lire et afficher toutes les lignes du serveur.

• {rows}= Nombre de lignes dans une page
• {page_number}= Numéro de la page en cours
• {begin_base_0}= Numéro de la ligne - 1 où la page commence = (page_number-1) * lignes

1. Sélectionner toutes les lignes de la base de données
2. Lire toutes les lignes mais envoyer à l'affichage uniquement lorsque le numéro de ligne des lignes lues est compris entre {begin_base_0 + 1}et{begin_base_0 + rows}

Sélectionnez * dans { table }, triez par { unique_key }

1. Sélectionnez toutes les lignes du début du tableau jusqu'à la dernière ligne à afficher ( {begin_base_0 + rows})
2. Lire les {begin_base_0 + rows}lignes mais envoyer à l'affichage uniquement lorsque le row_number des lignes lues est supérieur à{begin_base_0}

1. Sélectionnez uniquement {rows}les lignes à partir de la ligne suivante à afficher ( {begin_base_0 + 1})
2. Lire et envoyer pour afficher toutes les lignes lues dans la base de données

1. Sélectionnez uniquement {rows}les lignes avec filtre :
   1. Première page : sélectionnez uniquement les premières {rows}lignes, selon le type de base de données
   2. Page suivante : sélectionnez uniquement les premières {rows}lignes, selon le type de base de données, où le {unique_key}est supérieur à {last_val}(la valeur du de {unique_key}la dernière ligne de la page en cours)
   3. Page précédente : trier les données dans l'ordre inverse, sélectionner uniquement les premières {rows}lignes, où le {unique_key}est inférieur à {first_val}(la valeur du de {unique_key}la première ligne de la page actuelle) et trier le résultat dans l'ordre correct
2. Lire et envoyer pour afficher toutes les lignes lues dans la base de données

Certaines bases de données fournissent une syntaxe spécialisée pour les données hiérarchiques .

Une fonction de fenêtre dans SQL:2003 est une fonction d'agrégation appliquée à une partition de l'ensemble de résultats.

Par exemple,

somme ( population ) SUR ( PARTITION PAR ville )

calcule la somme des populations de toutes les lignes ayant la même valeur de ville que la ligne actuelle.

Les partitions sont spécifiées à l'aide de la clause OVER qui modifie l'agrégat. Syntaxe :

< OVER_CLAUSE > :: = SUR ( [ PARTITION PAR < expr > , ... ] [ COMMANDER PAR < expression > ] ) 

La clause OVER permet de partitionner et d'ordonner l'ensemble de résultats. L'ordre est utilisé pour les fonctions relatives à l'ordre, telles que row_number.

Le traitement d'une instruction SELECT selon ANSI SQL serait le suivant :

L'implémentation des fonctions de fenêtre par les fournisseurs de bases de données relationnelles et les moteurs SQL diffère énormément. La plupart des bases de données prennent en charge au moins certaines variantes des fonctions de fenêtre. Cependant, lorsque nous y regardons de plus près, il devient clair que la plupart des fournisseurs n'implémentent qu'un sous-ensemble de la norme. Prenons l'exemple de la puissante clause RANGE. Seuls Oracle, DB2, Spark/Hive et Google Big Query implémentent entièrement cette fonctionnalité. Plus récemment, les fournisseurs ont ajouté de nouvelles extensions à la norme, par exemple des fonctions d'agrégation de tableaux. Celles-ci sont particulièrement utiles dans le contexte de l'exécution de SQL sur un système de fichiers distribué (Hadoop, Spark, Google BigQuery) où les garanties de colocalisation des données sont plus faibles que sur une base de données relationnelle distribuée (MPP). Plutôt que de répartir uniformément les données sur tous les nœuds, les moteurs SQL exécutant des requêtes sur un système de fichiers distribué peuvent obtenir des garanties de colocalisation des données en imbriquant les données et en évitant ainsi des jointures potentiellement coûteuses impliquant un brassage important sur le réseau. Les fonctions d'agrégation définies par l'utilisateur qui peuvent être utilisées dans les fonctions de fenêtre sont une autre fonctionnalité extrêmement puissante.

Méthode pour générer des données basées sur l'union de tous

sélectionnez 1 a , 1 b union tout sélectionnez 1 , 2 union tout sélectionnez 1 , 3 union tout sélectionnez 2 , 1 union tout sélectionnez 5 , 1

SQL Server 2008 prend en charge la fonctionnalité « constructeur de lignes », spécifiée dans la norme SQL:1999

sélectionnez * parmi ( valeurs ( 1 , 1 ), ( 1 , 2 ), ( 1 , 3 ), ( 2 , 1 ), ( 5 , 1 )) comme x ( a , b )