comment écrire la jointure externe ?

**DiverSIG** · 05/07/2011, 11h54

Bonjour,

Je fais une requête sur ma base de données qui me renvoie des moyennes journalières de différentes valeurs stockées dans ma base :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT str.str_date AS d, AVG(mss.mss_valeur) AS moy, STDDEV(mss.mss_valeur) AS ec, COUNT(str.str_date) AS c 
FROM m_stries mss, stries str 
WHERE mss.mss_parametre = 1 
AND EXTRACT(YEAR FROM str.str_date) = 1990 
AND mss.mss_individu = str.str_individu 
AND mss.mss_strie = str.str_strie 
GROUP BY str.str_date 
ORDER BY str.str_date

Dans le résultat que j'obtiens, je n'ai pas toutes les dates de l'année 1990 (normal, il n'y a pas de valeurs à moyenner pour toutes les dates).
Comme je veux une table résultat avec toutes les dates, j'ai crée dans ma base une table calendrier avec une variable qui contient les dates de tous les jours.
Et mon idée est de faire une jointure externe entre cette table et le résultat de ma requête, mais je ne vois pas comment l'écrire... parce que dans toutes les syntaxes que j'ai vues, le LEFT INNER JOIN travaille sur 2 tables 'en dur', et pas sur le résultat d'une requête.

J'ai essayé quelque chose comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT ca.cal_date, str.str_date AS d, AVG(mss.mss_valeur) AS moy, STDDEV(mss.mss_valeur) AS ec, COUNT(str.str_date) AS c 
FROM calendrier ca, m_stries mss, stries str
LEFT OUTER JOIN calendrier cal
ON cal.cal_date = str.str_date
WHERE mss.mss_parametre = 1 
AND EXTRACT(YEAR FROM ca.cal_date) = 1990 
AND mss.mss_individu = str.str_individu 
AND mss.mss_strie = str.str_strie 
GROUP BY ca.cal_date, str.str_date 
ORDER BY str.str_date

mais ça mouline, et ça finit en message d'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ERROR: could not write block 68560 of temporary file: No space left on device
SQL state: 53100
Hint: Perhaps out of disk space?

une idée ?

Merci,
Nico

**Waldar** · 05/07/2011, 11h59

Quel est votre SGBD ?

**DiverSIG** · 05/07/2011, 12h05

PostgreSQL 8.4.8

Nico

**Waldar** · 05/07/2011, 12h17

Essayez ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  SELECT ca.cal_date
       , str.str_date           AS d
       , AVG(mss.mss_valeur)    AS moy
       , STDDEV(mss.mss_valeur) AS ec
       , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN stries str
         INNER JOIN m_stries mss
           ON mss.mss_individu  = str.str_individu 
          AND mss.mss_strie     = str.str_strie
          AND mss.mss_parametre = 1 
           ON str.str_date      = ca.cal_date
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

**DiverSIG** · 05/07/2011, 14h14

ça marche super, merci !
Nico

**StringBuilder** · 27/09/2011, 08h53

Pour répondre à l'intervention de Waldar dans le topic du classement de ligue 1 :

En quoi cette syntaxe est nécessaire ?
Pourquoi ne pas utiliser celle-ci ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT ca.cal_date
       , str.str_date           AS d
       , AVG(mss.mss_valeur)    AS moy
       , STDDEV(mss.mss_valeur) AS ec
       , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN stries str ON str.str_date      = ca.cal_date
         LEFT OUTER JOIN m_stries mss
           ON mss.mss_individu  = str.str_individu 
          AND mss.mss_strie     = str.str_strie
          AND mss.mss_parametre = 1 
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

Je ne vois pas du tout quelle différence il peut y avoir entre les deux syntaxe (??)

Hmmmm ok, je vois un cas particulier où effectivement, ce n'est pas équivalent.

Si str contient un NULL dans la référence à mss, alors ta syntaxe vire la ligne aussi de str lors de la jointure avec ca.

Ceci dit, pour plus de clareté, je mettrait des parenthèse et j'indenterais (en effet, cette syntaxe s'apparente au fonctionnenent d'une sous requête (syntaxe ci-dessous) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT ca.cal_date
         , str.str_date           AS d
         , AVG(mss.mss_valeur)    AS moy
         , STDDEV(mss.mss_valeur) AS ec
         , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN (
                  stries str
                  INNER JOIN m_stries mss 
                  ON mss.mss_individu  = str.str_individu
                  AND mss.mss_strie     = str.str_strie
                  AND mss.mss_parametre = 1 
         )
         ON str.str_date      = ca.cal_date
WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

