LE SQL de A à Z : 2e partie - le simple (?) SELECT et les fonctions SQL

SQLPro

Dans le précédent article, nous avons vu l'historique de SQL et ses différentes composantes. Nous entrons maintenant dans le vif du sujet, en nous intéressant au simple SELECT.
Simple ? Pas si sûr…
Dans le dernier document normatif de l'ISO, la syntaxe de la commande SELECT, est décrite en plus de 300 pages… C'est pourquoi nous nous permettons de mettre en doute la simplicité de la commande SELECT !

- Titre : LE SQL de A à Z : 2e partie - le simple (?) SELECT et les fonctions SQL

- Auteur : SQLPro

- Parution : 23 janvier 2003

- Licence : Copyright ® 2003 Frédéric Brouard. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Préambule

- 1. La commande SELECT

- 1-1. L'opérateur * (étoile)

- 1-2. L'opérateur DISTINCT (ou ALL)

- 1-3. L'opérateur AS

- 1-4. Opérateur de concaténation

- 1-5. Opérateurs mathématiques de base

- 1-6. Particularité du « FROM »

- 1-7. Utilisation du caractère double quote (guillemet)

- 2. La clause ORDER BY

- 3. La clause WHERE

- 3-1. Opérateurs de comparaison

- 3-2. Opérateur IN

- 3-3. Opérateur BETWEEN

- 3-4. Opérateur LIKE

- 3-5. Résumé des opérateurs pour les prédicats de la clause WHERE

- 4. Fonctions diverses

- 4-1. Trantypage à l'aide de la fonction CAST

- 4-2. Mise en majuscule / Minuscule

- 4-3. Supprimer les blancs (ou tout autre caractère)

- 4-4. Extraire une sous-chaine

- 4-5. Opérateur de traitement des dates

- 4-5-1. Extraire un paramètre temporel d'une date

- 4-5-2. Heure et date courante

- 4-6. Opérateurs statistiques

- 4-7. Autres fonctions normalisées

- 4-8. Autres opérateurs mathématiques (non normalisés)

- 4-9. Autres opérateurs de traitement des chaines de caractères (non normalisés)

- 4-10. Autres opérateurs sur les valeurs temporelles (non normalisés)

- 4-11. Opérateurs d'ensemble (non normalisés)

- 5. Traitement des « valeurs » nulles

- 5-1. Le null n'est ni la chaine vide, ni le zéro

- 5-2. Opérateurs de traitement des marqueurs NULL

- 6. Négation de valeurs

- 7. Les branchements dans le SQL

- 7-1. CASE sur expression

- 7-2. CASE généralisé

- 8. Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)

- 9. Résumé

- 10. Conclusion

Préambule

NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page « La base de données exemple ».

1 - La commande SELECT

Vous trouverez des compléments d'information sur le sujet aux pages 53 à 127 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

Le SELECT est la commande de base du SQL destinée à extraire des données d'une base ou calculer de nouvelles données à partir d'existantes…

Voici la syntaxe générale d'une commande SELECT :

SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM nom de table ou de la vue
[WHERE prédicats]
[GROUP BY ordre des groupes]
[HAVING condition]
[ORDER BY ] liste de colonnes

NOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en minuscule et entre crochets on trouve les parties optionnelles.

En fait l'ordre SQL SELECT est composé de six clauses dont quatre sont optionnelles.
Clauses de l'ordre SELECT :

SELECT

Spécification des colonnes du résultat

FROM

Spécification des tables sur lesquelles porte l'ordre

WHERE

Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)

GROUP BY

Définition d'un groupe (sous-ensemble)

HAVING

Filtre portant sur les résultats (conditions de regroupement des lignes)

ORDER BY

Tri des données du résultat

NOTA : ma plupart du temps, la difficulté réside dans la compréhension de la différence entre le filtre WHERE et le filtre HAVING. Disons plus pragmatiquement que le filtre WHERE permet de filtrer les données des tables tandis que le filtre HAVING permet de filtrer les données du résultat…

REMARQUE : pour spécifier une valeur littérale, il faut l'entourer de guillemets simples.

Un premier exemple basique :

Exemple 1

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'

CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
MARTIN   Marc
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
FAURE    Alain
PAUL     Marcel
DUVAL    Arsène
PHILIPPE André
CHABAUD  Daniel
BAILLY   Jean-François
...

Permet de trouver les noms et prénoms des clients dont le titre est ‘M.' (monsieur).

NOTA : comme tous les paramètres à prendre sous forme de littéraux doivent être exprimés entourés d'apostrophes (simples quotes), il faut dédoubler un tel caractère s'il s'avère présent dans la chaine utilisée.

1-1 - L'opérateur * (étoile)

Le caractère * (étoile) récupère toutes les colonnes de la table précisée dans la clause FROM de la requête.
Juste après le mot clef SELECT, on précise les colonnes de la table qui doivent être présentées dans la réponse.
L'utilisation du caractère étoile ramène toutes les colonnes de la table dans la réponse. Dans le cas contraire, il faut expressément nommer chacune des colonnes et les séparer par des virgules.

Exemple 2

SELECT   *
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'

CLI_ID   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
-------  --------  --------  ----------  ------------
1  M.        DUPONT    Alain       NULL
2  M.        MARTIN    Marc        Transports MARTIN & fils
3  M.        BOUVIER   Alain       NULL
4  M.        DUBOIS    Paul        NULL
5  M.        DREYFUS   Jean        NULL
6  M.        FAURE     Alain       Boulangerie du marché
11  M.        PAUL      Marcel      Cie Internationale des Mach...
12  M.        DUVAL     Arsène      NULL
13  M.        PHILIPPE  André       NULL
16  M.        CHABAUD   Daniel      NULL
...

Notons tout de suite la présence à plusieurs reprises du mot clef « NULL » dans la colonne CLI_ENSEIGNE. Non, il ne s'agit pas d'une enseigne particulière, mais simplement de l'absence d'information. Nous verrons que l'absence d'information, c'est le marquer « NULL » qui diffère de la chaine de caractères vierge ("") ou encore du zéro.

1-2 - L'opérateur DISTINCT (ou ALL)

Lorsque le moteur construit la réponse, il rapatrie toutes les lignes correspondantes, généralement dans l'ordre où il les trouve, même si ces dernières sont en double, c'est-à-dire qu'il récupère toutes les lignes (ALL par défaut). C'est pourquoi il est souvent nécessaire d'utiliser le mot clef DISTINCT qui permet d‘éliminer les doublons dans la réponse.

Exemples 3 et 4

SELECT   CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'

CLI_PRENOM
----------
Alain
Marc
Alain
Paul
Jean
Alain
Marcel
Arsène
André
Daniel
...

SELECT distinct   CLI_PRENOM
FROM              T_CLIENT
WHERE             TIT_CODE = 'M.'

CLI_PRENOM
----------
Alain
Alexandre
André
Arnaud
Arsène
Bernard
Christian
Christophe
Daniel
Denis
...

1-3 - L'opérateur AS

Vous pouvez rajouter autant de colonnes que vous le désirez en utilisant le mot clef AS.
En principe l'opérateur AS sert à donner un nom à de nouvelles colonnes créées par la requête.

Exemple 5

SELECT   CLI_NOM as NOM, 'homme' as SEXE
FROM     T_CLIENT
WHERE    TIT_CODE = 'M.'

NOM      SEXE
-------  -----
DUPONT   homme
MARTIN   homme
BOUVIER  homme
DUBOIS   homme
DREYFUS  homme
FAURE    homme
PAUL     homme
DUVAL    homme
PHILIPPE homme
CHABAUD  homme
...

1-4 - Opérateur de concaténation

L'opérateur || (double barre verticale) permet de concaténer des champs de type caractères.

