2.6. Jointures entre les tables

Jusqu'ici, nos requêtes avaient seulement consulté une table à la fois. Les requêtes peuvent accéder à plusieurs tables en même temps ou accéder à la même table de façon à ce que plusieurs lignes de la table soient traitées en même temps. Une requête qui consulte plusieurs lignes de la même ou de différentes tables en même temps est appelée requête de jointure. Comme exemple, dites-vous que vous souhaitez lister toutes les entrées du temps avec la colonne des noms de toutes les lignes de la table des villes et que vous choisissez les paires de lignes où ces valeurs correspondent.

Ceci sera accompli avec la requête suivante :

SELECT *
    FROM temps, villes
    WHERE ville = nom;

     ville     | temp_basse | temp_haute | prcp |    date    |     nom       | emplacement
---------------+------------+------------+------+------------+---------------+-------------
 San Francisco |         46 |         50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |         43 |         57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

Remarquez deux choses à propos du résultat :

• Il n'y a pas de lignes pour la ville de Hayward dans le résultat. C'est parce qu'il n'y a aucune entrée correspondante dans la table villes pour Hayward, donc la jointure ignore les lignes n'ayant pas de correspondance avec la table temps. Nous verrons rapidement comment cela peut être résolu.

• Il y a deux colonnes contenant le nom des villes. Cela est correct car les listes de colonnes des tables temps et villes sont concaténées. En pratique, ceci est indésirable, vous voudrez probablement lister les colonnes explicitement plutôt que d'utiliser * :

  SELECT ville, temp_basse, temp_haute, prcp, date, emplacement
      FROM temps, villes
      WHERE ville = nom;

Puisque toutes les colonnes ont un nom différent, l'analyseur a automatiquement trouvé à quelle table elles appartiennent, mais c'est mieux de qualifier pleinement les noms des colonnes dans les requêtes de jointure :

SELECT temps.ville, temps.temp_basse, temps.temp_haute,
       temps.prcp, temps.date, villes.emplacement
    FROM temps, villes
    WHERE villes.nom = temps.ville;

Les requêtes de jointure vues jusqu'ici peuvent aussi être écrites dans un format alternatif :

SELECT *
    FROM temps INNER JOIN villes ON (temps.ville = villes.nom);

Cette syntaxe n'est pas aussi couramment utilisée que les précédentes mais nous la montrons ici pour vous aider à comprendre les sujets suivants.

Maintenant, nous allons essayer de comprendre comment nous pouvons avoir les entrées de Hayward. Nous voulons que la requête balaie la table temps et que, pour chaque ligne, elle trouve la ligne de villes correspondante. Si aucune ligne correspondante n'est trouvée, nous voulons que les valeurs des colonnes de la table villes soient remplacées par des << valeurs vides >>. Ce genre de requêtes est appelé jointure externe (outer join). (Les jointures que nous avons vus jusqu'ici sont des jointures internes -- inner joins.) La commande ressemble à cela :

SELECT *
    FROM temps LEFT OUTER JOIN villes ON (temps.ville = villes.nom);

     ville     | temp_basse | temp_haute | prcp |    date    |     nom       | emplacement
---------------+------------+------------+------+------------+---------------+-------------
 Hayward       |         37 |         54 |      | 1994-11-29 |               |
 San Francisco |         46 |         50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |         43 |         57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

Cette requête est appelée une jointure externe à gauche (left outer join) parce que la table mentionnée à la gauche de l'opérateur de jointure aura au moins une fois ses lignes dans le résultat tandis que la table sur la droite aura seulement les lignes qui correspondent à des lignes de la table de gauche. Lors de l'affichage d'une ligne de la table de gauche pour laquelle il n'y a pas de correspondance dans la table de droite, des valeurs vides (appelées NULL) sont mises pour les colonnes de la table de droite.

Nous pouvons également joindre une table avec elle-même. Ceci est appelé une jointure réflexive. Comme exemple, supposons que nous voulons trouver toutes les entrées de temps qui sont dans un intervalle de température d'autres entrées de temps. Nous avons donc besoin de comparer les colonnes temp_basse et temp_haute de chaque ligne de temps aux colonnes temp_basse et temp_haute de toutes les autres lignes de temps. Nous pouvons faire cela avec la requête suivante :

SELECT W1.ville, W1.temp_basse AS low, W1.temp_haute AS high,
    W2.ville, W2.temp_basse AS low, W2.temp_haute AS high
    FROM temps W1, temps W2
    WHERE W1.temp_basse < W2.temp_basse
    AND W1.temp_haute > W2.temp_haute;

     ville      | low | high |     ville     | low | high
----------------+-----+------+---------------+-----+------
  San Francisco |  43 |   57 | San Francisco |  46 |   50
  Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

Ici, nous avons renommé la table temps en W1 et en W2 pour être capable de distinguer le côté gauche et droit de la jointure. Vous pouvez aussi utiliser ce genre d'alias dans d'autres requêtes pour économiser de la frappe, c'est-à-dire :

SELECT *
    FROM temps w, villes c
    WHERE w.ville = c.nom;

Vous rencontrerez ce genre d'abréviation assez fréquemment.