Details
-
Sub-task
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
-
ghx-label-2
Description
Table constraints (primary key, unique key) or reference integrity constraint information can be used to remove unnecessary DISTINCT aggregation.
T1 (A, B, C)
T2 (X, Y, Z)
Given the constraints:
primary key (T1.A)
primary key (T2.X, T2.Y)
T1 foreign key references T2
T1.B -> T2.X
T1.C -> T2.Y
Distinct can be removed:
SELECT DISTINCT T1.A, T1.B FROM T1;
SELECT DISTINCT T2.X, T2.Y FROM T2;
SELECT DISTINCT T1.A, T2.X, T2.Y, T2.Z FROM T1 INNER JOIN T2 ON (T1.B=T2.X and T1.C = T2.Y)
Distinct can't be removed:
SELECT DISTINCT T2.X, T2.Z FROM T2;
SELECT DISTINCT T1.A, T2.X, T2.Z FROM T1 INNER JOIN T2 ON (T1.B=T2.X and T1.C = T2.Y)