Exemple 6

SELECT   TIT_CODE || ' ' || CLI_PRENOM || ' ' ||  CLI_NOM as NOM
FROM     T_CLIENT

NOM
-----------------------
M. Alain DUPONT
M. Marc MARTIN
M. Alain BOUVIER
M. Paul DUBOIS
M. Jean DREYFUS
M. Alain FAURE
M. Paul LACOMBE
Melle. Evelyne DUHAMEL
Mme. Martine BOYER
M. Martin MARTIN
...

Néanmoins on trouve dans certains SGBDR le + comme opérateur de concaténation, comme la fonction CONCAT.

1-5 - Opérateurs mathématiques de base

On, peut utiliser les opérateurs mathématiques de base pour combiner différentes colonnes
(,+,-, *, /,).

Exemple 7

SELECT   CHB_ID, TRF_CHB_PRIX * 1.206 AS TARIF_TTC
FROM     TJ_TRF_CHB
WHERE    TRF_DATE_DEBUT = '2001-01-01'

CHB_ID  TARIF_TTC
------  ---------
1      424,51
2      482,40
3      617,47
4      424,51
5      463,10
6      482,40
7      424,51
8      540,29
9      482,40
10      617,47
...

1-6 - Particularité du « FROM »

Il est possible de surnommer une table dans la clause FROM, dans ce cas, la syntaxe de la partie FROM de la commande SELECT est la suivante :

FROM nom_de_table ou nom_de_la_vue surnom

Nous verrons dans quel cas ce renom est nécessaire ou obligatoire.
NOTA : certains auteurs préfèrent utiliser le mot d'alias que nous rejetons, car il indique souvent un autre concept, ou de synonyme, que nous acceptons de manière timorée…

1-7 - Utilisation du caractère double quote (guillemet)

Lorsqu'un nom d'un élément d'une base de données (table, colonne par exemple) est identique à un mot clef du SQL, il convient de l'entourer de guillemets (doubles quotes). En principe, les mots réservés du SQL sont déconseillés pour nommer des objets du modèle physique de données…

Imaginons une table de nom JOIN, composée des champs suivants :

NOM      SELECT  DATE        NOT
-------  ------  -------     ---
DURAND   Oui     1999-11-12  F
DUVAL    Non     1998-01-17  M

Exemple 8 : nous désirons sélectionner les colonnes SELECT et DATE lorsque la colonne NOT vaut F…

SELECT SELECT, DATE
FROM JOIN
WHERE NOT = 'F'

ERREUR !

SELECT "SELECT", "DATE"
FROM "JOIN"
WHERE "NOT" = 'F'

Correct : on entoure les mots clefs du SQL par des doubles quotes

Cela est aussi nécessaire lorsque le nom (d'une colonne ou d'une table) est composé de caractères particuliers tels que les blancs ou autres, ce qui est à éviter.

REMARQUE : les noms des identifiants d'objet de base de données doivent être écrit dans le jeux de caractères restreint suivant :
[A..Z] + [a..z] + [0..9] + [ _ ].
Ils ne doivent pas commencer par un chiffre et sont insensibles à la casse (indifférence entre majuscules et minuscules).

2 - La clause ORDER BY

Vous trouverez des compléments d'information sur le sujet aux pages 66 à 70 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

