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

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.

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

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

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.

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

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…

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

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 !=

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.

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

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.

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

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

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…

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.

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-1. Extraire un paramètre temporel d'une date▲

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

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

4-5-2. Heure et date courante▲

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

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.

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

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.

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

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 !!!

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 !

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

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