Avec une sous-requpête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT ca.cal_date
         , str.str_date           AS d
         , AVG(mss.mss_valeur)    AS moy
         , STDDEV(mss.mss_valeur) AS ec
         , COUNT(str.str_date)    AS c
    FROM calendrier ca
         LEFT OUTER JOIN (
                  select st2.str_date
                  from stries st2
                  INNER JOIN m_stries mss 
                  ON mss.mss_individu  = st2.str_individu 
                  AND mss.mss_strie     = st2.str_strie
                  AND mss.mss_parametre = 1 
         ) str
         ON str.str_date      = ca.cal_date
   WHERE ca.cal_date >= date '1990-01-01'
     AND ca.cal_date <  date '1991-01-01'
GROUP BY ca.cal_date
       , str.str_date 
ORDER BY ca.cal_date ASC

PS : J'ai pas bien compris pourquoi AND mss.mss_strie = str.str_strie AND mss.mss_parametre = 1 était dans le second ON, pour moi ça doit être dans le premier, non ?

**CinePhil** · 27/09/2011, 09h11

Pour une fois je suis d'accord avec toi StringBuilder !

Je trouve l'imbrication des jointures (à la manière d'Access par exemple) de la requête de Waldar beaucoup moins claire que lorsqu'on fait se succéder les jointures complètes.

Une remarques quand même sur la requête :
Plutôt que >= et <, il vaut mieux utiliser BETWEEN, en faisant attention aux bornes qui sont incluses avec BETWEEN.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT ca.cal_date, 
    str.str_date AS d, 
    AVG(mss.mss_valeur) AS moy, 
    STDDEV(mss.mss_valeur) AS ec
    COUNT(str.str_date) AS c 
FROM calendrier ca
LEFT OUTER JOIN stries str ON str.str_date = ca.cal_date
    INNER JOIN m_stries mss
        ON mss.mss_individu  = str.str_individu 
        AND mss.mss_strie = str.str_strie
        AND mss.mss_parametre = 1 
WHERE ca.cal_date BETWEEN '1990-01-01' AND '1990-12-31'
GROUP BY ca.cal_date, str.str_date 
ORDER BY ca.cal_date ASC

**StringBuilder** · 27/09/2011, 09h20

J'ai édité mon post, il y a un cas où effectivement, la syntaxe de Waldar est nécessaire.
Mais dans ce cas, je trouve plus lisible de mettre entre parenthèses la seconde jointure et d'indenter : ça rappelle la syntaxe d'une jointure sur une sous-requête (et c'est d'ailleurs ni plus ni moins ce qu'on fait, de façon "propre" mais illisible).

**StringBuilder** · 27/09/2011, 09h32

Voici un script qui illustre les différentes syntaxes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
drop table a;
drop table b;
drop table c;
 
 
create table c (id int not null identity primary key, vc varchar(10) not null);
create table b (id int not null identity primary key, c_id int null references c (id), vb varchar(10) not null);
create table a (id int not null identity primary key, b_id int null references b (id), va varchar(10) not null);
 
insert into c (vc) values ('C1');
insert into c (vc) values ('C2');
 
insert into b (c_id, vb) values (1, 'B1');
insert into b (c_id, vb) values (2, 'B2');
insert into b (c_id, vb) values (null, 'B3');
 
insert into a (b_id, va) values (1, 'A1');
insert into a (b_id, va) values (2, 'A2');
insert into a (b_id, va) values (3, 'A3');
insert into a (b_id, va) values (null, 'A4');
 
select a.va, b.vb, c.vc
from a
left outer join b on b.id = a.b_id
inner join c on c.id = b.c_id;
 
select a.va, b.vb, c.vc
from a
left outer join b on b.id = a.b_id
left outer join c on c.id = b.c_id;
 
select a.va, b.vb, c.vc
from a
left outer join 
(
	b
	inner join c on c.id = b.c_id
)
	on b.id = a.b_id;
 
select a.va, b2.vb, b2.vc
from a
left outer join 
(
	select b.id, b.vb, c.vc
	from b
	inner join c on c.id = b.c_id
) b2
	on b2.id = a.b_id;

La première : INNER JOIN entre B et C : vu que B ne référence pas toujours C, on perd des lignes de A : PAS BON

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2

La seconde : OUTER JOIN entre B et C : vu que A ne référence pas toujours B et que B ne référence pas toujours C, on a trop de lignes : on ne veux pas celles pour lesquelles B ne référence pas C

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2
A3         B3         NULL
A4         NULL       NULL

Les deux autres, qui sont équivalentes : Ca fait bien ce qu'on veut... Avec les parenthèses, on remarque à quel point elles sont similaires.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
va         vb         vc
---------- ---------- ----------
A1         B1         C1
A2         B2         C2
A3         NULL       NULL
A4         NULL       NULL

**CinePhil** · 27/09/2011, 09h32

C'est plus propre avec la sous-requête.

**StringBuilder** · 27/09/2011, 09h46

Les plans d'exécution (sous SQL Server) sont cependant différents. Il faudra voir (j'imagine que le choix du SGBD a un impact) laquelle est la plus rapide. J'imagine que la syntaxe sans sous-requête est meilleure.