ORDER BY colonne1 | 1 [ASC ou DESC ] [, colonne2 | 2 [ASC ou DESC ] ...

Cette clause permet de définir le tri des colonnes de la réponse, soit en précisant le nom littéral de la colonne, soit en précisant son n° d'ordre dans l'énumération qui suit le mot clef SELECT.
ASC spécifie l'ordre ascendant et DESC l'ordre descendant du tri. ASC ou DESC peut être omis, dans ce cas c'est l'ordre ascendant qui est utilisé par défaut.

Bien que la clause ORDER BY ne soit pas nécessaire, il est souvent utile de trier la réponse en fonction des colonnes. En revanche le temps de réponse s'en ressent souvent.
Pour spécifier l'ordre de tri, on doit placer les noms des colonnes séparées par des virgules juste après le mot clef « ORDER BY », dans l'ordre voulu. On peut aussi utiliser le rang de chaque colonne dans l'ordre spécifié dans la clause SELECT.

Attention : le tri est un tri interne, il ne faut donc placer dans cette clause que les noms des colonnes présentées dans la clause SELECT.

Souvent, le fait de placer DISTINCT suffit, en général, à établir un tri puisque le moteur doit se livrer à une comparaison des lignes, mais ce mécanisme n'est pas garanti, car ce tri s'effectue dans un ordre non contrôlable qui peut varier d'un serveur à l'autre.

Exemple 9

SELECT     CLI_NOM, CLI_PRENOM
FROM       T_CLIENT
ORDER BY   CLI_NOM, CLI_PRENOM

ou

SELECT     CLI_NOM, CLI_PRENOM
FROM       T_CLIENT
ORDER BY   1, 2

CLI_NOM   CLI_PRENOM
--------  ----------
AIACH     Alexandre
ALBERT    Christian
AUZENAT   Michel
BACQUE    Michel
BAILLY    Jean-François
BAVEREL   Frédéric
BEAUNEE   Pierre
BENATTAR  Bernard
BENATTAR  Pierre
BENZAQUI  Joël
...

REMARQUE : les marqueurs NULL sont situés en premier dans l'ordre ainsi établi.

NOTA : Un problème, qui n'est pas résolu, est de pouvoir choisir l'ordre des colonnes de la réponse. Sur certains serveurs cela peut être obtenu en plaçant les noms des colonnes à obtenir dans l'ordre où l'on veut les voir apparaître dans la clause SELECT, mais cette possibilité n'est jamais garantie…

ATTENTION : la clause ORDER BY est la dernière clause de tout ordre SQL et ne doit figurer qu'une seule fois dans le SELECT, même s'il existe des requêtes imbriquées ou un jeu de requêtes ensemblistes.

3 - La clause WHERE

Vous trouverez des compléments d'information sur le sujet aux pages 62 à 165 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

WHERE prédicats

Le prédicat doit contenir n'importe quelle expression logique renvoyant une valeur vrai.
Ainsi, une requête aussi stupide que la suivante, est supposée fonctionner :

Exemple 10

SELECT   CLI_NOM
FROM     T_CLIENT
WHERE    1=1

CLI_NOM
-------
DUPONT
MARTIN
BOUVIER
DUBOIS
DREYFUS
FAURE
LACOMBE
DUHAMEL
BOYER
MARTIN
...

Attention : la plupart des SGBDR ne comportent pas de colonne de type booléen. Une requête comme la première risque d'échouer.

Exemple 11

SELECT   *
FROM     TJ_CHB_PLN_CLI
WHERE    CHB_PLN_CLI_OCCUPE

ERREUR !
bien que CHB_PLN_CLI_OCCUPE puisse être du booléen, la plupart
des compilateurs SQL n'acceptent pas ce test direct.

SELECT   *
FROM     TJ_CHB_PLN_CLI
WHERE    CHB_PLN_CLI_OCCUPE = True

CORRECT ...
Mais sur certains compilateurs SQL il faut faire :
CHB_PLN_CLI_OCCUPE = 'True' (littéral).
Si le type booléen n'existe pas, alors il faut faire
CHB_PLN_CLI_OCCUPE = 1 si l'on a choisi de définir
les booléens comme INTEGER(1) avec 0 et 1

Pour pallier le manque de booléen, on utilise soit un littéral (True/False, Vrai/Faux, Oui/Non), soit un numérique avec les valeurs 0 (Faux) et 1 (Vrai). L'avantage des valeurs numériques est que le calcul logique est comparable aux divisions et additions…

opérateur ET

FAUX

VRAI

FAUX

FAUX

FAUX

VRAI

FAUX

VRAI

similitude entre le ET et la multiplication

multiplication

0

1

0

0

0

1

0

1, <> 0

opérateur OU

FAUX

VRAI

FAUX

FAUX

VRAI

VRAI

VRAI

VRAI

similitude entre le OU et l'addition

addition

0

1

0

0

1, <> 0

1

1, <> 0

2, <> 0

3-1 - Opérateurs de comparaison

Dans la clause WHERE, vous disposez de différents opérateurs de comparaisons logiques :

WHERE valeur1 [NOT et] = ou < ou <= ou > ou >= ou <>valeur2 [OR ou AND ...]

Exemple 12

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM >= 'A' AND CLI_NOM <'E'

ou

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    (CLI_NOM >= 'A')
AND
(CLI_NOM <'E')

plus lisible !

CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
DUHAMEL  Evelyne
BOYER    Martine
DUVAL    Arsène
DAUMIER  Amélie
CHABAUD  Daniel
BAILLY   Jean-François
...

Ici on obtient tous les noms et prénoms des clients dont le nom commence par les lettres A, B, C ou D.

Attention : dans certains moteurs de requête SQL l'opérateur « différent de » (<>) s'écrit !=

3-2 - Opérateur IN

L'opérateur IN permet de rechercher si une valeur se trouve dans un ensemble donné, quel que soit le type des valeurs de référence spécifié (alpha, numérique, date…). Bien entendu, il est possible d'inverser le fonctionnement de l'opérateur IN en lui adjoignant l'opérateur NOT.

Exemple 13

SELECT   TIT_CODE, CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    TIT_CODE IN ('Mme.', 'Melle.')

TIT_CODE  CLI_NOM     CLI_PRENOM
--------  ----------  ----------
Mme.      BOYER       Martine
Mme.      GALLACIER   Noëlle
Mme.      HESS       Lucette
Mme.      LETERRIER   Monique
Mme.      MARTINET    Carmen
Mme.      DAVID       Jacqueline
Mme.      MOURGUES    Jacqueline
Mme.      ZAMPIERO    Annick
Mme.      ROURE       Marie-Louise
Mme.      DE CONINCK  Patricia
...

On recherche les clients de sexe féminin, basés sur le code titre.

Le contenu de la parenthèse peut être remplacé par le résultat d'une requête possédant une colonne unique. Dans ce cas on parle de requêtes imbriquées, ce que nous verrons plus loin.

3-3 - Opérateur BETWEEN

L'opérateur BETWEEN permet de rechercher si une valeur se trouve dans un intervalle donné, quel que soit le type des valeurs de référence spécifié (alpha, numérique, date…).

Ainsi, la requête vue dans l'exemple 12 peut s'écrire :

Exemple 14

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM BETWEEN 'A' AND 'E'

CLI_NOM  CLI_PRENOM
-------  ----------
DUPONT   Alain
BOUVIER  Alain
DUBOIS   Paul
DREYFUS  Jean
DUHAMEL  Evelyne
BOYER    Martine
DUVAL    Arsène
DAUMIER  Amélie
CHABAUD  Daniel
BAILLY   Jean-François
...

NOTA : les opérateurs IN et BETWEEN sont très pratiques dans le cas où l'on désire effectuer des requêtes où l'utilisateur peut saisir une liste de choix multiples (IN) ou une plage de valeur (BETWEEN).

3-4 - Opérateur LIKE

L'opérateur LIKE permet d'effectuer une comparaison partielle. Il est surtout employé avec les colonnes contenant des données de type alpha. Il utilise les jokers % et _ (‘pour cent' et ‘blanc souligné'). Le joker % remplace n'importe quelle chaine de caractères, y compris la chaine vide. Le blanc souligné remplace un et un seul caractère.

Exemple 15

SELECT   CLI_NOM, CLI_PRENOM
FROM     T_CLIENT
WHERE    CLI_NOM LIKE 'B%'

CLI_NOM  CLI_PRENOM
-------  ----------
BOUVIER  Alain
BOYER    Martine
BAILLY   Jean-François
BOUCHET  Michel
BEAUNEE  Pierre
BERGER   Jean-Pierre
BOURA    André
BENZAQUI Joël
BAVEREL  Frédéric
BERTRAND Christophe
...

On recherche les clients dont le nom commence par B.

Mais si vos données sont susceptibles de contenir un des deux caractères joker, alors il faut recourir à une séquence d'échappement, à l'aide du mot clef ESCAPE…

Cherchons les clients dont l'enseigne contient au moins un caractère blanc souligné :

Exemple 16

SELECT   *
FROM     T_CLIENT
WHERE    CLI_ENSEIGNE
LIKE  '%_%'

CLI_ID TIT_CODE CLI_NOM    CLI_PRENOM    CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------
2 M.       MARTIN     Marc          Transports MARTIN & fils
6 M.       FAURE      Alain         Boulangerie du marché
10 M.       MARTIN     Martin        HERMAREX IMPORT_EXPORT
11 M.       PAUL       Marcel        Cie Internationale des Mach...
17 M.       BAILLY     Jean-François Entreprise DUPONT CHAUFFAGE
24 M.       CHTCHEPINE Dominique     HOTEL *** DE LA GARE
26 M.       GARREAU    Paul          IBM Corp.
34 Mme.     GALLACIER  Noëlle        Transports GALLACIER
42 Mme.     LETERRIER  Monique       SA AROMAX ENTREVONT
49 M.       COULOMB    Renaud        Cabinet COULOMN et CALEMANT

SELECT   *
FROM     T_CLIENT
WHERE    CLI_ENSEIGNE
LIKE  '%#_%' ESCAPE '#'

CLI_ID TIT_CODE CLI_NOM    CLI_PRENOM    CLI_ENSEIGNE
------ -------- ---------- ------------- ------------------------------
10 M.       MARTIN     Martin        HERMAREX IMPORT_EXPORT

Pour traiter ce cas, on définit « # » comme caractère d'échappement. Le caractère qui suit ce caractère d'échappement est donc interprété comme un caractère et non comme un joker.

NOTA : l'opérateur LIKE effectue une recherche en tenant compte de la différence entre lettres majuscules et minuscules. Si vous voulez effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il convient d'utiliser les opérateurs LOWER et UPPER (voir ci-dessous). Mais la plupart du temps, l'utilisation du like dans un SGBDR donné ignore la casse.

3-5 - Résumé des opérateurs pour les prédicats de la clause WHERE

Voici un tableau résumant les principaux opérateurs utilisés pour la construction des prédicats :

opérateurs de comparaisons

= <> < <= > >=

connecteurs logiques

{OR | AND}

opérateur de négation

NOT

parenthèses

( … )

opérateurs mathématiques

+ - * /

comparaison logique

IS [NOT] {TRUE | FALSE | UNKNOWN}

comparaison avec valeur

IS [NOT] NULL

intervalle

valeur BETWEEN borne_basse AND borne_haute

comparaison partielle de chaine de caractères

valeur LIKE motif [ESCAPE echappement]

comparaison à une liste de valeur

valeur [NOT] IN (liste)

4 - Fonctions diverses

Vous trouverez des compléments d'information sur le sujet aux pages 71 à 107 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

4-1 - Trantypage à l'aide de la fonction CAST

Il permet de changer le type de données d'une colonne afin d'effectuer une comparaison de données de type hétérogène par exemple entre un champ contenant des données numériques et un champ contenant des données de type chaine de caractères…
Sa syntaxe est CAST(colonne AS nouveau type).

Exemple 17

SELECT CHB_ID, CHB_NUMERO, CHB_POSTE_TEL
FROM T_CHAMBRE
WHERE CAST(CHB_POSTE_TEL AS INTEGER) / 10 > CHB_NUMERO

CHB_ID  CHB_NUMERO  CHB_POSTE_TEL
------  ----------  -------------
1           1  101
2           2  102
3           3  103
4           4  104
5           5  105
6           6  106
7           7  107
8           8  108
9           9  109
10          10  110

L'opérateur CAST permet de transtyper les valeurs contenues dans une colonne.
Bien entendu il faut qu'un type de donnée puisse être converti dans un autre type (compatibilité de types) afin que la réponse ne soit pas entachée d'erreurs ou d'omissions.

Exemple 18

SELECT   ADR_VILLE, CAST(ADR_CP AS INTEGER) + 1
FROM     T_ADRESSE

ADR_VILLE             ADR_CP  ADR_CP + 1
------------          ------  ----------
VERSAILLES            78000        78001
MONTMAIZIN            11254        11255
PARIS                 75015        75016
VERGNOLLES CEDEX 452  84524        84525
MARSEILLE             13002        13003
PARIS                 75012        75013
BONNEUIL CEDEX        94152        94153
PARIS                 75012        75013
PARIS                 75014        75015
PARIS                 75017        75018
...

4-2 - Mise en majuscule / Minuscule

Les opérateurs LOWER et UPPER permettent de mettre en majuscules ou en minuscules des chaines de caractères dans les requêtes.

Exemple 19

SELECT upper(CLI_PRENOM), lower(CLI_NOM)
FROM T_CLIENT

CLI_NOM  CLI_PRENOM
-------  ----------
ALAIN    dupont
MARC     martin
ALAIN    bouvier
PAUL     dubois
JEAN     dreyfus
ALAIN    faure
PAUL     lacombe
EVELYNE  duhamel
MARTINE  boyer
MARTIN   martin
...

NOTA : pour effectuer une recherche en ne tenant aucunement compte de la différence entre majuscules et minuscules, il faut utiliser l'opérateur UPPER (ou lower mais attention à la transformation des accents !) :

Exemple 20

SELECT *
FROM T_CLIENT
where upper(CLI_PRENOM) = upper(CLI_NOM)

CLI_ID  TIT_CODE  CLI_NOM  CLI_PRENOM  CLI_ENSEIGNE
------  --------  -------  ----------  ----------------------
10  M.        MARTIN   Martin      HERMAREX IMPORT_EXPORT

NOTA : certains SGBDR permettent de paramétrer l'activation de la recherche systématique des chaines de caractères sans tenir compte de la casse. Sur d'autres, le paramétrage permet de confondre les lettres accentuées ou non…

4-3 - Supprimer les blancs (ou tout autre caractère)

La fonction TRIM permet de supprimer en tête ou en queue (ou les deux) le blanc ou tout autre caractère spécifié.

TRIM ([LEADING ou TRAILING ou BOTH] [caractère] FROM nom de colonne)

LEADING : suppression en tête
TRAILING : suppression en queue
BOTH : suppression en tête et en queue

Dans notre table téléphone, nous voulons supprimer le zéro de tête des n° afin de pouvoir les communiquer aux étrangers qui n'ont pas besoin de composer ce chiffre (ils doivent simplement composer le 00 33 suivi du numéro à 9 chiffres).

Exemple 21

SELECT   TEL_NUMERO,
'00~33 ' || TRIM(LEADING '0' FROM TEL_NUMERO)
AS TEL_INTERNATIONAL
FROM     T_TELEPHONE

TEL_NUMERO      TEL_INTERNATIONAL
--------------  -----------------
01-45-42-56-63  00~33 1-45-42-56-63
01-44-28-52-52  00~33 1-44-28-52-52
01-44-28-52-50  00~33 1-44-28-52-50
06-11-86-78-89  00~33 6-11-86-78-89
02-41-58-89-52  00~33 2-41-58-89-52
01-51-58-52-50  00~33 1-51-58-52-50
01-54-11-43-21  00~33 1-54-11-43-21
06-55-41-42-95  00~33 6-55-41-42-95
01-48-98-92-21  00~33 1-48-98-92-21
01-44-22-56-21  00~33 1-44-22-56-21
...

NOTA : certains serveurs SQL proposent différentes fonctions comme LTRIM et RTRIM pour une suppression des blancs en tête ou en queue.

4-4 - Extraire une sous-chaine

La fonction SUBSTRING permet d'extraire une sous-chaine d'une chaine de caractères. Elle a besoin de l'ordre du premier caractère et du nombre de caractères sur lequel elle doit opérer.

SUBSTRING ( nom de colonne FROM n TO m)

Extrait la sous-chaine de nom de colonne en commençant à n sur m caractères.

Exemple 22

SELECT  CLI_NOM, CLI_PRENOM,
SUBSTRING(CLI_PRENOM FROM 1 FOR 1) ||
SUBSTRING(CLI_NOM FROM 1 FOR 1)
AS INITIALES
FROM    T_CLIENT

CLI_NOM  CLI_PRENOM  INITIALES
-------  ----------  ---------
DUPONT   Alain       AD
MARTIN   Marc        MM
BOUVIER  Alain       AB
DUBOIS   Paul        PD
DREYFUS  Jean        JD
FAURE    Alain       AF
LACOMBE  Paul        PL
DUHAMEL  Evelyne     ED
BOYER    Martine     MB
MARTIN   Martin      MM
...

Cet exemple construit les initiales des clients à partir des colonnes CLI_NOM et CLI_PRENOM_CLI.

Attention, certains SGBDR utilisent la fonction SUBSTR.

4-5 - Opérateur de traitement des dates

4-5-1 - Extraire un paramètre temporel d'une date

L'opérateur EXTRACT permet d'extraire depuis une date, le jour le mois ou l'année…

EXTRACT ( YEAR ou MONTH ou DAY FROM nom de colonne )

Dans la table des réservations, on recherche l'identifiant des chambres ayant été réservées au cours du mois de mai de n'importe quelle année et pour trois personnes.

Exemple 23

SELECT distinct CHB_ID
FROM  TJ_CHB_PLN_CLI
WHERE EXTRACT(MONTH FROM PLN_JOUR) = 5
AND CHB_PLN_CLI_RESERVE = 1
AND CHB_PLN_CLI_NB_PERS = 3

CHB_ID
------
1
5
6
8
11
12
16
17
18
20

NOTA : il est dommage de constater que la fonction EXTRACT du standard SQL, souvent fort utile, est rarement présente dans les moteurs de bases de données. Ni Access, ni Oracle, ni Sybase, ni SQL Server en sont dotés. Seul le middleware BDE de Borland Inprise Corel permet d'exploiter pleinement cette fonction avec les SGBDR Paradox, dBase, FoxPro, InterBase, MSSQL, Sybase, Informix, DB2, Oracle.
Cependant il est courant de trouver des fonctions s'en approchant : Exemple DATEPART dans SQL Server.

4-5-2 - Heure et date courante

L'heure courante, la date courante et le combiné date/heure courant peuvent être obtenus à l'aide des fonctions CURRENT_DATE, CURRENT_TIME et CURRENT_TIMESTAMP.

Exemple 24

SELECT distinct CHB_ID
FROM TJ_CHB_PLN_CLI
WHERE (CHB_PLN_CLI_RESERVE = 1)
AND PLN_JOUR BETWEEN CURRENT_DATE and CURRENT_DATE + 14
AND CHB_PLN_CLI_NB_PERS = 3

attention, le résultat de cette requête varie en fonction de
la date à laquelle vous l'exécutez !

CHB_ID
------
1
5
6
8
11
12
16
17
18
20

Cette requête renvoie les chambres réservées pour trois personnes entre la date du jour et pour les deux semaines à venir.

Attention : la plupart des SGBDR n'acceptent pas encore cette version normalisée des fonctions de recherche de temps courant. Voici les fonctions spécifiques aux différents serveurs SQL :

Oracle

SYSDATE()

Sybase

GETDATE()

SQL Server

GETDATE()

Access

NOW()

MySQL

NOW()

Paradox (QBE)

TODAY

4-6 - Opérateurs statistiques

Il est possible de réaliser des comptages statistiques sur les colonnes, à l'aide des opérateurs AVG (moyenne), MAX (maximum), MIN (minimum), SUM (total), COUNT (nombre). On les appelle aussi fonctions d'agrégation.

Exemple 25

SELECT AVG(TRF_CHB_PRIX) as MOYENNE,
MAX(TRF_CHB_PRIX) as MAXI,
MIN(TRF_CHB_PRIX) as MINI,
SUM(TRF_CHB_PRIX) as TOTAL,
COUNT(TRF_CHB_PRIX) as NOMBRE
FROM TJ_TRF_CHB
WHERE TRF_DATE_DEBUT = '2001-01-01'

MOYENNE   MAXI      MINI      TOTAL       NOMBRE
--------  --------  --------  ----------  ------
406,74 F  512,00 F  352,00 F  7 728,00 F      19

Cette requête calcule la moyenne, le montant maximum, minimum, la totalisation et le nombre des tarifs de chambre pour la date de début du premier janvier 2001.

On peut s'étonner que les opérateurs statistiques VARIANCE ou STDDEV (écart type) soient rarement présents dans les SGBDR, car il s'agit de fonctions statistiques qui possèdent une grande utilité. Mais la norme SQL 92 ne les a pas retenus. Cependant, ils existent notamment dans Oracle. Cela est d'autant plus étonnant que les techniques modernes du DataWareHouse et en particulier le DataMining, utilisent ces fonctions dans le cadre de calculs statistiques d'hypercubes (techniques OLAP et ROLAP).

ATTENTION : nous verrons que l'utilisation des fonctions statistiques nécessite la plupart du temps la mise en place d'une clause de groupage, afin de déterminer quel est le sous-ensemble cible d'agrégation pour les calculs.

4-7 - Autres fonctions normalisées

BIT_LENGTH

Taille d'une colonne de type BIT ou BIT VARYING (nombre de bits)

CHAR_LENGTH

Taille d'une colonne de type caractère (nombre de caractères)

OCTET_LENGTH

Taille d'une colonne de type caractère (nombre d'octets)

