Optimizing a Query (Removing Sub Select)

[NateUNI]

I have the following query:

SELECT *
FROM Prospect
WHERE Prospect.ID_NUMBER NOT IN (
SELECT ID_NUMBER
FROM Prospect_EMAIL
WHERE CAMPAIGNID=50)

This query takes a LONG time to run. Is that a better way to write this query. I am using Oracle if that helps, Thanks!!

 

[notadba]

Depending on which fields you need from the Prospect table, you could limit your query to just return the ID's by the following:

SELECT ID_NUMBER
FROM Prospect
MINUS
SELECT ID_NUMBER
FROM Prospect_EMAIL
WHERE CAMPAIGNID=50)

Otherwise the Oracle forum might be a better place to get tuning info. Hope this hepls.

 

[NateUNI]

How about if I have fields in the first select that are not in the second select? Thanks!

 

[dnoeth]

SELECT *
FROM Prospect p
WHERE NOT EXISTS
(SELECT ID_NUMBER
FROM Prospect_EMAIL e
WHERE p.ID_NUMBER = e.ID_NUMBER
AND CAMPAIGNID=50)

or

SELECT p.*
FROM
Prospect p LEFT OUTER JOIN
Prospect_EMAIL e
ON p.ID_NUMBER = e.ID_NUMBER
AND CAMPAIGNID=50
WHERE p.ID_NUMBER IS NULL

I'm not shure if Oracle's Outer Join syntax using (*) will return the same result set.

Dieter