[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle select with sub select not working

Posted on 2006-11-30
13
Medium Priority
?
2,289 Views
Last Modified: 2010-07-27
I want to compare two tables and display items from one table that are not in the other table. The query returns no results as written below. Thanks.

select distinct STATE_lookup.STATE, state_fullname from STEO.ADOPT_STATE, STEO.STATE_LOOKUP
      where STATE_LOOKUP.STATE NOT IN (select STATE from adopt_state)
      and state_lookup.state <> 'DC'
      and state_lookup.state <> 'US'
    order by state_fullname
0
Comment
Question by:pwdavismd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 18047942
what about this:

select distinct sl.STATE, sl.state_fullname
from STEO.STATE_LOOKUP sl
where sl.STATE NOT IN (select STATE from adopt_state)
and sl.state <> 'DC'
and sl.state <> 'US'
order by sl.state_fullname
0
 
LVL 8

Assisted Solution

by:tncbbthositg
tncbbthositg earned 600 total points
ID: 18048062
The only difference between mine and angelIII's is that I used the object owner in my subquery.  That's all I've got for you :)

SELECT DISTINCT
  sl.state,
  sl.state_fullname
FROM STEO.STATE_LOOKUP AS sl
WHERE
  sl.state NOT IN (SELECT state FROM STEO.adopt_state) AND
  sl.state <> 'DC' AND
  sl.state <> 'US'
ORDER BY sl.state_fullname
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048078
Actually, this would probably work too:

SELECT DISTINCT
  sl.state,
  sl.state_fullname
FROM STEO.STATE_LOOKUP AS sl
LEFT JOIN STEO.ADOPT_STATE AS adopt
  ON sl.state = adopt.state
WHERE
  sl.state <> 'DC' AND
  sl.state <> 'US' AND
  adopt.state IS NULL
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 32

Accepted Solution

by:
awking00 earned 600 total points
ID: 18048187
select state, state_fullname from state_lookup l
where not exists
(select 1 from adopt_state a
 where a.state = l.state)
and state <> 'DC'
and state <> 'US'
order by state_fullname;
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048284
That's an interesting technique awking.  I don't suppose you need a correlated subquery.  Are you intentionally trying to avoid the performance boost by the caching that occurs by the non-correlated subqueries?  I'd imagine that your solutions comes at a huge cost because your subquery has to be executed once for every row in the state_lookup table.  Why woludn't you just use a regular subquery like Angel and I used?

TNC
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18048333
Just to add to the mix, I'm not sure why a simple 'MINUS' wouldn't work and it should be faster than a table join:

Show everything in A that isn't in B:
---------------------------------
select state from tableA
minus
select state from tableB where blah, blah, blah.

0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048343
What's the minus operator?

TNC
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18048387
It's one of the set operators (UNION, UNION ALL, INTERSECT and MINUS) allowed in Oracle:
http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10759/queries004.htm#sthref2253

I really don't know the best way to describe it other than it takes the result set from the top query and subtracts the matching values from the bottom query.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18048400
Forgot to add:
Oracle claims and I tend to agree that use of the set operators will out perform joins and subquerys most of the time.  Of course, there are always exceptions to that rule.
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048430
Interesting.  I haven't used oracle.  I wonder how that is optimized.  You'd think a subquery or a join would be faster because the original worktable wouldn't be quite as large.  Actually, I suppose that the worktable would be the same size with the minus in the case of a left join . . . curious.  I wish oracle would do what everybody else does and provide a free developer edition.

TNC
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 18048697
OOPS....  I thought this question was in the Oracle TA (I spend most of my time there).  Having reread everything, I realized Oracle was never mentioned...

>>I wish oracle would do what everybody else does and provide a free developer edition.
Actually Oracle does better than most:  ALL oracle products are free to use in a development/learning enviroment and they now have a small footprint product free for use in production called XE (similar to MSDE).

Oracle products can be downloaded from: http://www.oracle.com/technology/index.html
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048864
Oh SlighWV, you are my hero!  Thanks.  I've been dying to kick the oracle tires.  Thank you.
0
 
LVL 8

Expert Comment

by:tncbbthositg
ID: 18048978
Thanks for the points pwdavismd.  Don't forget that awking00's answer is fundamentally different from Angel's anwser or my answer.  Before implementing that as a solution, you might consider doing a little research into correlated subqueries.  Just a caveat.

TNC
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question