CURRENT_DATE

Date en cours

CURRENT_TIME

Heure en cours

CURRENT_TIMESTAMP

Date et heure en cours

CONVERT

Conversion paramétrée d'une chaine de caractères

POSITION

Position d'une chaine de caractères dans une sous-chaine

TRANSLATE

Traduction d'une chaine de caractères dans un format spécifié

4-8 - Autres opérateurs mathématiques (non normalisés)

Les opérateurs ci-dessous peuvent être implémentés dans différents moteurs.

ABS

valeur absolue

MOD

modulo

SIGN

signe

SQRT

racine carrée

CEIL

plus petit entier

FLOOR

plus grand entier

ROUND

arrondi

TRUNC

tronqué

EXP

exponentielle

LN

logarithme népérien

LOG

logarithme décimal

POWER

puissance

COS

cosinus

COSH

cosinus hyperbolique

SIN

sinus

SINH

sinus hyperbolique

TAN

tangente

TANH

tangente hyperbolique

PI

constante Pi

Certains sont rarement implémentés du fait que les SGBDR sont axés sur l'informatique de gestion, la collecte et le traitement d'informations et non le calcul mathématique.

Attention : le nom de certains de ces opérateurs peut différer d'un SGBDR à l'autre.

4-9 - Autres opérateurs de traitement des chaines de caractères (non normalisés)

