Solved

Oracle select with sub select not working

Posted on 2006-11-30
13
2,271 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
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 150 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 32

Accepted Solution

by:
awking00 earned 150 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 76

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 76

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 76

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 76

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

832 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