Exercices sur les jointures !

Salut à tous.

J'ai repris le lien suivant, donné par Escartefigue dans un autre sujet :
--> http://weblogs.sqlteam.com/jeffs/arc...joins-sql.aspx
Il s'agit d'un problème de jointure entre plusieurs tables.

J'ai repris les exercices que je retranscris ici, sous forme d'exécutable.

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `People`
--------------
 
--------------
CREATE TABLE `People`
( `PersonID`    integer unsigned  not null auto_increment primary key,
  `PersonName`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `People` (`PersonName`) values
  ('Fred Flintstone'), ('Barney Rubble'), ('George Jetson')
--------------
 
--------------
select * from `People`
--------------
 
+----------+-----------------+
| PersonID | PersonName      |
+----------+-----------------+
|        1 | Fred Flintstone |
|        2 | Barney Rubble   |
|        3 | George Jetson   |
+----------+-----------------+
--------------
DROP TABLE IF EXISTS `PetTypes`
--------------
 
--------------
CREATE TABLE `PetTypes`
( `PetTypeID`  integer unsigned  not null auto_increment primary key,
  `PetType`    varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `PetTypes` (`PetType`) values
  ('Dinosaur'), ('Hopparoo')
--------------
 
--------------
select * from `PetTypes`
--------------
 
+-----------+----------+
| PetTypeID | PetType  |
+-----------+----------+
|         1 | Dinosaur |
|         2 | Hopparoo |
+-----------+----------+
--------------
DROP TABLE IF EXISTS `Pets`
--------------
 
--------------
CREATE TABLE `Pets`
( `PetID`      integer unsigned  not null auto_increment primary key,
  `PetTypeID`  integer unsigned  not null,
  `PetName`    varchar(255)      not null,
  `OwnerID`    integer unsigned  not null,
  CONSTRAINT `FK_PETYPES` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PEOPLES` FOREIGN KEY (`OwnerID`)   REFERENCES `People`   (`PersonID`)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `Pets` (`PetTypeID`,`PetName`,`OwnerID`) values
  (1, 'Dino', 1), (2, 'Hoppy', 2)
--------------
 
--------------
select * from `Pets`
--------------
 
+-------+-----------+---------+---------+
| PetID | PetTypeID | PetName | OwnerID |
+-------+-----------+---------+---------+
|     1 |         1 | Dino    |       1 |
|     2 |         2 | Hoppy   |       2 |
+-------+-----------+---------+---------+
--------------
select           t1.PersonName,
                 t2.PetName
 
           from  People as t1
 
left outer join  Pets   as t2
             on  t2.OwnerID = t1.PersonID
--------------
 
+-----------------+---------+
| PersonName      | PetName |
+-----------------+---------+
| Fred Flintstone | Dino    |
| Barney Rubble   | Hoppy   |
| George Jetson   | NULL    |
+-----------------+---------+
--------------
select           t1.PersonName,
                 t2.PetName,
                 t3.PetType
           from  People   as t1
 
left outer join  Pets     as t2
             on  t2.OwnerID = t1.PersonID
 
     inner join  PetTypes as t3
             on  t3.PetTypeID = t2.PetTypeID
--------------
 
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
+-----------------+---------+----------+
--------------
select           t1.PersonName,
                 t2.PetName,
                 t3.PetType
           from  People   as t1
 
left outer join  Pets     as t2
             on  t2.OwnerID = t1.PersonID
 
left outer join  PetTypes as t3
             on  t3.PetTypeID = t2.PetTypeID
--------------
 
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
| George Jetson   | NULL    | NULL     |
+-----------------+---------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans ce premier exercice, l'auteur tient à nous souligner que le résultat qu'il attend est celui de la dernière requête que je reproduis ci-après :

Code:

1
2
3
4
5
6
7
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
| George Jetson   | NULL    | NULL     |
+-----------------+---------+----------+

Dans le deuxième exercice, il introduit une nouvelle ligne dans la table "Pets", et cherche à obtenir le même résultat que précédemment.
Comme par hasard, cette ligne nouvellement ajouté, n'est pas en relation avec la table "PetsTypes".

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `People`
--------------
 
--------------
CREATE TABLE `People`
( `PersonID`    integer unsigned  not null auto_increment primary key,
  `PersonName`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `People` (`PersonName`) values
  ('Fred Flintstone'), ('Barney Rubble'), ('George Jetson')
--------------
 
--------------
select * from `People`
--------------
 
+----------+-----------------+
| PersonID | PersonName      |
+----------+-----------------+
|        1 | Fred Flintstone |
|        2 | Barney Rubble   |
|        3 | George Jetson   |
+----------+-----------------+
--------------
DROP TABLE IF EXISTS `PetTypes`
--------------
 
--------------
CREATE TABLE `PetTypes`
( `PetTypeID`  integer unsigned  not null auto_increment primary key,
  `PetType`    varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `PetTypes` (`PetType`) values
  ('Dinosaur'), ('Hopparoo')
--------------
 
--------------
select * from `PetTypes`
--------------
 
+-----------+----------+
| PetTypeID | PetType  |
+-----------+----------+
|         1 | Dinosaur |
|         2 | Hopparoo |
+-----------+----------+
--------------
DROP TABLE IF EXISTS `Pets`
--------------
 
--------------
CREATE TABLE `Pets`
( `PetID`      integer unsigned  not null auto_increment primary key,
  `PetTypeID`  integer unsigned      null,
  `PetName`    varchar(255)      not null,
  `OwnerID`    integer unsigned  not null,
  CONSTRAINT `FK_PETYPES` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_PEOPLES` FOREIGN KEY (`OwnerID`)   REFERENCES `People`   (`PersonID`)  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `Pets` (`PetTypeID`,`PetName`,`OwnerID`) values
  (1, 'Dino', 1), (2, 'Hoppy', 2), (null, 'Baby Puss', 1)
--------------
 
--------------
select * from `Pets`
--------------
 
+-------+-----------+-----------+---------+
| PetID | PetTypeID | PetName   | OwnerID |
+-------+-----------+-----------+---------+
|     1 |         1 | Dino      |       1 |
|     2 |         2 | Hoppy     |       2 |
|     3 |      NULL | Baby Puss |       1 |
+-------+-----------+-----------+---------+
--------------
select           t1.PersonName,
                 t2.PetName,
                 t3.PetType
           from  People   as t1
 
left outer join  Pets     as t2
             on  t2.OwnerID = t1.PersonID
 
     inner join  PetTypes as t3
             on  t3.PetTypeID = t2.PetTypeID
--------------
 
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
+-----------------+---------+----------+
--------------
select      ta.ownerID,
            ta.PetName,
            tb.PetType
      from  Pets as ta
 
inner join  PetTypes as tb
        on  tb.PetTypeID = ta.PetTypeID
--------------
 
+---------+---------+----------+
| ownerID | PetName | PetType  |
+---------+---------+----------+
|       1 | Dino    | Dinosaur |
|       2 | Hoppy   | Hopparoo |
+---------+---------+----------+
--------------
select           t1.PersonName,
                 t2.PetName,
                 t2.PetType
           from  People   as t1
 
left outer join  (    select  ta.ownerID,
                              ta.PetName,
                              tb.PetType
                        from  Pets as ta
 
                  inner join  PetTypes as tb
                          on  tb.PetTypeID = ta.PetTypeID
                 ) as t2
             on  t2.OwnerID   = t1.PersonID
--------------
 
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
| George Jetson   | NULL    | NULL     |
+-----------------+---------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Comme on peut le voir dans la première requête, la ligne contenant le nom "George Jetson" n'apparait pas.
En fait, le problème vient dans la jointure où il est mis un "inner joint" (une jointure interne).

La solution proposée consiste à créer une premier extraction entre la table "Pets" et la table "PetTypes", sous la forme d'une sous-requête.
J'ai reproduis dans la deuxième requête, le contenu de ce résultat.

Puis dans la troisième requête de faire une jointure entre la table "people" et cette sous-requête.
Le tout afin de reproduire, à l'identique, la même extraction que dans le premier exercice.

Franchement, est-ce bien utile de procéder ainsi ?

Je propose la solution suivante, qui permet aussi de produire le même résultat :

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
--------------
set sql_mode = ''
--------------
 
--------------
select           t1.PersonName,
                 t2.PetName,
                 t3.PetType
 
           from  People   as t1
 
left outer join  Pets     as t2
             on  t2.OwnerID   = t1.PersonID
 
left outer join  PetTypes as t3
             on  t3.PetTypeID = t2.PetTypeID
 
       group by  t1.PersonName
--------------
 
+-----------------+---------+----------+
| PersonName      | PetName | PetType  |
+-----------------+---------+----------+
| Fred Flintstone | Dino    | Dinosaur |
| Barney Rubble   | Hoppy   | Hopparoo |
| George Jetson   | NULL    | NULL     |
+-----------------+---------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Comme on peut le voir, il est inutile de passer par une sous-requête pour obtenir le même résultat.
Il y a juste un regroupement à faire sur le nom de la personne.

Si vous avez des commentaires à faire sur cet exercice, je suis preneur.

Merci !
@+

Citation:

Envoyé par Artemus24

J'ai repris le lien suivant, donné par Escartefigue dans un autre sujet :
--> http://weblogs.sqlteam.com/jeffs/arc...joins-sql.aspx
Il s'agit d'un problème de jointure entre plusieurs tables.

Personnellement, je préfère la syntaxe mentionnée en fin de lien, plutôt qu'une requete corrélée, à savoir (dans l'exemple du lien)
Code:

1 2 3 4 5 select People.PersonName, Pets.PetName, PetTypes.PetType from People left outer join (Pets inner join PetTypes on Pets.PetTypeID = PetTypes.PetTypeID) on Pets.OwnerID = People.PersonID

Salut Escartefigue.

Je pense qu'il existe toujours un équivalent (voir mon dernier exemple), pour obtenir le résultat recherché, sans passer par cette forme d'écriture.
Il serait intéressant de donner un exemple où justement, on ne peut pas faire autrement que de passer par cette forme d'écriture.

Le problème dans cet exemple (le second) vient du comportement du "inner join" par rapport au "left outer join".
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID inner join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | +-----------------+---------+----------+
Le "left outer join" n'affiche pas la ligne ayant comme nom "George Jetson" alors qu'elle devrait le faire.
Tout se passe comme si le "inner join" prenait le pas sur le "left outer join".

D'où ma question : n'y-a-t-il pas un bug de comportement quand il y a plusieurs jointures ?

@+

Ce n'est pas un bug, c'est le fonctionnement normal

Dans un cas on construit (T1 left T2) intersect T3
Dans l'autre on construit T1 left (T2 intersect T3)

Le 1er cas revient à une jointure inner

D'où l'intérêt de la syntaxe que j'ai mentionnée plus haut
NB : considérer INTERSECT au sens relationnel, MySQL sauf erreur de ma part, ne connait toujours pas cette instruction y compris en V8...

Salut Escartefigue.

Citation:

Envoyé par Escartefigue

NB : considérer INTERSECT au sens relationnel, MySQL sauf erreur de ma part, ne connait toujours pas cette instruction y compris en V8...

Oui, en effet, MySql V8.0 n'a toujours pas implémenté "intersect" (idem aussi pour "except").
Mais il existe un équivalent avec "in" ou avec "exists".
C'est un peu plus lourd à mettre en oeuvre, mais ça fonctionne.

Citation:

Envoyé par Escartefigue

Ce n'est pas un bug, c'est le fonctionnement normal

Jusqu'à présent, j'ai toujours utilisé le même type de jointure dans un select, soit tout en "inner join" ou soit tout en "left outer join".

Quand il y a un panachage entre les deux, le résultat peut surprendre.
J'ai toujours cru que la première jointure prenait le pas sur la deuxième, qui la deuxième prenait le pas sur la troisième et ainsi de suite.
Avec des parenthèses, cela donne (table1 jointure (table2 jointure (table3 jointure ...))).
Or d'après ce que j'ai pu comprendre, c'est exactement l'inverse, à savoir (((table1 jointure table2) jointure table3) jointure ...).
Mais il y a pire, MySql peut changer l'ordre des jointures pour des questions de performances.

Il faudrait alors adopter, dans le cas de plusieurs jointures, l'introduction des parenthèses pour bien délimiter les extractions.

Citation:

Envoyé par Escartefigue

D'où l'intérêt de la syntaxe que j'ai mentionnée plus haut

En effet, c'est la seule solution que l'on doit adopter en cas de panachage dans les types de jointures.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join ( Pets as t2 inner join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID ) on t2.OwnerID = t1.PersonID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | | George Jetson | NULL | NULL | +-----------------+---------+----------+
Cette petite étude m'a permis de bien comprendre tout l'intérêt de cette écriture !

@+