Tout le monde connait le prédicat SQL EXISTS qui permet de savoir si la sous requête retourne ou non des lignes (mêmes NULLs). Bien entendu ce prédicat n’a de sens qu’avec uns sous requête corrélée sinon il n’offre aucun intérêt .
En revanche, peu de développeurs pensent à utiliser les prédicats UNIQUE et DISTINCT, car ils sont rarement implémenté en SQL.
Voici comment, en leur absence, vous pouvez écrire des requêtes qui donnent sensiblement le même résultat…
1 – Quantificateur d’existence EXISTS
Le prédicat EXISTS peu être considéré comme une fonction dont l’argument serait une requête. Si cette requête produit un résultat, EXISTS vaut vrai, quelque soit le résultat découlant de l’évaluation de la requête, même si toutes les occurrences des lignes et des colonnes de la table réponse sont constituées de marqueurs NULL. Dans le cas contraire (aucune ligne retournée) EXISTS vaut faux.
Il n’est pas nécessaire, dans la clause SELECT de la sous requête introduite pas le prédicat EXISTS, de préciser les colonnes visées, mieux vaut utiliser l’étoile, ou une constante telle que 1. Car ce n’est pas le résultat qui compte mais plus simplement l’existence ou non d’au moins une ligne même si toutes les colonnes sont à NULL. En revanche, dans le cas ou un calcul d’agrégat statistique est introduit dans la sous requête, il est plus pratique d’y faire figurer une constante (0 ou 1 par exemple), sinon cela obligerait à utiliser un groupage sur de multiples colonnes.
Insistons sur le fait que le prédicat EXISTS n’a de réel intérêt que lorsqu’il est introduit dans le cadre d’une sous requête corrélée.
Exemple :
CREATE TABLE T1 (C1 INT);
INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2);
INSERT INTO T1 VALUES (3);
INSERT INTO T1 VALUES (5);
INSERT INTO T1 VALUES (7);
INSERT INTO T1 VALUES (9);
CREATE TABLE T2 (C2 INT);
INSERT INTO T2 VALUES (1);
INSERT INTO T2 VALUES (2);
INSERT INTO T2 VALUES (3);
INSERT INTO T2 VALUES (3);
INSERT INTO T2 VALUES (3);
INSERT INTO T2 VALUES (5);
INSERT INTO T2 VALUES (5);
INSERT INTO T2 VALUES (7);
SELECT *
FROM T1
WHERE EXISTS(SELECT *
FROM T2
WHERE T1.C1 BETWEEN T2.C2 - 1
AND T2.C2 + 1)
C1
-----------
1
2
3
5
7
2 – Quantificateur UNIQUE
Le prédicat UNIQUE consiste en un raffinement du prédicat EXISTS. Il vaut vrai si le résultat est unique, c’est à dire constitué :
- d’aucune ligne
- d’une ligne unique
- d’un ensemble de ligne de valeurs toutes différentes.
Rappelons que les marqueurs NULL ne pouvant se comparer, des occurrences marquées à NULL sont par définition toutes différentes.
Ainsi, le prédicat UNIQUE considère les évaluations suivantes :
UNIQUE () => vrai
UNIQUE (1) => vrai
UNIQUE (1, 2) => vrai
UNIQUE (1, NULL) => vrai
UNIQUE (NULL, NULL) => vrai
UNIQUE (1, 1) => faux
Voici maintenant un exemple de construction de prédicat UNIQUE à l’aide du quantificateur EXISTS :
SELECT *
FROM T1
WHERE EXISTS(SELECT 1
FROM T2
WHERE T1.C1 BETWEEN T2.C2 - 1
AND T2.C2 + 1
GROUP BY T1.C1
HAVING COUNT(DISTINCT C2) = 1)
OR NOT EXISTS(SELECT 1
FROM T2
WHERE T2.C2 = T1.C1)
C1
-----------
5
7
9
3 – Quantificateur DISTINCT
Le prédicat DISTINCT est un raffinement du prédicat UNIQUE Il vaut vrai si le résultat est vraiment unique, c’est à dire constitué :
- d’aucune ligne
- d’une ligne unique
- d’un ensemble de lignes, de valeurs différentes, dont toutes les occurrences sont valuées.
- d’un ensemble de lignes toutes différentes dont les occurrences vides (NULL) ne doublonnent pas.
Mais contrairement au prédicat UNIQUE, le prédicat DISTINCT considère que les marqueurs NULL doivent être pris en compte comme des valeurs ordinaires identiques, ce qui conduit à éliminer des « doublons » de NULLs.
Ainsi, le prédicat DISTINCT considère les évaluations suivantes :
DISTINCT () => vrai
DISTINCT (1) => vrai
DISTINCT (1, 2) => vrai
DISTINCT (1, NULL) => vrai
DISTINCT (NULL, NULL) => faux
DISTINCT (1, 1) => faux
Voici maintenant un exemple de construction de prédicat DISTINCT à l’aide du quantificateur EXISTS :
SELECT *
FROM T1
WHERE EXISTS(SELECT 1
FROM T2
WHERE T1.C1 BETWEEN T2.C2 - 1
AND T2.C2 + 1
GROUP BY T1.C1
HAVING COUNT(*) = 1)
OR NOT EXISTS(SELECT 1
FROM T2
WHERE T2.C2 = T1.C1)
C1
-----------
7
9
Pour terminer…
Reste un petit raffinement pour gérer pleinement ces équivalences : la gestion du NULL dans les sous requêtes résultantes. Je laisse à votre sagacité le soin de réfléchir à ce sujet !
***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation
Bonjour,
Nous sommes confrontés à un problème concernant le Distinct.
Voici la requête que nous devons exécuter :
SELECT DISTINCT F.Code_Financement, F.Regle_deduction_abs
FROM Temp_stat_financiere T
INNER JOIN Financement F ON (T.Code_financement = F.Code_financement)
order by t.min_deb DESC,t.min_tot
où l’on fait un order by sur des champs qui ne sont pas dans le distinct
Ceci a son importance dans les algos effectués à la suite de la requête.
Il est en effet impossible dans SQLServer de faire un order sans spécifier les champs dans le distinct.
Nous nous sommes donc retourné vers la solution que vous expliquez plus haut, mais SqlServer provoque une erreur:
« Chaque expression GROUP BY doit contenir au moins une colonne qui n’est pas une référence externe. »
En enlevant le group by le comportement de la requête semble être le même.
Pouvez vous expliquer plus précisément la méthode. A savoir :
– Pourquoi cette clause where dans le EXISTS : WHERE T1.C1 BETWEEN T2.C2 – 1 AND T2.C2 + 1
– Pourquoi un Having count(*) = 1, alors qu’il serait plus cohérent de faire une Having count(*) > 0
J’espère avoir été suffisamment clair, dans le cas contraire n’hésitez-pas à me le faire savoir.
Cordialement,
juva