CONCAT

concaténation : équivalent du || - Nota : utiliser de préférence || plus standard. Le + entre colonnes alphanumériques peut aussi souvent être utilisé comme opérateur de concaténation, préférez de toute façon ||

INITCAP

initiales en lettres capitales

LPAD

complément ou troncature à n positions à gauche

LTRIM / RTRIM

suppression en tête/queue d'une chaine

REPLACE

remplacement

RPAD

complément ou troncature à n positions à droite

SOUNDEX

code de consonance - Attention : phonétique souvent anglaise

INSTR

Position d'une chaine dans une sous-chaine

LENGTH

longueur de la chaine

TO_CHAR

numérique sous forme littérale - Attention : souvent en anglais

ASCII

code ASCII d'un caractère

CHR

caractère dont le code ASCII est donné

REVERSE

Inverse l'ordre des caractères d'une chaine

FLIP

Pivote les parties droite et gauche d'une chaine par rapport au n° du caractère servant de pivot.

Attention : le nom de certains de ces opérateurs peut différer d'un SGBDR à l'autre.

4-10 - Autres opérateurs sur les valeurs temporelles (non normalisés)

ADD_MONTH, ADD_DAY, ADD_YEAR …

ajoute des mois, des jours des années à une date

LAST_DAY

renvoie le n° du dernier jour d'un mois d'une date

MONTH_BETWEEN

nombre de mois entre deux dates

NEXT_DAY

date du prochain jour d'un nom donné

TO_CHAR

date sous forme littérale - Attention : souvent en anglais

DATE_DIFF

différence entre deux dates

4-11 - Opérateurs d'ensemble (non normalisés)

GREATEST

la plus grande des valeurs d'un ensemble

LEAST

la plus petite des valeurs d'un ensemble

DECODE

renvoie la valeur située en nième position

5 - Traitement des « valeurs » nulles

Vous trouverez des compléments d'information sur le sujet aux pages 115 à 120 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

NOTA : le NULL n'est pas à proprement parler une valeur, mais bien l'absence de valeur, c'est pourquoi nous parlerons de marqueur NULL et non de valeur NULL.
Le marqueur NULL pose une quantité de problèmes et nous allons dans ce paragraphe soulever un coin du voile, que nous traiterons un peu plus tard dans le cas général de la recherche des occurrences d'inexistence.

5-1 - Le null n'est ni la chaine vide, ni le zéro

NULL n'est pas une valeur. C'est un marqueur. Par conséquent le marqueur NULL ne peut jamais être comparé à une valeur.

Recherchons les clients qui n'ont pas d'enseigne.

Exemple 26

SELECT CLI_ID, CLI_NOM
FROM T_CLIENT
WHERE CLI_ENSEIGNE = ''

CLI_ID  CLI_NOM
-------  --------
...

La réponse doit produire une table vide !
Pour controurner ce problème il faut :
soit penser à enregistrer une chaine de caractères vide lors de l'insertion des données dans la table
soit la clause WHERE avec un opérateur spécialisé dans le traitement des valeurs nulles

Le problème des « null » a été bien décrit dans le livre de Joe Celko intitulé « SQL avancé » publié par Thomson International Publishing. Joe Celko pose la question en ces termes : l'absence d'information est-elle due à son ignorance ou à son impertinence ? Pourquoi donc ne pas faire de différence entre la couleur du toit d'une voiture qui n'est pas connue, et la couleur du toit d'une moto qui n'est pas applicable… Certains logiciens de l'algèbre relationnelle sont même allés plus loin en proposant différentes valeurs pour gérer les différents cas, en distinguant des cas très différents : le « null », le « inconnu » et le « inapplicable »…

Il y a donc un véritable dilemme à utiliser des requêtes en se basant sur des critères d'absence de valeur et il faut toujours faire très attention aux clauses qui utilisent des références aux valeurs nulles, suivant ce que l'on veut obtenir. D'autant plus que les NULL se propagent dans les calculs.

