Select DISTINCT vs. WHERE EXISTS

Toad for Oracle
1

It has been suggested elsewhere including here

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TSQ4
19/Default.aspx

that a select with “WHERE EXISTS” is more efficient than a select
DISTINCT.

That’s swell, but I find in my minimal experience that it also selects
duplicates. Did I stray somewhere?

I believe these two queries are reasonable adaptations of the examples given at
the ToadWorld site.

– Select ORGs that have employees

SELECT DISTINCT

f . ftvorgn_orgn_code org ,

f . ftvorgn_title title

FROM

ftvorgn f , – Table of ORGs

pebempl p – Table of employees

WHERE

f . ftvorgn_orgn_code = p. pebempl_orgn_code_home

ORDER BY 1 ;

– Select ORGs that have employees

SELECT

f . ftvorgn_orgn_code org ,

f . ftvorgn_title title

FROM

ftvorgn f – Table of ORGs

WHERE EXISTS

(select

‘ORGs that have employees’

from

pebempl p – Table of employees

where

p. pebempl_orgn_code_home = f . ftvorgn_orgn_code )

ORDER BY 1 , 2

The first query returns 228 unique rows; the second 533, the majority of them
being duplicates.

What gives?

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

image001.jpeg
image001.jpeg

2

My colleagues have taken great delight in pointing out the error of my ways. :wink:
There’s a line on the ToadWorld site that reads:

“Ensure that the columns used in the correlation are supported by an
appropriate index .”

What’s meant by “appropriate index” is a unique index or
primary key.

ftvorgn_orgn_code in our ftvorgn table is not “supported by” a
unique index. Many of the codes appear multiple times in the table. Thus the
many duplicates in my query.

So I’ve learned something in spite of it all. J And now I’ll need to
learn to accommodate the two dates that are in the primary key for the ftvorgn
table. So much fun to be had!

Dan

Daniel B Madvig
Computer Technologies

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323
image001.jpeg

3

I should take a look at the data in the ftvorgn table. WHERE EXISTS should
not duplicate. There are duplicate records in the table. As Daniel suggested add
a primary key, after removing the duplicate records off course.

Groetjes,
Wim

2011/11/8 Madvig, Daniel B

My colleagues have taken great delight in pointing out the error of my ways.
;-) There’s a line on the ToadWorld site that reads:

“Ensure that the columns used in the correlation are supported by an
appropriate index .”

 

What’s meant by “appropriate index” is a unique index or primary key.

 

ftvorgn_orgn_code in our ftvorgn table is not “supported by” a unique
index. Many of the codes appear multiple times in the table. Thus the many
duplicates in my query.

 

So I’ve learned something in spite of it all. J And now I’ll need to
learn to accommodate the two dates that are in the primary key for the
ftvorgn table. So much fun to be had!

 

Dan

Daniel B Madvig
Computer Technologies

 

Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu

651.631.5323

image001.jpeg