Jointures et autres requêtes sql

Dans notre base de données PostgreSQL de test, nous créons deux tables à l'aide des instructions SQL suivantes:

CREATE TABLE opération
(jour CHAR(8),
 seq SMALLINT,
 montant NUMERIC(13,2),
 contrepartie CHAR(5),
 PRIMARY KEY (jour, seq))
;
CREATE TABLE contrepartie
(contrepartie CHAR(5) PRIMARY KEY,
 libellé VARCHAR(50))
;

et nous y plaçons quelques données, données que nous pouvons facilement retrouver grâce à des requêtes SQL élémentaires, exécutées par exemple dans un terminal psql:

Nous aimerions maintenant faire apparaître à la suite de chaque opération le libellé de la contrepartie. Pour ce faire nous devons réaliser une jointure entre les deux tables. Diverses possibilités s'offrent à nous.
Jointure intérieure (INNER JOIN):

bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé
bdtest-> FROM opération a INNER JOIN contrepartie b
bdtest-> ON a.contrepartie = b.contrepartie
bdtest-> ORDER BY a.jour, a.seq
bdtest-> ;
   jour   | seq | montant |    libellé     
----------+-----+---------+----------------
 2011-255 |   2 | -400.00 | Epargne #1
 2011-255 |   3 | -123.29 | Grande Surface
 2011-255 |   4 | -150.00 | Alphonse
 2011-256 |   2 |   57.49 | Epargne #2
(4 lignes)

Le mot INNER est facultatif car il s'agit du type de jointure par défaut.
Les deux colonnes qui sont jointes portent le même nom. Dans ce cas, nous aurions pu arriver au même résultat avec le query suivant:

SELECT a.jour, a.seq, a.montant, b.libellé
FROM opération a NATURAL JOIN contrepartie b
ORDER BY a.jour
;

Un problème se pose: toutes les opérations ne sont pas reprises.
Nous devons donc utiliser un autre type de jointure:
Jointure extérieure gauche (LEFT OUTER JOIN):

bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé
bdtest-> FROM opération a NATURAL LEFT OUTER JOIN contrepartie b
bdtest-> ORDER BY a.jour, a.seq
bdtest-> ;
   jour   | seq | montant |    libellé     
----------+-----+---------+----------------
 2011-255 |   1 | 1460.45 | 
 2011-255 |   2 | -400.00 | Epargne #1
 2011-255 |   3 | -123.29 | Grande Surface
 2011-255 |   4 | -150.00 | Alphonse
 2011-256 |   1 |  -48.00 | 
 2011-256 |   2 |   57.49 | Epargne #2
 2011-257 |   1 |  -28.27 | 
(7 lignes)

Lorsque la colonne a.contrepartie contient une valeur qui n'existe pas dans la table contrepartie, le système joint à la rangée concernée de la table opération une rangée (fictive) de la table contrepartie dont tous les champs sont à la valeur NULL. En ajoutant une clause WHERE au query précédent (et en modifiant aussi la clause SELECT), nous pouvons établir une liste des contreparties dont le libellé manque:

bdtest=> SELECT DISTINCT a.contrepartie
bdtest-> FROM opération a NATURAL LEFT OUTER JOIN contrepartie b
bdtest-> WHERE b.contrepartie IS NULL
bdtest-> order by a.contrepartie
bdtest-> ;
 contrepartie 
--------------
 autre
 EL   
 RN   
(3 lignes)

D'autres queries conduisent au même résultat:

SELECT DISTINCT a.contrepartie
FROM opération a
WHERE NOT EXISTS
(SELECT *
 FROM contrepartie b
 WHERE b.contrepartie = a.contrepartie)
ORDER by a.contrepartie
;

ou encore:

SELECT DISTINCT contrepartie
FROM opération 
WHERE contrepartie NOT IN
(SELECT contrepartie
 FROM contrepartie )
ORDER by contrepartie
;

Nous n'avons pas épuisé toutes les possibilités en ce qui concerne les types de jointures:
Jointure externe droite (RIGHT OUTER JOIN)

bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé
bdtest-> FROM opération a NATURAL RIGHT OUTER JOIN contrepartie b
bdtest-> ORDER BY a.jour
bdtest-> ;
   jour   | seq | montant |    libellé     
----------+-----+---------+----------------
 2011-255 |   2 | -400.00 | Epargne #1
 2011-255 |   3 | -123.29 | Grande Surface
 2011-255 |   4 | -150.00 | Alphonse
 2011-256 |   2 |   57.49 | Epargne #2
          |     |         | Magasin
(5 lignes)

Cette fois tous les libellés de la table de droite font partie du résultat, qu'il y ait ou non des opérations correspondantes.
Et enfin...
Jointure  complète (FULL JOIN)

bdtest=> SELECT a.jour, a.seq, a.montant, b.libellé
bdtest-> FROM opération a  NATURAL FULL JOIN contrepartie b
bdtest-> ORDER BY a.jour
bdtest-> ;
   jour   | seq | montant |    libellé     