Voici un extrait de la table T_LIGNE_FACTURE

LIF_ID    FAC_ID    LIF_QTE    LIF_REMISE_POURCENT  LIF_REMISE_MONTANT  LIF_MONTANT  LIF_TAUX_TVA
--------  --------  ---------  -------------------  ------------------  -----------  ------------
1         1       1,00                15,00                NULL     320,00 F         18,60
2         3       1,00                 NULL             50,00 F     250,00 F         18,60
3         3       1,00                 NULL             50,00 F     320,00 F         18,60
4         3       1,00                 NULL             50,00 F     240,00 F         18,60
5         5       1,00                 NULL                NULL     320,00 F         18,60
6         5       1,00                 NULL                NULL     220,00 F         18,60
7         7       1,00                 NULL                NULL     220,00 F         18,60
8         7       1,00                 NULL                NULL     250,00 F         18,60
9         7       1,00                 NULL                NULL     320,00 F         18,60
10         7       1,00                 NULL                NULL     270,00 F         18,60
...

Nous voulons calculer le montant total de chacune des lignes de cette table, pour une facture donnée.

La requête pour FAC_ID = 3 est la suivante :

Exemple 27

SELECT FAC_ID,
sum (LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100)) AS TOTAL_FAC,
sum((LIF_QTE * (LIF_MONTANT - LIF_REMISE_MONTANT) * (1 - LIF_REMISE_POURCENT / 100))
* (LIF_TAUX_TVA / (100 +   LIF_TAUX_TVA))) AS TOTAL_TAXES
FROM T_LIGNE_FACTURE
WHERE FAC_ID = 3
GROUP BY FAC_ID

FAC_ID  TOTAL_FAC  TOTAL_TAXES
------  ---------  -----------
3       NULL         NULL

On constate que pour les lignes qui n'ont pas de valeurs renseignées dans les colonnes LIF_REMISE_POURCENT, LIF_REMISE_MONTANT, le résultat du calcul donne la valeur « null » qui se traduit à l'affichage par… rien !

NOTA : en général, pour se sortir de ce mauvais pas, on peut, lors de la création de la base de données, obliger tous les champs de type numérique (réels ou entiers) a ne pas accepter la valeur nulle et prendre par défaut la valeur zéro…

Attention : l'arithmétique des nuls est assez particulière… Souvenez-vous toujours que les NULL se propagent. Cela est vrai pour les numériques, les dates, mais aussi pour les chaines de caractères. Ainsi SQL opère une distinction entre une chaine de caractères vide et un champ non renseigné. Dans le cas de la concaténation d'une colonne NULL et d'une colonne proprement renseignée, la valeur renvoyée sera NULL !!!

5-2 - Opérateurs de traitement des marqueurs NULL

La norme SQL 2 (1992) spécifie une comparaison et différents opérateurs sur les marqueurs NULL :
IS NULL / IS NOT NULL : teste si la colonne est vide ou non vide.
COALESCE() qui recherche la première valeur non vide dans un ensemble
NULLIF NULLifie une colonne en fonction d'une valeur donnée

COALESCE ( valeur1, valeur2 [, valeur3] ... )
NULLIF ( nom_de_colonne, valeur)
expression IS [NOT] NULL

NOTA : ISNULL (en un seul mot) est une autre fonction de branchement que l'on rencontre parfois (renvoie une valeur si la valeur est nulle). Dans la même veine, NVL ou VALUE sont des expressions équivalentes à COALESCE que l'on rencontre sur certains SGBDR.

La requête précédente s'exprime, à l'aide de l'opérateur ISNULL :

Exemple 28

SELECT FAC_ID,
sum (LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0))
* (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100)) AS TOTAL_FAC,
sum((LIF_QTE * (LIF_MONTANT - ISNULL(LIF_REMISE_MONTANT, 0))
* (1 - ISNULL(LIF_REMISE_POURCENT, 0) / 100))
* (LIF_TAUX_TVA / (100 +   LIF_TAUX_TVA))) AS TOTAL_TAXES
FROM T_LIGNE_FACTURE
WHERE FAC_ID = 3
GROUP BY FAC_ID

FAC_ID  TOTAL_FAC  TOTAL_TAXES
------  ---------  -----------
3      810.0       127.03

NOTA : En règle générale, dès que l'on traite des colonnes contenant des valeurs monétaires ou numériques, il est bon de faire en sorte que la colonne soit obligatoire et que par défaut elle soit renseignée à zéro.
Sinon, il faudra faire un usage systématique des fonctions NULLIF ou COALESCE dans tous les calculs et cela grèvera les performances d'exécution !

6 - Négation de valeurs

C'est l'opérateur NOT qui réalise la négation de valeurs et inverse la valeur logique d'un prédicat.
L'opérateur NOT peut être combiné avec la plupart des opérateurs de comparaison. Mais il devient très intéressant lorsqu'il est combiné aux opérateurs IN, BETWEEN, LIKE et NULL

Recherchons par exemple toutes les chambres permettant de recevoir au moins trois personnes, ne comportant pas le chiffre 4 (chiffre de la mort au japon) ni les chambres portant les n° 7 et 13 pour un client particulièrement superstitieux…

Exemple 29

SELECT CHB_ID, CHB_NUMERO, CHB_COUCHAGE
FROM   T_CHAMBRE
WHERE  NOT (CAST(CHB_NUMERO AS VARCHAR(10)) LIKE '%4%')
AND CHB_NUMERO NOT IN ('7', '13')
AND CHB_COUCHAGE >= 3

CHB_ID      CHB_NUMERO CHB_COUCHAGE
----------- ---------- ------------
1           1          3
5           5          3
6           6          5
8           8          3
11          11         3
12          12         3
15          16         3
16          17         5
17          18         3
19          20         3

Nous verrons que le NOT IN est particulièrement précieux dans les requêtes imbriquées, c'est-à-dire les requêtes multitables.

Nous voulons maintenant le nom des clients qui ne commence pas par 'DU' :

Exemple 30

SELECT CLI_NOM
FROM T_CLIENT
WHERE CLI_NOM NOT LIKE 'DU%'

--------------------------------
MARTIN
BOUVIER
DREYFUS
FAURE
LACOMBE
BOYER
MARTIN
PAUL
PHILIPPE
PIERRELAYE
...

7 - Les branchements dans le SQL

Vous trouverez des compléments d'information sur le sujet aux pages 120 à 123 de l'ouvrage « SQL », collection « La Référence », Campus Press éditeur.

SQL possède un branchement à la manière des IF et autres structures de test des langages procéduraux.
Mais il convient de ne l'utiliser qu'à bon escient, c'est-à-dire aussi peu souvent que possible, beaucoup de cas pouvant être traités soit par le COALESCE soit par des requêtes avec des opérations ensemblistes de type UNION. En effet les performances se dégradent très vite lors de l'usage du CASE à cause de l'impossibilité d'effectuer des traitements par « paquets ».

La structure CASE du SQL comprend deux syntaxes différentes. Le CASE pour branchement sur les valeurs d'une expression et le CASE généralisé.

7-1 - CASE sur expression

Dans ce cas, la syntaxe est la suivante :

CASE expression
WHEN valeur1 THEN expression1
[WHEN valeur2 THEN expression2]
...
[ELSE expression_défaut]
END

Exemple 31

SELECT CHB_NUMERO, CASE CHB_ETAGE
WHEN 'RDC' THEN 0
WHEN '1er' THEN 1
WHEN '2e'  THEN 2
END AS ETAGE, CHB_COUCHAGE
FROM   T_CHAMBRE
ORDER  BY  ETAGE, CHB_COUCHAGE

CHB_NUMERO ETAGE       CHB_COUCHAGE
---------- ----------- ------------
2          0           2
3          0           2
4          0           2
1          0           3
7          1           2
9          1           2
10         1           2
5          1           3
8          1           3
11         1           3
12         1           3
6          1           5
14         2           2
15         2           2
19         2           2
21         2           2
16         2           3
18         2           3
20         2           3
17         2           5

7-2 - CASE généralisé

L'expression disparait au profit de différents prédicats.

CASE WHEN condition1 THEN expression1
[WHEN condition2 THEN expression2]
...
[ELSE expression_défaut]
END

Exemple 32

SELECT CHB_NUMERO, CASE WHEN CHB_ETAGE = 'RDC' THEN 0
WHEN CHB_ETAGE = '1er' THEN 1
WHEN CHB_ETAGE = '2e'  THEN 2
END AS ETAGE, CHB_COUCHAGE
FROM   T_CHAMBRE
ORDER  BY  ETAGE, CHB_COUCHAGE

CHB_NUMERO ETAGE       CHB_COUCHAGE
---------- ----------- ------------
2          0           2
3          0           2
4          0           2
1          0           3
7          1           2
9          1           2
10         1           2
5          1           3
8          1           3
11         1           3
12         1           3
6          1           5
14         2           2
15         2           2
19         2           2
21         2           2
16         2           3
18         2           3
20         2           3
17         2           5

Qui donne le même résultat !

ATTENTION : tous les SGBDR ne supportent pas ces deux syntaxes.

NOTA : dans les deux cas, il est possible de remplacer le IF d'un langage procédural :

CASE WHEN condition1 THEN expression1
[ELSE expression_défaut]
END

CASE expression
WHEN valeur1 THEN expression1
[ELSE expression_défaut]
END

8 - Le constructeur de lignes valuées (ROW VALUE CONSTRUCTOR)

Nous voici devant un élément peu connu du SQL, car rarement implanté. Rendons hommage à Oracle, car il est l'un des rares SGBDR à l'implémenter. Cette construction peut vous tirer d'embarras pour bien des cas en simplifiant drastiquement vos requêtes.

L'idée consiste à dire que différentes colonnes devant correspondre à différents critères comparées à l'aide d'un même opérateur de comparaison peuvent être évaluées simultanément.

Il est plus facile de comprendre ce concept à l'aide d'un exemple.

Un utilisateur crée une table T_EVENEMENT dans laquelle il a quatre colonnes indiquant l'heure, la minute, la seconde de survenance de l'événement et la nature de l'événement :

CREATE table T_EVENEMENT
(EVT_HEURE      int            NOT NULL,
EVT_MINUTE     smallint       NULL,
EVT_SECONDE    smallint       NULL,
EVT_EVENEMENT  varchar(64)    NOT NULL,
CONSTRAINT CKC_EVT_HEURE CHECK  (EVT_HEURE >= 0),
CONSTRAINT CKC_EVT_MINUTE CHECK  (EVT_MINUTE BETWEEN 0 AND 59),
CONSTRAINT CKC_EVT_SECONDE CHECK (EVT_SECONDE BETWEEN 0 AND 59))

INSERT INTO T_EVENEMENT VALUES (3, 12, 25, 'Surtension')
INSERT INTO T_EVENEMENT VALUES (3, 2,  48, 'Surchauffe')
INSERT INTO T_EVENEMENT VALUES (3, 15, 0,  'Arrêt')
INSERT INTO T_EVENEMENT VALUES (4, 0,  0,  'Incendie')
INSERT INTO T_EVENEMENT VALUES (2, 58, 59, 'Démarrage')
INSERT INTO T_EVENEMENT VALUES (4, 22, 33, 'Explosion')

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- --------------
2           58         59          Démarrage
3           2          48          Surchauffe
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion

La question est : quels sont les événements survenus après 3h 10 ?

Exemple 33 : intuitivement, nous sommes amenés à écrire la requête suivante :

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE >3 AND EVT_MINUTE >10

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- --------------
4           22         33          Explosion

Mais elle oublie sciemment trois événements survenus avant 3h10 ! Comment est-ce possible ?

En fait l'événement qui s'est produit à 4h 0 n'est pas pris en compte du fait de la première ligne de la clause WHERE !
EVT_HEURE > 3 AND EVT_MINUTE > 10
tout simplement parce que 0 minute est bien inférieur à 10 minutes…
Or 4h 0 est bien supérieur à 3h 10… il faut donc supprimer la partie AND EVT_MINUTE > 10 de la clause WHERE
La bonne construction est donc :

Exemple 34

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE > 3
OR EVT_HEURE = 3 AND EVT_MINUTE > 10

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion

D'où l'idée d'implémenter une construction capable d'évaluer globalement une telle opération. Cette construction est le ROW VALUE CONSTRUCTOR que j'ai traduit par constructeur de lignes valuées…

Avec un tel outil, la requête précédente s'exprime plus simplement :

Exemple 35

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE)  > (3, 10)

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           12         25          Surtension
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion

La syntaxe d'une telle expression utilisant un ROW VALUE CONSTRUCTOR est :

( <constructeur de lignes valuées> ) <comparateur> ( <constructeur de lignes valuées> )

Le constructeur de lignes valuées pouvant être : une liste de colonnes, une liste de valeurs, une liste combinant colonnes et valeurs ou encore une sous-requête :

- (colonne1, colonne2, colonne3)

- (colonne1, valeur1, valeur2, colonne2)

- (valeur1, valeur2)

- SELECT * FROM Matable à condition de ne retourner qu'une seule ligne

etc.

L'équivalent entre cette construction et celle n'utilisant pas le ROW VALUE CONSTRUCTOR, peut être développé comme suit.
Supposons que le premier constructeur de lignes valuées comporte des colonnes de tables de 1 à n et que le second constructeur de ligne valuées comporte des valeurs de 1 à n, c'est-à-dire :

(colonne1, colonne2, ... colonneN) <comparateur> (valeur1, valeur2, ... valeurN)

alors cette comparaison est équivalente à :

colonne1 <comparateur> valeur1
OR colonne1 = valeur1 AND colonne2 <comparateur> valeur2
OR colonne1 = valeur1 AND colonne2 = valeur2 AND colonne3 <comparateur> valeur3
...
OR colonne1 = valeur1 AND colonne2 = valeur2 AND ... AND colonneN <comparateur> valeurN

Toujours basé sur notre jeu d'essais, voici un exemple plus complet :: cherchons tous les événements survenus après 3h12'30" :

Exemple 36

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE)  > (3, 12, 30)

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion

Exemple 37 : et sa construction logiquement équivalente :

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE > 3
OR EVT_HEURE = 3 AND EVT_MINUTE > 12
OR EVT_HEURE = 3 AND EVT_MINUTE = 12 AND EVT_SECONDE > 30

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- -------------
3           15         0           Arrêt
4           0          0           Incendie
4           22         33          Explosion

En fait le ROW VALUE CONSTRUCTOR agit, dans les recherches, un peu comme si l'on visualisait les données de manière fractale
Le plus grossier est traité, puis on s'intéresse à quelques données plus fines une fois le grossier fixé, et ainsi de suite en fonction du niveau fractal que l'on s'est fixé.

NOTA : l'utilisation du ROW VALUE CONSTRUCTOR avec un critère de comparaison d'égalité donne parfois des résultats surprenants, qui peuvent défier la logique. Il faut se méfier d'un recours systématique à une telle construction.

Exemple 38 : quels sont les événements survenus à 3h 12 (en utilisant le ROW VALUE CONSTRUCTOR)

