Solved

Oracle select with sub select not working

Posted on 2006-11-30
13
2,267 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
13 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
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 150 total points
Comment Utility
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
Comment Utility
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
 
LVL 31

Accepted Solution

by:
awking00 earned 150 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:tncbbthositg
Comment Utility
What's the minus operator?

TNC
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
This article describes some very basic things about SQL Server filegroups.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now