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; |
Partager