[Puristes norme SQL] UNION / UNION ALL [Résolu]

[Bowen]

Bonjour.

J'aimerai refaire un point avec les habitués du SQL et de la théorie des ensembles.

J'ai actuellement un problème avec mon moteur de base de données. (HyperFile)

Je vais d'abord poser le contexte :

• Deux fichiers : FactureLigne et _ArcFactureLigne (un fichier et son archive)
• Les structures des deux fichiers sont strictement identiques
• Ces fichiers contiennent respectivement 316 FactureLigne et 0 _ArcFactureLigne (fichiers vidés pour l'exemple)
• La requête
  Code :

  SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne

  me renvoie 316 lignes
  
• La requête
  Code :

  SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

  me renvoie 0 lignes
  
• La requête
  Code :

  SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne

  me renvoie 256 lignes
  
• La requête
  Code :

  1
  2
  SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
  UNION ALL SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

  me renvoie 316 lignes

Jusqu'ici pas de problème, tout est cohérent.
Mon problème maintenant :
La requête

Code :

1
2
SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
UNION SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

devrait pour moi renvoyer 316 lignes.
C'est à dire :

• Les lignes de FactureLigne (316 enreg)
• Auquelles on ajoute les lignes de _ArcFactureLigne (0 enreg)
• et à cet ensemble de données résultat, on retire l'intersection de FactureLigne et _ArcFactureLigne (soit 0 lignes, pour ne pas les prendre deux fois)

Ce qui me chagrine, c'est que le moteur ne me renvoie que 256 lignes, soit le résultat de la requête suivante :

Code :

1
2
3
4
5
 
SELECT DISTINCT * FROM (
SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
UNION ALL SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne
)

Donc voici ma question :
Qui a raison ?

[Oishiiii]

Bonjour,

Je cite Wikipédia, sur l'opération d'union dans la théorie des ensembles:

Dans la théorie des ensembles, l'union ou réunion de deux ensembles A et B est l'ensemble qui contient tous les éléments qui appartiennent à A ou appartiennent à B.

Dans la théorie relationnelle (qui n'est pas la théorie SQL) les opérandes de l'opérateur UNION sont des relations. Une relation est un ensemble. Tout va bien.


Le fait est que le langage SQL permet de manipuler des tables (ou relation) qui contiennent des doublons !
Un ensemble par définition ne peux pas contenir de doublons ! Il n'y a pas de doublons dans l'ensemble des entiers naturels.

Par défaut en SQL, l'opérateur SELECT permet donc de retourner des doublons. C'est une énorme erreur, un délit.
C'est comme si, lorsqu'on écrit simplement "SELECT ..." on utilisait implicitement:

Code :

SELECT ALL ...

L'opérateur UNION agit en respectant la théorie relationnel, car on est certain que le résultat d'une UNION de deux tables ne retournera jamais de doublons. C'est tout à fait correct.

Envoyé par Bowen

Ce qui me chagrine, c'est que le moteur ne me renvoie que 256 lignes, soit le résultat de la requête suivante :

Code :

Je dirais plutôt que le moteur retourne ceci :

Code :

1
2
3
SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
UNION 
SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

Le "SELECT ALL" implicite a était remplacé par un "SELECT DISTINCT", ce qui devrait être le cas en permanence...

Code :

SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne

De toute évidence le résultat de cette requête (qui est une table en soit), d'après ce que vous nous dites, contient des doublons (60 lignes).

Soit vous avez besoins de ces doublons dans le résultat de l'opération d'union qui comporte cette table en opérande, dans ce cas utilisez UNION ALL, soit vous n'en avez pas besoin, et il est tout à fait normal qu'UNION ne retourne pas ces doublons.

Envoyé par Bowen

Donc voici ma question :
Qui a raison ?

J'espère avoir était clair et avoir répondu à la question.

[Bowen]

Envoyé par Oishiiii

Je dirais plutôt que le moteur retourne ceci :

Code :

Le "SELECT ALL" implicite a était remplacé par un "SELECT DISTINCT", ce qui devrait être le cas en permanence...

Test effectué, le DISTINCT s'effectue bien sur le résultat final, pas sur chaune des requêtes.

Puis-je prendre un jeu de données, pour faciliter ma compréhension ?

FactureLigne : 4 enreg [ Qte20 | PxNetHT ]

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17

_ArcFactureLigne : 4 enreg [ Qte20 | PxNetHT ]

• 8 | 22
• 9 | 23
• 6 | 35
• 8 | 22

J'ai donc 4 lignes, mais 3 valeurs distinctes par table.
Et la ligne [ 6 | 35 ] se retrouve dans chacun des deux ensembles.

La requête

Code :

1
2
3
4
 
SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
UNION 
SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

ne me renvoie pourtant que 5 lignes. La ligne [ 6 | 35 ] n'apparaissant qu'une seule fois.

La même requête sans les DISTINCT renvoie d'ailleurs exactement les mêmes lignes.
Est-ce que mon raisonnement est mauvais?
Pour moi, la requête

Code :

1
2
3
SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne
UNION 
SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM _ArcFactureLigne

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17
• 8 | 22
• 9 | 23
• 8 | 22

Dans le cas contraire, la méthode pour récupérer ce résultat doit-elle être ?

• Prendre l'ensemble de FactureLigne qui n'est pas dans _ArcFactureLigne (avec un NOT IN ou un NOT EXISTS)
• Prendre l'ensemble de _ArcFactureLigne
• Faire un UNION ALL des deux

[Oishiiii]

Envoyé par Bowen

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17
• 8 | 22
• 9 | 23
• 8 | 22

Dans le cas contraire, la méthode pour récupérer ce résultat doit-elle être ?

• Prendre l'ensemble de FactureLigne qui n'est pas dans _ArcFactureLigne (avec un NOT IN ou un NOT EXISTS)
• Prendre l'ensemble de _ArcFactureLigne
• Faire un UNION ALL des deux

Je ne comprend pas vraiment l'intérêt de la chose, mais oui, si c'est ce résultat que vous cherchez, effectivement il faut utiliser NOT EXISTS avec forcément UNION ALL.

Sur SQL Server 2005 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH A(W, X) AS (
	SELECT 3, 62 UNION ALL
	SELECT 6, 17 UNION ALL
	SELECT 6, 35 UNION ALL
	SELECT 6, 17
), B (Y, Z) AS (
	SELECT 8, 22 UNION ALL
	SELECT 9, 23 UNION ALL
	SELECT 6, 35 UNION ALL
	SELECT 8, 22
)
SELECT W, X FROM A WHERE NOT EXISTS(SELECT * FROM B WHERE W=Y AND X=Z)
UNION ALL
SELECT  Y, Z FROM B

Code :

1
2
3
4
5
6
7
8
9
W      X
--------
3	62
6	17
6	17
8	22
9	23
6	35
8	22

[CinePhil]

Bowen, apparemment, tu n'as pas compris l'explication d'Oishiiii.

Envoyé par Bowen

La requête

Code :

SELECT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne

me renvoie 316 lignes

La requête

Code :

SELECT DISTINCT FactureLigne_Qte20, FactureLigne_PxNetHT FROM FactureLigne

me renvoie 256 lignes

Ceci montre que ta table FactureLigne contient 316 - 256 = 60 couples {FactureLigne_Qte20, FactureLigne_PxNetHT} en double.

Comme l'opération UNION supprime les doublons, l'union de la table FactureLigne avec une table vide supprime les doublons de la table FactureLigne et revient donc à faire une requête SELECT DISTINCT sur la table FactureLigne toute seule. Il est donc normal que le résultat de la requête UNION ne te retourne que 256 lignes.

Dans l'ordre, le SGBD exécute les deux sous-requêtes, soit une liste de 316 + 0 = 316 lignes, puis il supprime les 60 doublons de cette liste et tu n'as plus que 256 lignes.

Faisons-le avec ton dernier exemple :

FactureLigne : 4 enreg [ Qte20 | PxNetHT ]

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17

_ArcFactureLigne : 4 enreg [ Qte20 | PxNetHT ]

• 8 | 22
• 9 | 23
• 6 | 35
• 8 | 22

Code :

Le SGBD met dans une liste le résultat des deux sous-requêtes, soit la liste suivante :
[ Qte20 | PxNetHT ]

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17
• 8 | 22
• 9 | 23
• 6 | 35
• 8 | 22

Ensuite il supprime les lignes de la liste en double :

• 3 | 62
• 6 | 17
• 6 | 35
• 6 | 17
• 8 | 22
• 9 | 23
• 6 | 35
• 8 | 22

Résultat final :

• 3 | 62
• 6 | 17
• 6 | 35
• 8 | 22
• 9 | 23

Soit 5 lignes de résultat.

[Bowen]

Si si, j'avais bien compris... que je n'utilisais pas la bonne méthode. Ce que je voulais, c'était ne supprimer que les doublons créés par l'intersection des deux fichiers.
Chose que je croyais possible directement par un UNION.
Bref, j'ai encore dit une bêtise...

Je vous remercie en tous cas pour tous ces éclaircissements.

Ma requête correcte (dans mon code, donc dans une configuration un peu moins "scolaire") comprends donc un UNION ALL maintenant.

Merci, à vous, ça fait du bien de revenir sur les fondamentaux.

[SQLpro]

Un doublon étant un doublon il est impossible de le distinguer. Que les doublons soit dans une table ou l'autre, une fois l'union faite vous serez incapable de voir d'où viennent vos doublons.
Donc l'opérateur UNION dédoublonne TOUT !

Votre erreur, votre mauvaise interprétation vient du fait que vous ne savez pas travailler en ensembliste et pensez itératif... C'est une erreur courante !
Pensez qu'une ensembliste il n'y a pas de notion d'ordre. Une fois dans un résultat il n'est pas possible de savoir d'ou vient une ligne !

A +