Jointures entre les tables

Jusqu'à présent nos requêtes n'ont concerné qu'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 que les différentes lignes soient gérées en même temps. Une requête qui accède à de multiples lignes de la même table ou de différentes tables au même instant est appelée join. Par exemple, vous voulez lister tous les enregistrements weather ensemble avec leur ville associée. Pour ça, vous avez besoin de comparer la colonne ville de chaque ligne de la table weather avec le nom de colonne de toutes les lignes de la table cities, et sélectionner les lignes où ces valeurs existent.

C'est seulement un modèle conceptuel. La jointure peut être gérée de manière plus efficace, mais c'est invisible pour l'utilisateur.

SELECT *
    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 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)

Nous observons ainsi deux choses :

• Il n'y a pas de résultat pour la ville de Hayward. C'est parce qu'il n'y a pas d'entrée correspondante dans la table cities pour Hayward, ainsi la jointure ignore les lignes non significatives dans la table weather. Nous verrons rapidement comment ceci peut être résolu.

• Il y a deux colonnes contenant un nom de ville. Ceci est correct car la liste des colonnes des tables weather et cities sont concaténées. En pratique ceci est indésirable, vous voudrez sans doute afficher les colonnes de façon plus explicite plutôt que d'utiliser le *:

  SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

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

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

Les requêtes de jointure de cette sorte peuvent aussi être écrites de façon alternative :

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

Maintenant, voyons comment nous pouvons obtenir les enregistrements de Hayward. Nous voulons que la requête recherche dans la table weather et pour chaque ligne trouve les lignes appariées dans cities. Si aucune ligne appariée n'est trouvée nous voulons que "champ vide" soit substitué au nom de colonnes de la table cities. Ce genre de requête est appelée outer join. (Les jointures que nous avons vu plus loin sont des inner joins). La commande ressemblera à ça :

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 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)

Nous pouvons aussi joindre une table sur elle-même. Ceci est appelé self join. Par exemple, supposons que nous voulons tous les enregistrements de weather qui sont dans un domaine de temperature d'autres enregistrements weather. Nous devons comparer les colonnes temp_lo et temp_hi de chaque ligne weather aux colonnes temp_lo et temp_hi de toutes les lignes weather. On peut faire celà avec la requête suivante :

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

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

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;