SELECT *
FROM T_EVENEMENT
WHERE (EVT_HEURE, EVT_MINUTE, EVT_SECONDE)  = (3, 12)

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- ---------------
3           12         25          Surtension
3           2          48          Surchauffe
3           15         0           Arrêt

Ce qui offre peut d'intérêt il faut bien le dire !

La construction logique équivalente étant :

Exemple 39

SELECT *
FROM T_EVENEMENT
WHERE EVT_HEURE = 3
OR EVT_HEURE = 3 AND EVT_MINUTE = 12

EVT_HEURE   EVT_MINUTE EVT_SECONDE EVT_EVENEMENT
----------- ---------- ----------- ---------------
3           12         25          Surtension
3           2          48          Surchauffe
3           15         0           Arrêt

ENFIN, notez que le ROW VALUE CONSTRUCTOR possède un intérêt immense, celui de permettre l'insertion multiple (plusieurs lignes de données) au sein du même ordre INSERT !

Exemple 40 : insertion multiple avec le ROW VALUE CONSTRUCTOR

INSERT INTO T_EVENEMENT (EVT_HEURE, EVT_MINUTE, EVT_SECONDE, EVT_EVENEMENT
VALUES ((4, 37, 21, 'Catastrophe'),
(4, 44, 18, 'Cataclisme') ,
(5, 21, 1, 'Destruction finale planète terre'))

ATTENTION : seul ORACLE et quelques SGBDR plus expérimentaux que professionnels (OCELOT par exemple) utilisent une telle technique !

9 - Résumé

Voici les différences entre les moteurs des bases de données :(1)(2)(3)

Fonctions \ SGBDR

Paradox 9

Access 2000

Sybase Adaptive

SQL Server 7

Oracle 8

|| (concaténation)

Oui

Non (&)

Non (+)

Non (+)

Oui

CAST

Oui

Non

Non (CONVERT)

Oui

Non (TO_CHAR / TO_NUM / TO_DATE)

LIKE / ESCAPE / % / _

Oui/Oui/Oui/Oui

Oui/Non/Non/Non

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

TRIM

Oui

Non (LTRIM, RTRIM)

Non (LTRIM, RTRIM)

Non (LTRIM, RTRIM)

Non (LTRIM, RTRIM)

SUBSTRING

Oui

Non (MID)

Non (ODBC SUBSTRING)

Non (ODBC SUBSTRING)

Non (SUBSTR)

LOWER / UPPER

Oui/Oui

Non (UCASE) / Non (LCASE)

Oui/Oui

Oui/Oui

Oui/Oui

EXTRACT

Oui

Non

Non (DATEPART)

Non (DATEPART)

Non

CURRENT_DATE / CURRENT_TIME / CURRENT_TIMESTAMP

Non/Non/Non (TODAY, NOW en QBE)

Non (NOW)

Non (GETDATE)

Non (GETDATE() / CURRENT_TIMESTAMP)

Non (SYSDATE)

AVG / MAX / MIN / SUM / COUNT

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

Oui/Oui/Oui/Oui

VARIANCE / STDEV

Non/Non

Non/Non

Non/Non

Non/Non

Oui/Oui

ABS / MOD / SIGN / SQRT / PI

Non/Non/Non/Non/Non

Oui /Non/ Non (SGN) / Non (SQR) / Non

Oui/Non (%)/Oui/Oui/Oui

Oui/Non (%)/Oui/Oui/Oui

Oui/Oui/Oui/Oui/Non

CEIL / FLOOR / ROUND / TRUNC

Non/Non/Non/Non

Non/Non (INT)/Non/Non

Non/Oui/Oui/Non

Non/Oui/Oui/Non

Oui/Oui/Oui/Oui

EXP / LN / LOG / POWER

Non/Non/Non/Non

Oui/Non/Oui/Non

Oui/Non/Oui/Oui

Oui/Non/Oui/Oui

Oui/Oui/Non (LOG (M, n))/Oui

COS / SIN / TAN

Non/Non/Non

Oui/Oui/Oui

Oui/Oui/Oui

Oui/Oui/Oui

Oui/Oui/Oui

COSH / SINH / TANH

Non/Non/Non

Non/Non/Non

Non/Non/Non

Non/Non/Non

Oui/Oui/Oui

INITCAP / LPAD / RPAD / REPLACE

Non/Non/Non/Non

Non/Non/Non/Non

Non/Non/Non/Non

Non/Non/Non/Oui

Oui/Oui/Oui/Oui

SOUNDEX / TRANSLATE / INSTR / LENGTH

Non (COMME dans QBE) / Non / Non / Non

Non / Non / Non (LOCATE) / Non

Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN)

Oui / Non (REPLACE) / Non (PATINDEX) / Non (LEN)

Oui / Oui / Non (LOCATE) / Non

TO_CHAR pour numérique / pour date

Non/Non

Non/Non

Non (CONVERT) / Non (CONVERT)

Non (CONVERT) / Non (CONVERT)

Non (auto )/ Non

ASCII / CHR

Non/Non

Non/Non

Oui / Non (CHAR)

Oui / Non (CHAR)

Oui/Oui

ADD_MONTH / MONTH_BETWEEN

Non/Non

Non (DATEADD) / Non (DATEDIFF)

Non (DATEADD) / Non (DATEDIFF)

Non (DATEADD) / Non (DATEDIFF)

Oui/Oui

LAST_DAY / NEXT_DAY

Non/Non

Non/Non

Non/Non

Non/Non

Oui/Oui

GREATEST / LEAST / DECODE

Non/Non/Non

Non/Non/Non

Non/Non/Non

Non/Non/Non

Oui/Oui/Non

IS NULL / COALESCE / NULLIF

Oui/Non/Non

Oui / Non / Non (ISNULL)

Oui / Non / Non (ISNULL)

Oui/Oui/Oui

Oui / Non / Non (NVL)

ROW VALUE CONSTRUCTOR

Non

Non

Non

Non

Oui

10 - Conclusion

Curieusement Paradox n'a pas implémenté les opérateurs de récupération des valeurs temporelles courantes alors qu'ils existent en QBE ! Il est facile de s'en passer en passant la date ou l'heure courante en paramètre de la requête, mais tout de même…

En ce qui concerne MS Access, on ne peut qu'être frappé par le fait que la plupart des fonctions de base des requêtes sont incompatibles avec la norme. Par exemple le LIKE utilise des jokers différents : * remplace le % et ? remplace le _. Cela oblige à utiliser une syntaxe propriétaire qui rend la portabilité des requêtes très difficile d'un SGBDR à l'autre. Mais ne serait-ce pas là une tactique voulue ??? Autre inconvénient il ne sait pas traiter le NOT BETWEEN !!!

Plus curieux la plupart des SGBDR n'acceptent pas l'opérateur de concaténation ||!

Dans Sybase comme SQL Server la fonction modulo s'exprime sous la forme d'un caractère '%' d'où d'énormes possibilités de confusion entre les caractères joker du like, comme le calcul de pourcentage… À quand une release sur ce sujet ???

Le SGBDR le plus proche de la norme est celui de Sybase, suivi de SQL Server. Le plus complet par son jeu de fonctions est sans doute Oracle.

(1) Contrairement à la norme, l'opérateur like d'Access comme celui de SQL Server, ne fait aucune différence entre les majuscules et les minuscules (sauf paramétrage spécifique du serveur). Quant à l'opérateur « escape », il n'est tout simplement pas implémenté dans Access, mais il semble possible de le contourner en utilisant une syntaxe à base de jokers spécifique à Access…. Les jokers d'Access ne sont pas standards : * remplace le % et ? remplace le _.

(2) La tangente hyperbolique est bien implémentée, mais elle ne donne apparemment pas les bons résultats... !

(3) La conversion est automatique dans Oracle