Oracle SQL + select from multiple tables while doing (Not Exists) onto another.

I got this sql query that works fine, I select from 2 tables, but I need to (in the Where clause) do a NOT EXISTS from another table yet, and so far it's not going well, your thoughts.

Thx
Roberto Madro R.Programmer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Without the query all we can say is:  That should be possible.

You will also need to define "not going well".  Does it not return any data, not return the correct data or does it catch the keyboard on fire?
Roberto Madro R.Programmer AnalystAuthor Commented:
In this section of the query / script;

*************
select xyz, abc, etc........

from
information2 df,
infomration3 dl
where not exists
(Select * from information3 cl Where df.recordid = cl.recordid and
df.recordid is not null And
df.recordid = dl.recordid (+) And
df.OrdDate <= trunc(to_char(sysdate,'YYYYMMDD')));
spool off

******** I'm getting error ORA-01705: an outer join cannot be specified on a correlation column  *********
PortletPaulEE Topic AdvisorCommented:
Perhaps you might cease using antiquated syntax for joins?

The following is a bit of a guess, but the error message is quite accurate, you can't define the outer join (joining df and dl) inside the NOT EXISTS subquery.

Current ANSI standard syntax:
SELECT
    xyz
  , abc
  , etc........
FROM information2 df
LEFT JOIN infomration3 dl ON df.recordid = dl.recordid
WHERE df.OrdDate <= trunc(sysdate)
AND df.recordid IS NOT NULL
AND NOT EXISTS (
    SELECT
        *
    FROM information3 cl
    WHERE df.recordid = cl.recordid
    )

Open in new window

& now I will now attempt the same in the ancient art:
SELECT
    xyz
  , abc
  , etc........
FROM information2 df, infomration3 dl
WHERE df.recordid = dl.recordid (+)
AND df.OrdDate <= trunc(sysdate)
AND df.recordid IS NOT NULL
AND NOT EXISTS (
    SELECT
        *
    FROM information3 cl
    WHERE df.recordid = cl.recordid
    )

Open in new window


NB: I do not believe you need to use trunc(to_char(sysdate,'YYYYMMDD'))
by itself trunc(sysdate) sets the time to 00:00:00
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

johnsoneSenior Oracle DBACommented:
I know that the optimizer has gotten much better, but in the subquery for the NOT EXISTS, I would change

SELECT *

to

SELECT NULL

Internally, the optimizer may do that now, but it didn't used to and there is a performance penalty for that.  The SELECT * has to do additional I/O to get the entire row, while the SELECT NULL can not only be satisfied using only the index (assuming there is one) but only a single bit needs to be projected.
PortletPaulEE Topic AdvisorCommented:
I believe it is many years since using "select *" when associated with |not| exists has been a performance issue.  
Wasn't that back in Ora 8x days?
johnsoneSenior Oracle DBACommented:
I know for sure that it would do it up through at least 9 and probably into 10.  As I said, the optimizer may rewrite your query to avoid that now.  But, the optimizer could choose a different path and not recognize that it does not have to project the whole row.  The optimizer is not infallible, don't trust it to do your job for you.
jtriftsMI and AutomationCommented:
Ask Tom will disagree... (with the SELECT * alleged performance issue. Tom Kyte has been debunking it for years...and he's bee retired for what, 5 years now?!)

As for "antiquated coding"... Oracle developers used Oracle shorthand for ages. That does not make it antiquated. It makes it efficient.
Writing a "(+)" instead of LEFT OUTER JOIN XXX ON blah blah  = blah... It's obvious to Oracle developers and does not require the elaborate syntax of ANSI.

Coding in ANSI will make your SQL code more "portable", however, if you are a commercial software supplier, portable code should not be your priority...it is a gold-plated waste of your talents. Maintainable code? Good, go for it. Portable? Waste of your time and your client's money.
johnsoneSenior Oracle DBACommented:
Older incarnations of the optimizer did access the entire row if you had a SELECT * in the NOT EXISTS.  You could see it in the plan.  It was quite obvious.  As I said, the optimizer has surely changed, but why chance the optimizer changing your query in some way (it does look at thousands of permutations by default) that it would believe it has to project the entire row?  The optimizer isn't perfect, never has been, never will be.  Give it all the help you can.
jtriftsMI and AutomationCommented:
Sure, if you're running 7.3, and possibly 8i. Don't code to specs which no longer exist. Help the optimizer by writing and testing your code and by understanding explain plan, tk_prof, baselines, adaptive plans,  Give [the optimizer] help where it requires it...not "all the help you can".

To me, that's like perfectionism. I wouldn't hire a perfectionist as they waste too much money on frivolity.
Roberto Madro R.Programmer AnalystAuthor Commented:
The " ( " at the end of the (Select * from ... ) was missing, applied it, took the " )" from last line of the script out, and voila, it worked, but as usual, I always learn new things from all the experts on EE.  Many thanks

select xyz, abc, etc........
from
information2 df,
infomration3 dl
where not exists
(Select * from information3 cl Where df.recordid = cl.recordid) and
df.recordid is not null And
df.recordid = dl.recordid (+) And
df.OrdDate <= trunc(to_char(sysdate,'YYYYMMDD'));
spool off

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.