LEFT JOIN sur tables identiques ? [Résolu]

[Cyborg]

Bonjour,

J'ai un petit soucis de requête SQL. Je suis gêné par le comportement par défaut du JOIN (avec Excel) : association automatique des champs de même nom.

Prenons une première table comprenant (ID_CLIENT, NOM), et une seconde table comprenant (ID_CLIENT, TYPE_NUMERO,NUMERO).

Code [Table CLIENT] :

1
2
3
ID_CLIENT | NOM
------------------
    1     | DUPONT

et

Code [Table TELEPHONE] :

1
2
3
4
ID_CLIENT | TYPE_NUMERO | NUMERO
------------------------------------
    1     | FIXE        | 0112345678
    1     | MOBILE      | 0612345678

Et je voudrais faire une requête me sortant :

Code [Table NUMERO] :

1
2
3
NOM    | NUMERO_FIXE | NUMERO_MOBILE
---------------------------------
DUPONT | 0112345678  | 0612345678

Sans les JOIN, c'est simple :

Code :

1
2
3
4
5
6
SELECT C.NOM, N1.NUMERO, N2.NUMERO
FROM CLIENT C, NUMERO N1, NUMERO N2
WHERE C.ID_CLIENT = N1.ID_CLIENT
AND C.ID_CLIENT = N2.ID_CLIENT
AND N1.TYPE_NUMERO = 'FIXE'
AND N2.TYPE_NUMERO = 'MOBILE'

L'équivalent avec un LEFT JOIN :

Code :

1
2
3
4
5
6
SELECT C.NOM, N1.NUMERO, N2.NUMERO
FROM (CLIENT C
 LEFT JOIN NUMERO N1
 ON C.ID_CLIENT = N1.ID_CLIENT)
 LEFT JOIN NUMERO N2
 ON C.ID_CLIENT = N2.ID_CLIENT

Sauf que si on passe par les JOIN, il associe automatiquement les champs identique. Donc non seulement mes conditions précisées en ON sont superflues. Mais en plus, il m'ajoute certainement un truc du genre :

Code :

1
2
3
WHERE N1.ID_CLIENT = N2.ID_CLIENT
AND N1.TYPE_NUMERO = N2.TYPE_NUMERO
AND N1.NUMERO = N2.NUMERO

Et voilà le résultat :

Code [Table NUMERO] :

1
2
3
4
NOM    | NUMERO_FIXE | NUMERO_MOBILE
---------------------------------
DUPONT | 0112345678  | 0112345678
DUPONT | 0612345678  | 0612345678

Est-ce que vous auriez une 'belle' solution à mon problème ?

Moi qui vient tout juste d'apprendre la manipulation du JOIN, et uniquement pour l'expliquer à quelqu'un d'autre, j'aurais tendance à me contenter d'un :

Lé JOIN cé d'la maird'hanb' Harr

[seabs]

Bonjour,

Moi qui vient tout juste d'apprendre la manipulation du JOIN, et uniquement pour l'expliquer à quelqu'un d'autre, j'aurais tendance à me contenter d'un :
Citation:
Lé JOIN cé d'la maird'hanb' Harr

C'est peut être aller un peu vite dans le jugement.

Ton SQL doit bien supporter l'instruction CASE alors, il convient de faire

Code :

1
2
3
4
5
SELECT C.NOM,
   CASE WHEN T.TYPE_NUMERO = 'FIXE' THEN T.NUMERO END AS NumeroFixe, 
   CASE WHEN T.TYPE_NUMERO = 'MOBILE' THEN T.NUMERO END AS NumeroMobile, 
FROM CLIENT C,
   LEFT JOIN TELEPHONE T ON C.ID_CLIENT = T.ID_CLIENT

Il est peut avantageux de remplacer LEFT JOIN par un INNER JOIN. Tout cela dépend de ton application.

Maintenant, il faut se poser la question si c'est le travail de la base de données de présenter les résultats en colonne. Pour ma part, je pense que c'est à l'applicatif d'effectuer la mise en forme.

Notre ami CinePhil te dirait, la cosmétique ce n'est pas le travail de ta base de données

@+

[tatayo]

Bonjour,
J'ajouterai que tu compares une requêtes sans JOIN, mais avec des critères de sélection sur le type de numéro, et une requête avec JOIN, mais sans les critères en question. Donc pas étonnant que le résultat soit légèrement différent...

Tatayo.

[Waldar]

seabs, n'oubliez pas l'agrégat sur les numéros, actuellement votre requête renvoie deux lignes.

[Rei Ichido]

Envoyé par Cyborg

Sans les JOIN, c'est simple :

Code :

L'équivalent avec un LEFT JOIN :

Code :

Perdu, l'équivalent avec les joins :

Code :

1
2
3
4
5
6
7
 
SELECT C.NOM, N1.NUMERO, N2.NUMERO
FROM CLIENT C
 LEFT JOIN NUMERO N1
 ON C.ID_CLIENT = N1.ID_CLIENT AND N1.TYPE_NUMERO = 'FIXE'
 LEFT JOIN NUMERO N2
 ON C.ID_CLIENT = N2.ID_CLIENT AND N2.TYPE_NUMERO = 'MOBILE'

[Cyborg]

Merci à seabs et Waldar pour vos réponses instructives.

Envoyé par seabs

Ton SQL doit bien supporter l'instruction CASE

Il semble supporter le "CASE" ... mais pas le "WHEN" !!

Code :

Mot clé non reconnu WHEN.

J'vais fouiller des conseils sur des forums SQL / VB, car ça me parait bizzare qu'il ne reconnaisse qu'une partie du CASE/WHEN THEN.
Une autre idée sinon ?

Envoyé par Waldar

seabs, n'oubliez pas l'agrégat sur les numéros, actuellement votre requête renvoie deux lignes.

Euh ... d'ailleurs, j'vois pas quoi agréger pour retirer les lignes en trop.

Envoyé par seabs

Maintenant, il faut se poser la question si c'est le travail de la base de données de présenter les résultats en colonne. Pour ma part, je pense que c'est à l'applicatif d'effectuer la mise en forme.

Ce sont des requêtes dont les bases sont des fichiers Excel (voir des dossiers de fichiers .csv). Le résultat est donc aussi un fichier Excel utilisé par quelqu'un d'autre. Et tout effectuer sous forme de requête est bien plus efficace que de manipuler des "Range" Excel.
J'utilise d'ailleurs des SELECT INTO pour m'affranchir de l'ouverture/fermeture des fichiers Excel.

Il est peut avantageux de remplacer LEFT JOIN par un INNER JOIN. Tout cela dépend de ton application.

En l'occurence, j'aurais aimé pousser le vice jusqu'à avoir la liste de mes clients, avec les différents numéros sur les colonnes adéquates, ou bien null si un numéro est manquant. Le INNER JOIN donne le même résultat que le FROM/WHERE. Le LEFT JOIN me raccourcit l'expression car pour un résultat identique avec les FROM/WHERE, il me faut faire l'UNION de plusieurs requêtes traitant de chacun de mes cas (N2.NUMERO IS NULL, etc).


Pour Rei Ichido et tatayo : Désolé, j'ai débauché plus d'une 1/2 heure en retard vendredi pour faire ce message, donc il y avait peut-être quelques imperfections dues à mon impatience à décoller. De plus, c'est une copie visuelle de code d'un PC à un autre (pas Internet sur mon PC où je code), d'où les oublis probables. Néanmoins, je pensais que mes explications étaint suffisamment claires pour avoir droit à une réponse à mon problème.

[Cyborg]

Envoyé par seabs

Ton SQL doit bien supporter l'instruction CASE

J'ai remplacé le

Code :

CASE WHEN T.TYPE_NUMERO = 'FIXE' THEN T.NUMERO END AS NumeroFixe

par

Code :

IIF(T.TYPE_NUMERO = 'FIXE',T.NUMERO,NULL) AS NumeroFixe

Et ça fonctionne parfaitement.

Il ne reste que l'erreur donnée par Waldar :

Envoyé par Waldar

n'oubliez pas l'agrégat sur les numéros, actuellement votre requête renvoie deux lignes.

Une idée ?

[Rei Ichido]

Grouper par id_client, et dans les fonctions mettre un MAX ou un MIN (selon la façon dont le SGBD trie les null) :

Code :

1
2
3
4
5
6
7
 
SELECT C.NOM,
MAX(IIF(T.TYPE_NUMERO = 'FIXE',T.NUMERO,NULL)) AS NumeroFixe,
MAX(IIF(T.TYPE_NUMERO = 'MOBILE',T.NUMERO,NULL)) AS NumeroMobile
FROM CLIENT C
         JOIN NUMERO T ON (C.ID_CLIENT = T.ID_CLIENT)
GROUP BY c.id_client

Edit : cela dit, je préfère largement la version à double jointure !

[Waldar]

Essayez le même besoin avec quinze attributs, comparez les syntaxes des requêtes ainsi que les performances et dites-moi ensuite lequel vous préférez !

[Cyborg]

Envoyé par Rei Ichido

