Discussion :

[flamant]

Bonjour,
la plupart des SGBD traduisent probablement la clause LEFT OUTER JOIN par du SQL pur (equivalent dans la partie "FROM" et la partie "WHERE"). C'est cette traduction que je recherche

Par exemple Pour ce début de requête

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT m.msg " +
                "FROM message m  " +
                "LEFT JOIN screen s on (m.scr_id = s.scr_id)" +
                "LEFT JOIN activity a on (a.act_id = s.act_id)" +
                "LEFT JOIN subject sub on (m.sub_id = sub.sub_id)" +
"LEFT JOIN RActivityStatus actStat on (actStat.id.act_id = a.act_id and actStat.sub_id = sub.sub_id) " +
LEFT JOIN  r_screenstatus stat on (stat.scr_id = s.scr_id and stat.sub_id = sub.sub_id and stat.sst_scrseq = m.sst_scrseq),
..............
.............
WHERE
...........
...........
....... actStatus.status <> '777' and stat.status <> '777'

Comment Oracle par exemple (ou un autre SGBD) traduirait en SQL pur les 2 dernieres OUTER JOIN ?

Si vous pouvez m'aider, merci d'avance

[Waldar]

Oracle converti la syntaxe SQL:92 en SQL:89 pour son moteur interne (donc avec des (+) pour les jointures externes), probablement lié à la gestion des compatibilités ascendantes.

On le voit en regardant le plan d'exécution, par exemple :

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
    select count(*)
      from dual d1
 left join dual d2
        on d2.dummy = d1.dummy;
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |     4 |            |          |
|   2 |   NESTED LOOPS OUTER|      |     1 |     4 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter("D2"."DUMMY"(+)="D1"."DUMMY")

[CinePhil]

LEFT OUTER JOIN, c'est du SQL pur, normalisé depuis 20 ans !

Vouloir rechercher l'ancienne syntaxe comme semble le faire Oracle, c'est comme vouloir retravailler avec un ordinateur à processeur 486, sur un écran en 640 x 400... Je dis ça de mémoire mais c'est l'idée.

Beurk !

[skuatamad]

LEFT OUTER JOIN, c'est du SQL pur, normalisé depuis 20 ans !

Et alors ?

Le SQL est un langage déclaratif pour décrire ce que l'on souhaite obtenir, la tambouille interne est laissée à la discrétion de l'éditeur. La norme ne fait que dire aux éditeurs voilà ce que vous êtes cencés comprendre et aux développeurs voilà ce que vous êtes cencés écrire !

Et je ne veux pas dire mais le fait que la norme SQL 2003 ne soit même pas accessible gratuitement... c'est un peu du foutage de gueule...

Vouloir rechercher l'ancienne syntaxe comme semble le faire Oracle

Le propre de l'optimiseur du SGBD c'est de transformer la requête SQL reçue pour récupérer les données, par exemple avec une vue V et une requête sur V avec un filtre, l'optimiseur peut choisir de pousser le filtre dans la vue afin de simplifier l'accès aux tables derrière la vue, plutôt que d'exécuter d'abord la vue puis de la filtrer...

Certes à l'heure actuelle oracle transfome le SQL ANSI en SQL non normalisé avant de faire les différentes étapes d'optimisation, mais je ne vois pas trop le problème tant que ça ne génère pas de bug (ce qui n'est pas toujours le cas)

c'est comme vouloir retravailler avec un ordinateur à processeur 486, sur un écran en 640 x 400...

Ben quelque soit le langage utilisé, in fine le processeur ne comprend que des 0 et des 1, donc il y a transformation du langage vers des bits en passant par des langages de couches inférieures...

[flamant]

Bonjour skuatamad, CinePhil, Waldar et merci pour vos réponses

Je m'appercoit que je me suis très mal exprimé.
Ce que je voulais dire est ce qui suit:

Par exemple si j'ai 2 tables REGION et BRANCH

REGION
region_nbr region_name
100 East region
200 Central region
300 Virtual region
400 West region

BRANCH
branch_nbr branch name region_nbr employee_count
108 New York 100 10
110 Boston 100 6
212 Chicago 200 5
404 San Diego 400 6
415 San Jose 400 3

Soit la requête suivante

SELECT region.region_nbr, region.region_name, branch.branch_nbr, branch.branch_name
FROM dbo.region
INNER JOIN dbo.branch
ON branch.region_nbr = region.region_nbr
ORDER BY region.region_nbr

Quand je veux dire que le SGBD traduit le INNER JOIN en SQL pur, je veux dire que la traduction (avec une clause WHERE) de la requête est la suivante

SELECT region.region_nbr, region.region_name, branch.branch_nbr, branch.branch_name
FROM dbo.region,
dbo.branch,
WHERE
branch.region_nbr = region.region_nbr
ORDER BY region.region_nbr

Ma vrai question est la suivante: Pour la requête suivante

SELECT region.region_nbr, region.region_name, branch.branch_nbr, branch.branch_name
FROM dbo.region
LEFT OUTER JOIN dbo.branch
ON branch.region_nbr = region.region_nbr
ORDER BY region.region_nbr

Comment je peux traduire cette dernière requête avec seulement une clause WHERE, c'est à dire une requête du style


SELECT region.region_nbr, region.region_name, branch.branch_nbr, branch.branch_name
FROM dbo.region,
dbo.branch,
WHERE
((branch.region_nbr = region.region_nbr (CONDITION QUI TRADUIT QU'ON INCLUS TOUTES LES LIGNES OU IL Y A UNE CORRESPONDANCE ENTRE LES DEUX TABLES)) OR (CONDITION QUI TRADUIT LE FAIT QU'ON PREND AUSSI LES LIGNES DE dbo.region QUI N'ONT PAS DE CORRESPONDANCES AVEC LA TABLE dbo.branch))
ORDER BY region.region_nbr

Le résultat de la requête avec la clause LEFT OUTER JOIN est

region_nbr region_name branch_nbr branch_name
100 East Region 108 New York
100 East Region 110 Boston
200 Central Region 212 Chicago
300 Virtual Region NULL NULL
Ma question est comment traduire avec seulement une clause WHERE (sans la clause LEFT OUTER JOIN) le fait que la jointure inclus aussi cette ligne où il n'y a pas de correspondance entre dbo.region et dbo.branch, c'est à dire que la jointure inclus toutes les lignes de dbo.region même si il n'y a pas de correspondance avec une ligne de dbo.branch
400 West Region 404 San Diego
400 West Region 415 San Jose

Je vous explique aussi pourquoi je me pose cette question

j'utilise Hibernate dans une application JAVA/J2EE ainsi que des requêtes JPQL comme NamedQuery. D'après la documentation de JPQL on peut faire des LEFT OUTER JOIN (en fait LEFT JOIN) entre des tables qui ont un lien dans le mapping hibernate, mais malheureusement il semble (j'ai essayé et ca ne marche pas) qu'il ne soit pas possible de faire des LEFT OUTER JOIN sans qu'il y ait de correspondance dans le mapping hibernate en specifiant une clause "ON" comme il est possible de faire en SQL pur.

Ce que je veux faire et qui ne marche pas

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
        @NamedQuery(query = SELECT  +
                m  +
                FROM RMessage m   +
                LEFT JOIN m.screen s  +  (par exemple le mapping hibernate indique que pour chaque object java RMessage m, m posséde une propriété screen de type RScreen) Là il y a un lien dans le mapping Hibernate
                LEFT JOIN s.RActivity a  + Là il y a un lien dans le mapping Hibernate
                LEFT JOIN m.subject sub  + Là il y a un lien dans le mapping Hibernate
LEFT JOIN RActivityStatus actStat on (actStat.id.actId = a.actId and actStat.id.subId = sub.subId)  + Là il n'y a pas de lien dans le mapping Hibernate
LEFT JOIN RScreenstatus stat on (stat.id.scrId = s.scrId AND stat.id.subId = sub.subId AND stat.id.sstScrseq = m.sstScrseq),  + Là il n y a pas de lien dans le mapping Hibernate
                RMsgline ml,  +
                RMsgline ml1,  +
                WHERE  +
                ml1.message.msgId = m.msgId and ml1.id.mslId = (select min(ml2.id.mslId) from RMsgline ml2 where ml2.message.msgId = m.msgId) AND  +
                ml.message.msgId = m.msgId AND  +
                m.subject.subId = :subId AND  +
                a.actId = :actId AND  +
                s.scrId = :scrId AND  +
                m.sstScrseq = :scrSeq AND  +
                ((m.fieldId is not null and actStat.status <> 777 and stat.sstStatus <> 777) OR (m.fieldId is null and m.msgCenter = 0 and actStat.status <> 777 and stat.sstStatus <> 777) OR m.msgCenter <> 0) AND  +
                m.msgType = Q  +
                ORDER BY m.msgDuedt, ml1.id.mslId, ml.id.mslId,
                name = RMessage.findByStillOpenSubjectActivityAndScreen),

La table de départ est RMessage. En fait les JOIN se feront que lorsque la propriété "msgCenter" de RMessage est égale à 0. C'est pourquoi j'ai essayé de faire quelque chose dans le style suivant, mais sans succès.

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
 
 
        @NamedQuery(query = SELECT  +
                m  +
                FROM RMessage m   +
                LEFT JOIN m.screen s  +
                LEFT JOIN s.RActivity a  +
                LEFT JOIN m.subject sub,  +
                RMsgline ml,  +
                RMsgline ml1,  +
                RActivityStatus actStat,  +
                RScreenstatus stat  +
                WHERE  +
                (m.msgCenter <> 0 OR (m.msgCenter = 0 and stat.id.scrId = s.scrId AND stat.id.subId = sub.subId AND stat.id.sstScrseq = m.sstScrseq)) AND  +
                (m.msgCenter <> 0 OR (m.msgCenter = 0 and actStat.id.actId = a.actId and actStat.id.subId = sub.subId)) AND  +
                ml1.message.msgId = m.msgId and ml1.id.mslId = (select min(ml2.id.mslId) from RMsgline ml2 where ml2.message.msgId = m.msgId) AND  +
                ml.message.msgId = m.msgId AND  +
                m.subject.subId = :subId AND  +
                a.actId = :actId AND  +
                s.scrId = :scrId AND  +
                m.sstScrseq = :scrSeq AND  +
                ((m.fieldId is not null and actStat.status <> 777 and stat.sstStatus <> 777) OR (m.fieldId is null and m.msgCenter = 0 and actStat.status <> 777 and stat.sstStatus <> 777) OR m.msgCenter <> 0) AND  +
                m.msgType = Q  +
                ORDER BY m.msgDuedt, ml1.id.mslId, ml.id.mslId,
                name = RMessage.findByStillOpenSubjectActivityAndScreen),

Si vous pouvez m'aider, d'avance merci

[tatayo]

Bonjour,
En gros tu veux transformer une requête avec une jointure externe en requête sans jointure externe.
Tu peux le faire avec une union:

Code SQL :

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

1
2
3
4
 
select TableA.col1,TableB.Col2
from TableA
left outer join TableB on TableA.Colx = TableB.Coly

peut se traduire en:

Code SQL :

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

1
2
3
4
5
6
7
8
 
select TableA.col1,TableB.Col2
from TableA
inner join TableB on TableA.Colx = TableB.Coly
union
select TableA.col1,null
from TableA
where not exists(select 1 from TableB where TableA.Colx = TableB.Coly)

Tatayo.

[flamant]

Bonjour tatayo et merci pour ta réponse.

Effectivement c'est une solution intéressante mais malheureusement JPQL ne supporte pas le "UNION"

Vois-tu un autre moyen ?

Merci encore

[CinePhil]

Flamant, tu es donc depuis deux jours au moins sur un problème dû à cet Objet Réellement Merdique qu'est Hibernate !

Comme j'ai passé quelques semaines douloureuses sur cette merde, je crois me souvenir qu'il est quand même possible de lui donner à manger une requête écrite en SQL normal et de lui demander simplement de l'exécuter.

Sauf pour ceux qui ont passé de longues heures d'apprentissage du dialecte, écrire une requête un peu complexe en JPQL prend beaucoup plus de temps que d'écrire une requête en SQL pur. Et je maintiens que la jointure en SQL pur, c'est JOIN depuis 20 ans !

[tchize_]

Flamant, tu es donc depuis deux jours au mloins sur un problème dû à cet Objet Réellement Merdique qu'est Hibernate !

Comme j'ai passé quelques semaines douloureuse sur cette merde, je crois me souvenir qu'il est quand même possible de lui donner à manger une requête écrite en SQL normal et de lui demander simplement de l'exécuter.

Faut pas exégérer non plus, il fait très bien ce qu'il est censé faire: du mapping db <-> objet. Après si tu veux lui faire faire des requetes qu'on n'ont rien à voir avec ton mapping objet (comme par exemple lui faire evaluer des relations qui n'existent pas, récupérer des données partielles, ou faire des requetes qui ne correspondent à aucune objet), tu le fais sortir de son rôle.

Et oui, il est possible de lui faire exécuter une requete SQL de base. Mais si c'est pour faire ça, pourquoi passer par hibernate au lieu d'aller prendre directement une connexion sur la DB? :/

[flamant]

Bonjour tchize et Cinephil et merci pour vos réponse,
finalement je suis passé par des requêtes en SQL natif plutôt que des requêtes JPQL et j'ai pu faire mes LEFT OUTER JOIN avec les clauses "ON" sans problème.

Merci encore pour vos réponses