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
| -- tables et données pour notre exemple
CREATE TABLE T_CHARGEMENT_CHG
(CHG_ID INT IDENTITY PRIMARY KEY,
CHG_DATE DATE NOT NULL,
CHG_ENTREPOT VARCHAR(32) NOT NULL,
CHG_NOMBRE SMALLINT NOT NULL);
INSERT INTO T_CHARGEMENT_CHG VALUES ( '2023-09-17', 'Marseille', 11 ) ;
INSERT INTO T_CHARGEMENT_CHG VALUES ( '2023-09-15', 'Marseille', 10 ) ;
INSERT INTO T_CHARGEMENT_CHG VALUES ( '2023-09-16', 'Paris', 20 ) ;
INSERT INTO T_CHARGEMENT_CHG VALUES ( '2023-09-17', 'Lyon', 31 ) ;
INSERT INTO T_CHARGEMENT_CHG VALUES ( '2023-09-16', 'Lyon', 30 ) ;
CREATE TABLE T_ENTREPOT_ETP
(ETP_ENTREPOT varchar(32));
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Marseille' );
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Paris' );
INSERT INTO T_ENTREPOT_ETP ( ETP_ENTREPOT ) VALUES ( 'Lyon' );
--> les requêtes suivantes :
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_ENTREPOT_ETP AS L
RIGHT OUTER JOIN T_CHARGEMENT_CHG S
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_ENTREPOT_ETP AS L
LEFT OUTER JOIN T_CHARGEMENT_CHG S
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
SELECT S.CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_ENTREPOT_ETP AS L
INNER JOIN T_CHARGEMENT_CHG S
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
--> donnent toutes le même résultat qui ne fait pas apparaître
-- toutes les dates combinées aux entreprots...
/*
CHG_DATE ETP_ENTREPOT CHG_NOMBRE
---------- -------------------------------- ----------
2023-09-17 Marseille 11
2023-09-15 Marseille 10
2023-09-16 Paris 20
2023-09-17 Lyon 31
2023-09-16 Lyon 30
*/
--> Il y manque 4 lignes :
/*
2023-09-16 Marseille NULL
2023-09-15 Paris NULL
2023-09-17 Paris NULL
2023-09-15 Lyon NULL
*/
--> la jointure partitionnée résous élégamment ce problème :
SELECT S.CHG_DATE, L.ETP_ENTREPOT,S.CHG_NOMBRE
FROM T_CHARGEMENT_CHG AS S PARTITION BY (CHG_DATE)
RIGHT OUTER JOIN T_ENTREPOT_ETP L
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT;
/*
CHG_DATE ETP_ENTREPOT CHG_NOMBRE
---------- ---------------- -----------
2019-01-15 Boston NULL
2019-01-15 London 10
2019-01-15 Paris NULL
2019-01-16 Boston 30
2019-01-16 London NULL
2019-01-16 Paris 20
2019-01-17 Boston 31
2019-01-17 London 11
2019-01-17 Paris NULL
*/
--> équivalence avec expression de chacun des valeurs du domaine
SELECT CAST('2023-09-15' AS DATE) AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_ENTREPOT_ETP AS L
RIGHT OUTER JOIN T_CHARGEMENT_CHG AS S
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT AND S.CHG_DATE = '2023-09-15'
UNION ALL
SELECT CAST('2023-09-16' AS DATE) AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_CHARGEMENT_CHG AS S
RIGHT OUTER JOIN T_ENTREPOT_ETP AS L
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT AND S.CHG_DATE = '2023-09-16'
UNION ALL
SELECT CAST('2023-09-17' AS DATE) AS CHG_DATE, L.ETP_ENTREPOT, S.CHG_NOMBRE
FROM T_CHARGEMENT_CHG AS S
RIGHT OUTER JOIN T_ENTREPOT_ETP AS L
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT AND S.CHG_DATE = '2023-09-17'
ORDER BY 1, 2
--> 4 formes d'équivalence avec plusieurs jointures dont un produit cartésien
SELECT CE.*, C.CHG_NOMBRE
FROM (SELECT *
FROM (SELECT DISTINCT CHG_DATE
FROM T_CHARGEMENT_CHG) AS T1
CROSS JOIN (SELECT DISTINCT ETP_ENTREPOT
FROM T_ENTREPOT_ETP) AS T2
) AS CE
LEFT OUTER JOIN (SELECT CHG_DATE, CHG_ENTREPOT, CHG_NOMBRE
FROM T_CHARGEMENT_CHG) AS C
ON CE.ETP_ENTREPOT = C.CHG_ENTREPOT
AND CE.CHG_DATE = C.CHG_DATE
ORDER BY 1, 2;
WITH T AS
(SELECT C.CHG_DATE, E.ETP_ENTREPOT, C.CHG_NOMBRE
FROM T_CHARGEMENT_CHG AS C
INNER JOIN T_ENTREPOT_ETP AS E
ON C.CHG_ENTREPOT = E.ETP_ENTREPOT)
SELECT *
FROM T
UNION ALL
SELECT DISTINCT C.CHG_DATE, E.ETP_ENTREPOT, NULL
FROM T_CHARGEMENT_CHG AS C
CROSS JOIN T_ENTREPOT_ETP AS E
WHERE NOT EXISTS(SELECT *
FROM T
WHERE T.CHG_DATE = C.CHG_DATE
AND E.ETP_ENTREPOT = T.ETP_ENTREPOT)
ORDER BY 1, 2;
WITH
T AS (SELECT DISTINCT CHG_DATE, X.CHG_ENTREPOT
FROM T_CHARGEMENT_CHG
CROSS JOIN (SELECT CHG_ENTREPOT
FROM T_CHARGEMENT_CHG) AS X)
SELECT T.CHG_DATE, T.CHG_ENTREPOT, S.CHG_NOMBRE
FROM T
LEFT OUTER JOIN T_CHARGEMENT_CHG AS S
ON T.CHG_DATE = S.CHG_DATE
AND T.CHG_ENTREPOT = S.CHG_ENTREPOT
LEFT OUTER JOIN T_ENTREPOT_ETP AS L
ON S.CHG_ENTREPOT = L.ETP_ENTREPOT
ORDER BY 1, 2;
WITH
T AS
(
SELECT DISTINCT C.CHG_DATE, E.ETP_ENTREPOT
FROM T_CHARGEMENT_CHG AS C
CROSS JOIN T_ENTREPOT_ETP AS E
)
SELECT T.CHG_DATE, T.ETP_ENTREPOT, C.CHG_NOMBRE
FROM T
LEFT OUTER JOIN T_CHARGEMENT_CHG AS C
ON T.CHG_DATE = C.CHG_DATE
AND T.ETP_ENTREPOT = C.CHG_ENTREPOT
ORDER BY 1, 2; |