----------+-----+---------+----------------
 2011-255 |   1 | 1460.45 | 
 2011-255 |   2 | -400.00 | Epargne #1
 2011-255 |   3 | -123.29 | Grande Surface
 2011-255 |   4 | -150.00 | Alphonse
 2011-256 |   1 |  -48.00 | 
 2011-256 |   2 |   57.49 | Epargne #2
 2011-257 |   1 |  -28.27 | 
          |     |         | Magasin
(8 lignes)

Répartition sur deux colonnes des crédits et des débits
Dans la table opération tous les montants sont dans une seule colonne. Il est possible de les afficher sur deux colonnes, une réservée aux crédits et une autre pour les débits:

bdtest=> SELECT jour, seq, libellé,
bdtest-> CASE  
bdtest->  WHEN montant > 0 THEN
bdtest->  montant 
bdtest-> ELSE 0
bdtest-> END AS credit,
bdtest-> CASE  
bdtest->  WHEN montant < 0 THEN
bdtest->  -montant 
bdtest-> ELSE 0
bdtest-> END AS debit
bdtest-> FROM opération NATURAL LEFT OUTER JOIN contrepartie
bdtest-> ORDER BY jour;
   jour   | seq |    libellé     | credit  | debit  
----------+-----+----------------+---------+--------
 2011-255 |   1 |                | 1460.45 |      0
 2011-255 |   2 | Epargne #1     |       0 | 400.00
 2011-255 |   3 | Grande Surface |       0 | 123.29
 2011-255 |   4 | Alphonse       |       0 | 150.00
 2011-256 |   1 |                |       0 |  48.00
 2011-256 |   2 | Epargne #2     |   57.49 |      0
 2011-257 |   1 |                |       0 |  28.27
(7 lignes)

bdtest=> \w opération.sql

Après exécution de la requête, nous avons écrit celle-ci dans le fichier opération.sql en vue d'un usage ultérieur.
Exportation vers un tableur
L'output de la requête ci-dessus peut-être redirigé vers un fichier et formaté de manière telle que les données puissent être facilement exportées vers un tableur. Pour ce faire, il suffit d'utiliser dans notre terminal psql les meta commandes suivantes:
\pset numericlocale (pour avoir par exemple 1.460,45 au lieu de 1460.45)
\a pour passer au mode de sortie non aligné
\t pour afficher uniquement les rangées (sans les en-têtes)
\f ';' pour initialiser le séparateur de champ à ';'
\o [FICHIER] pour envoyer le résultat de la requête vers le fichier FICHIER
\i  [FICHIER] pour exécuter les commande du fichier FICHIER (ce n'est évidemment pas le même)

bdtest=> \pset numericlocale
Affichage de la sortie numérique adaptée à la locale.

bdtest=> \a

Le format de sortie est unaligned.
bdtest=> \t
Affichage des tuples seuls.
bdtest=> \f ';'
Le séparateur de champs est « ; ».
bdtest=> \o bilan.csv
bdtest=> \i opération.sql
bdtest=> 

Ouvrons bilan.csv dans un tableur.
Rapidement nous arrivons à ceci:

Bilans comme résultat de requêtes

L'idéal pour obtenir un bilan est d'utiliser un tableur. Il est également possible d'établir un rapport depuis un outil comme libreoffice base.

Cependant de simples requêtes permettent d'arriver à de bons résultats.

Ainsi:

SELECT jour, seq, libellé,
CASE  
 WHEN montant > 0 THEN
 montant 
 ELSE 0
END AS credit,
CASE  
 WHEN montant < 0 THEN
 -montant 
 ELSE 0
END AS debit,
montant AS solde
FROM opération NATURAL LEFT OUTER JOIN contrepartie
UNION
SELECT 'Total' AS jour, 0 AS seq, '' AS libellé,
sum(credit) AS credit,
sum(debit) AS debit,
sum(solde) AS solde
FROM (SELECT
CASE  
 WHEN montant > 0 THEN
 montant 
 ELSE 0
END AS credit,
CASE  
 WHEN montant < 0 THEN
 -montant 
 ELSE 0
END AS debit,
montant AS solde
FROM opération NATURAL LEFT OUTER JOIN contrepartie
) a
ORDER BY jour, seq
;

nous donne (on a laissé numericlocale activé):

Ce query:

SELECT 
jour, 
sum(credit) AS credit,
sum(debit) AS debit,
sum(solde) as solde
FROM (SELECT jour,
CASE  
 WHEN montant > 0 THEN
 montant 
END AS credit,
CASE  
 WHEN montant < 0 THEN
 -montant 
END AS debit,
montant AS solde
FROM opération) a
GROUP BY jour
UNION
SELECT 
'Total' AS jour,
sum(credit) AS credit,
sum(debit) AS debit,
sum(solde) AS solde
FROM (SELECT
CASE  
 WHEN montant > 0 THEN
 montant 
END AS credit,
CASE  
 WHEN montant < 0 THEN
 -montant 
END AS debit,
montant AS solde
FROM opération) a
ORDER BY jour
;

conduit au résultat: