We help IT Professionals succeed at work.

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

Roberto Madro R.
on
98 Views
Last Modified: 2018-10-22
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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 Analyst

Author

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 Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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 Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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 DBA
CERTIFIED EXPERT

Commented:
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 Automation
CERTIFIED EXPERT

Commented:
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 DBA
CERTIFIED EXPERT

Commented:
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 Automation
CERTIFIED EXPERT

Commented:
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.
Programmer Analyst
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.