**CinePhil** · 27/09/2011, 10h09

Mouais... des clés étrangères "NULLables" c'est pas terrible !

**StringBuilder** · 27/09/2011, 10h27

Bah c'est pour ça que les jointures externes existent

On peut aussi reprendre dans l'autre sens (et ça n'a pas plus de sens) : les éléments de listes de valeurs qui n'ont pas de référence.

Moins ça arrive, mieux on se porte, mais bon, des fois c'est quand même utile

**CinePhil** · 27/09/2011, 10h43

Envoyé par CinePhil

Mouais... des clés étrangères "NULLables" c'est pas terrible !

Envoyé par StringBuilder

Bah c'est pour ça que les jointures externes existent

Ça n'a rien à voir !
MCD :
personne -0,n----diriger----1,1- projet

Tables :
personne (prs_id...)
projet (prj_id, prj_id_chef...) <= clé étrangère non nulle.

Quelles sont les personnes qui n'ont jamais dirigé de projet ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT prs_id
FROM personne p
LEFT OUTER JOIN projet j ON j.prj_id_chef = p.prs_id
WHERE j.prj_id_chef IS NULL

Le NULL n'apparaîtrait dans le résultat qu'à cause de la jointure externe mais pas parce que la clé étrangère serait nullable !

**Waldar** · 27/09/2011, 17h55

Envoyé par CinePhil

Je trouve l'imbrication des jointures (à la manière d'Access par exemple) de la requête de Waldar beaucoup moins claire que lorsqu'on fait se succéder les jointures complètes.

L'imbrication a du sens, il n'y a pas de hasard en jeu.
Ce n'est pas plus complexe à comprendre qu'une jointure externe.
Effectivement, deux parenthèses en plus aident à la lisibilité, mais elles ne changent rien en terme d'exécution de la requête. Chacun fait comme il souhaite, du moment que les résultats retournés sont corrects !

Cette syntaxe permet d'être plus concis dans l'écriture de la requête.

Envoyé par CinePhil

Une remarques quand même sur la requête :
Plutôt que >= et <, il vaut mieux utiliser BETWEEN, en faisant attention aux bornes qui sont incluses avec BETWEEN.

BETWEEN est transformé en >= et <=, on le voit quand on profile les requêtes.
Comme un IN est transformé en une succession de OR.
En fait, ça permet juste d'être plus concis dans l'écriture de la requête.

... tient, ça me rappelle quelque chose !

En l'occurrence BETWEEN a parfaitement sa place ici, j'ai du penser à la présence ou nom des heures minutes secondes, mais comme il s'agit d'une table calendrier la logique voudrait une ligne par jour.
Donc bonne correction !

Envoyé par CinePhil

C'est plus propre avec la sous-requête.

Là pour le coup, je ne suis pas d'accord.
Vous le comprenez certes mieux, mais je ne vois pas en quoi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT a.va, b2.vb, b2.vc
  FROM a
       LEFT OUTER JOIN 
       (
	  SELECT b.id, b.vb, c.vc
            FROM b
                 INNER JOIN c ON c.id = b.c_id
       ) b2
	 ON b2.id = a.b_id;

est plus propre que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT a.va, b.vb, b.vc
  FROM a
       LEFT OUTER JOIN b
       INNER JOIN c
         ON c.id = b.c_id
         ON b.id = a.b_id;

Il y a plus de select, plus d'alias, si vous voulez rajouter une colonne dans le select final il faut allez aussi le rajouter dans la sous-requête.

C'est exactement la même chose que de remonter certains critères de jointures dans le FROM pour les jointures externes.
Ce n'est jamais obligatoire, c'est juste une simplification syntaxique qui répond à des règles précises !

Et si c'est un peu ambigu au début il suffit de rajouter des parenthèses comme l'a fait StringBuilder :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT a.va, b.vb, b.vc
  FROM a
       LEFT OUTER JOIN
       (
         b
         INNER JOIN c
           ON c.id = b.c_id
       )
         ON b.id = a.b_id;

On garde la facilité d'écriture et c'est visuellement plus parlant.

Dans le même registre je continue à mettre des parenthèses avec les prédicats OR et AND lorsqu'ils sont mélangés car je ne me souviens jamais de leur règle de priorité !

Envoyé par CinePhil

Mouais... des clés étrangères "NULLables" c'est pas terrible !

Ça arrive tout le temps ! Heureusement qu'elles sont nullables les clefs étrangères.
Pour reprendre votre exemple : on crée un nouveau projet, on lui affecte un responsable la semaine suivante.
Il n'y a pas de problème, et encore une fois, ça arrive tout le temps.

J'ai beaucoup cité Cinephil, rien de personnel bien entendu !

comment écrire la jointure externe ?

Langage SQL

Discussions similaires

Partager

Partager