cela dit, je préfère largement la version à double jointure !

Envoyé par Waldar

Essayez le même besoin avec quinze attributs, comparez les syntaxes des requêtes ainsi que les performances et dites-moi ensuite lequel vous préférez !

Avec le SQL au travers du VBA Excel, la question ne se pose pas, puisqu'il m'était impossible d'ajouter dans le JOIN

Code :

AND N1.TYPE_NUMERO = 'FIXE'

Code :

Expression JOIN non supportée

En revanche, si la vitesse de la requête sera certainement plus rapide avec la solution MAX(IFF()) (seulement 2 tables à joindre, le reste est réalisé avec des UNION au travers du IIF), c'est aussi un peu moins lisible pour quelqu'un reprenant le code, à mon goût.

Petite remarque : J'ai dû remplacer l'agrégation sur C.ID_CLIENT par C.NOM.

Code :

1
2
3
4
5
6
SELECT C.NOM,
MAX(IIF(T.TYPE_NUMERO = 'FIXE',T.NUMERO,NULL)) AS NumeroFixe,
MAX(IIF(T.TYPE_NUMERO = 'MOBILE',T.NUMERO,NULL)) AS NumeroMobile
FROM CLIENT C
         JOIN NUMERO T ON (C.ID_CLIENT = T.ID_CLIENT)
GROUP BY C.NOM

Sans cela, le moteur d'Excel m'envoyait bouler, à moins que je ne retire le C.NOM du SELECT.

En tout cas, merci à tous, cela fonctionne exactement comme je le souhaite à présent !! Et ça m'a permis d'intégrer en prime le fonctionnement du IIF, donc c'est tout bénef !!

[Rei Ichido]

Envoyé par Waldar

Essayez le même besoin avec quinze attributs, comparez les syntaxes des requêtes ainsi que les performances et dites-moi ensuite lequel vous préférez !

On en revient à une vieille discussion : selon le besoin initial, je vais préférer quelque chose qui me semble plus immédiatement compréhensible, tant que la différence de performance ne me semble pas flagrante.
Donc, oui, si la table à double jointure est vraiment énorme, ou s'il faut la caser 100 fois, je vais adopter une méthode efficace.

Et pour Cyborg : j'avais fait ça vite fait sans checker, my bad, c'est pourtant évident !

[punkoff]

Envoyé par Rei Ichido

On en revient à une vieille discussion : selon le besoin initial, je vais préférer quelque chose qui me semble plus immédiatement compréhensible, tant que la différence de performance ne me semble pas flagrante.
Donc, oui, si la table à double jointure est vraiment énorme, ou s'il faut la caser 100 fois, je vais adopter une méthode efficace.

Et pour Cyborg : j'avais fait ça vite fait sans checker, my bad, c'est pourtant évident !

Bonjour,

Il n'y a pas de discussion à avoir.

Vous aidez des personnes qui ont généralement une connaissance très réduite en SQL et vous leur inculquez des mauvaises pratiques.

Que ce passera-t-il quand ils devront reprendre toute leur application car leur requête ne seront plus performante ?

C'est une perte de temps et d'argent.

Combien de fois je me suis retrouvé devant des programmes batch totalement inutilisables car la personne qui l'avait développé avait justement utilisé ce qu'il pensait être lisible et non performant ?

Bref je trouve votre vision vraiment limitée, il faut savoir se remettre en question pour évoluer.

[Rei Ichido]

Envoyé par punkoff

Que ce passera-t-il quand ils devront reprendre toute leur application car leur requête ne seront plus performante ?

Si _toute_ l'application doit être reprise, c'est qu'il y a un souci à la base sur la mise en forme du problème, ou sur la prise en compte de l'accroissement de volume. À ce stade, quelles que soient les pratiques préconisées ici, ça aurait posé problème.

C'est une perte de temps et d'argent.

La question du temps et de l'argent se discute, justement, et dépend fortement de la persistance des ressources sur un projet donné.
Dans le cadre des projets avec fort turn-over et utilisation massive de débutants, est-ce qu'il vaut mieux passer 30% de développement en plus, et 30% sur toutes les opérations de maintenance et d'évolution pour le cas où un jour la volumétrie explose ?

Cela dit, j'accorde le point sur les bonnes pratiques à faire passer ici.

[pacmann]

Salut,

C'est pas non plus une usine à gaz... tu m'étonnes que tu aies un gros turnover dans ton équipe si tu considères que GROUP BY + max ne vaille pas la peine d'être expliqué parce que consommateur d'un peu de budget (genre 5 minutes pour lui expliquer le GROUP BY) !

Et puis surtout, ce type de question sur le "PIVOT" doit être dans le TOP 5 des questions les plus posées sur ce forum et ailleurs...

Bref, c'est un autre débat.

Et sinon Seabs : non, ce n'est pas du cosmétique, et si des gens te le disent, il faut pas le croire.
Je pense vraiment pas que tu préfères pour toutes les propriétés modélisées en clef - valeur (donc "en lignes") coder des boucles dans tous les sens au lieu de binder directement sur ta requête.

[Rei Ichido]

Envoyé par pacmann

Salut,

C'est pas non plus une usine à gaz... tu m'étonnes que tu aies un gros turnover dans ton équipe si tu considères que GROUP BY + max ne vaille pas la peine d'être expliqué parce que consommateur d'un peu de budget (genre 5 minutes pour lui expliquer le GROUP BY) !

Le problème ne se pose pas quand je suis là, mais plutôt quand il n'y a (plus) que des débutants. Et globalement, je prenais ce cas en exemple, mais ça se pose en général sur des requêtes un brin plus complexes - et encore une fois, il s'agit pour moi d'un facteur de choix qui ne vient pas remplacer le facteur performance, mais peut m'orienter si je sens qu'il y a plusieurs façons de faire.
Alors tu vas me dire, confrontés à un code qu'ils ne comprennent pas forcément bien, les débutants peuvent toujours venir ici, et ça va les former, etc. Personnellement je constate juste que ça génère plus de bugs qu'autre chose, vu que je dois repasser après voire être appelé en pompier.
=> Au final rien ne remplacera l'encadrement, et dans ce contexte là je vais faire l'effort de faire passer ce que je pense être de bonnes pratiques (je n'ai certainement pas la prétention d'être un expert SQL).

[gecodis]

Envoyé par Waldar

Essayez le même besoin avec quinze attributs, comparez les syntaxes des requêtes ainsi que les performances et dites-moi ensuite lequel vous préférez !

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT a.id_article
      ,MAX(CASE WHEN fk_critere = 1 THEN valeur END) AS v1
      ,MAX(CASE WHEN fk_critere = 2 THEN valeur END) AS v2
      ,MAX(CASE WHEN fk_critere = 3 THEN valeur END) AS v3
      ,MAX(CASE WHEN fk_critere = 4 THEN valeur END) AS v4
      ,MAX(CASE WHEN fk_critere = 5 THEN valeur END) AS v5
      ,MAX(CASE WHEN fk_critere = 6 THEN valeur END) AS v6
      ,MAX(CASE WHEN fk_critere = 7 THEN valeur END) AS v7
      ,MAX(CASE WHEN fk_critere = 8 THEN valeur END) AS v8
      ,MAX(CASE WHEN fk_critere = 9 THEN valeur END) AS v9
      ,MAX(CASE WHEN fk_critere = 10 THEN valeur END) AS v10
      ,MAX(CASE WHEN fk_critere = 11 THEN valeur END) AS v11
      ,MAX(CASE WHEN fk_critere = 12 THEN valeur END) AS v12
      ,MAX(CASE WHEN fk_critere = 13 THEN valeur END) AS v13
      ,MAX(CASE WHEN fk_critere = 14 THEN valeur END) AS v14
      ,MAX(CASE WHEN fk_critere = 15 THEN valeur END) AS v15   
FROM article a
LEFT JOIN valeurcritere v
ON v.fk_article = a.id_article AND fk_critere IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
GROUP BY a.id_article

(40877 ligne(s) affectée(s))
Temps d'exécution de SQL Server :
Temps UC = 500 ms, temps coulé = 588 ms.
Avertissement : la valeur nulle est éliminée par un agrégat ou par une autre opération définie.

Code :

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
SELECT a.id_article
      ,v1.valeur AS v1
      ,v2.valeur AS v2
      ,v3.valeur AS v3
      ,v4.valeur AS v4
      ,v5.valeur AS v5
      ,v6.valeur AS v6
      ,v7.valeur AS v7
      ,v8.valeur AS v8
      ,v9.valeur AS v9
      ,v10.valeur AS v10
      ,v11.valeur AS v11
      ,v12.valeur AS v12
      ,v13.valeur AS v13
      ,v14.valeur AS v14
      ,v15.valeur AS v15
FROM article a 
LEFT JOIN valeurcritere v1
ON v1.fk_article = a.id_article AND v1.fk_critere = 1
LEFT JOIN valeurcritere v2
ON v2.fk_article = a.id_article AND v2.fk_critere = 2
LEFT JOIN valeurcritere v3
ON v3.fk_article = a.id_article AND v3.fk_critere = 3
LEFT JOIN valeurcritere v4
ON v4.fk_article = a.id_article AND v4.fk_critere = 4
LEFT JOIN valeurcritere v5
ON v5.fk_article = a.id_article AND v5.fk_critere = 5
LEFT JOIN valeurcritere v6
ON v6.fk_article = a.id_article AND v6.fk_critere = 6
LEFT JOIN valeurcritere v7
ON v7.fk_article = a.id_article AND v7.fk_critere = 7
LEFT JOIN valeurcritere v8
ON v8.fk_article = a.id_article AND v8.fk_critere = 8
LEFT JOIN valeurcritere v9
ON v9.fk_article = a.id_article AND v9.fk_critere = 9
LEFT JOIN valeurcritere v10
ON v10.fk_article = a.id_article AND v10.fk_critere = 10
LEFT JOIN valeurcritere v11
ON v11.fk_article = a.id_article AND v11.fk_critere = 11
LEFT JOIN valeurcritere v12
ON v12.fk_article = a.id_article AND v12.fk_critere = 12
LEFT JOIN valeurcritere v13
ON v13.fk_article = a.id_article AND v13.fk_critere = 13
LEFT JOIN valeurcritere v14
ON v14.fk_article = a.id_article AND v14.fk_critere = 14
LEFT JOIN valeurcritere v15
ON v15.fk_article = a.id_article AND v15.fk_critere = 15

(40877 ligne(s) affectée(s))
Temps d'exécution de SQL Server :
Temps UC = 875 ms, temps coulé = 895 ms.

Je pense qu'y a pas photo.

Par contre, en ajoutant des ORDER BY id_article aux 2 requêtes, j'obtiens

Temps d'exécution de SQL Server :
Temps UC = 468 ms, temps coulé = 574 ms.
Avertissement : la valeur nulle est éliminée par un agrégat ou par une autre opération définie.

Temps d'exécution de SQL Server :
Temps UC = 532 ms, temps coulé = 619 ms.

Des explications?

[gecodis]

Bon finalement il semblerait qu'il y'a photo et qu'il faut vraiment voir au cas par cas.
Sur un cas réel pour mon site:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT a.id_article
          ,a.descsuc
          ,a.limite
          ,a.marque
          ,a.nom
          ,a.photo2
          ,a.remise
          ,a.pvht * (1 - CAST(a.remise AS FLOAT) / 100) AS pvremise
          ,MAX(CASE WHEN fk_critere = '1134' THEN valeur END) AS critere1
          ,MAX(CASE WHEN fk_critere = '1135' THEN valeur END) AS critere2
          ,MAX(CASE WHEN fk_critere = '1136' THEN valeur END) AS critere3
FROM article a
INNER JOIN article_categorie a2 ON a.id_article = a2.fk_article
INNER JOIN article_type_attrib a3 ON a.id_article = a3.fk_article
LEFT JOIN valeurcritere v ON a.id_article = v.fk_article
AND v.fk_critere IN ('1134','1135','1136')
WHERE a2.fk_categorie IN (721) AND a.online <> 0 AND a.valide <> 0 AND a.type_article = 1 AND a3.fk_type = 1
GROUP BY a.id_article, a.descsuc, a.limite, a.marque, a.nom, a.photo2, a.remise, a.pvHT
ORDER BY pvremise

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT a.id_article
          ,a.descsuc
          ,a.limite
          ,a.marque
          ,a.nom
          ,a.photo2
          ,a.remise,a.pvht * (1 - CAST(a.remise AS FLOAT) / 100) AS pvremise
          ,v1.valeur AS critere1
          ,v2.valeur AS critere2
          ,v3.valeur AS critere3
FROM article a
INNER JOIN article_categorie a2 ON a.id_article = a2.fk_article
INNER JOIN article_type_attrib a3 ON a.id_article = a3.fk_article
LEFT JOIN valeurcritere v1 ON a.id_article = v1.fk_article
AND v1.fk_critere = '1134'
LEFT JOIN valeurcritere v2 ON a.id_article = v2.fk_article
AND v2.fk_critere = '1135'
LEFT JOIN valeurcritere v3 ON a.id_article = v3.fk_article
AND v3.fk_critere = '1136'
WHERE a2.fk_categorie IN (721) AND a.online <> 0 AND a.valide <> 0 AND a.type_article = 1 AND a3.fk_type = 1
ORDER BY pvremise

La seconde requête est plus rapide que la première sur mon serveur de dev.
Par contre, la première reste plus rapide sur le serveur de prod