If no duplicate values are returned, the DISTINCT node is superfluous, and
transforms the
statement internally into one without the DISTINCT keyword.
The requirements are as follows:
- No GROUP BY list.
- SELECT list contains at least one
simple
column reference.
- Every simple column reference is from the same table.
- Every table in the FROM list is a
base
table.
- Primary table
There is at least one unique index on one table in the FROM list for which
all the columns appear in one of the following:
- Equality
predicates with expressions that do not include any column
references
- Simple column references in the SELECT list
- Secondary table(s)
All the other tables in the FROM list also have at least one unique index for
which all the columns appear in one of the following:
- Equality predicates with expressions that do not include columns from the
same table
- Simple column references in the SELECT list
For example:
CREATE TABLE tab1 (c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 CHAR(2),
PRIMARY KEY (c1, c2, c3))
CREATE TABLE tab2 (c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY (c1, c2))
INSERT INTO tab1 VALUES (1, 2, 3, 'WA'),
(1, 2, 5, 'WA'),
(1, 2, 4, 'CA'),
(1, 3, 5, 'CA'),
(2, 3, 1, 'CA')
INSERT INTO tab2 VALUES (1, 2),
(1, 3),
(2, 2),
(2, 3)
-- all the columns in the index on the only table (tab1) appear
-- in the way required for the Primary table (simple column references)
SELECT DISTINCT c1, c2, c3, c4
FROM tab1
-- all the columns in the index on the only table (tab1) appear
-- in the way required for the Primary table (equality predicates)
SELECT DISTINCT c3, c4
FROM tab1
WHERE c1 = 1
AND c2 = 2
AND c4 = 'WA'
-- all the columns in the index on tab1 appear
-- in the way required for the Primary table,
-- and all the columns in the
-- other tables appear in the way required
-- for a Secondary table
SELECT DISTINCT tab1.c1, tab1.c3, tab1.c4
FROM tab1, tab2
WHERE tab1.c2 = 2
AND tab2.c2 = tab1.c2
AND tab2.c1 = tab1.c1