Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2293
  • Last Modified:

Oracle select with sub select not working

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
pwdavismd
Asked:
pwdavismd
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
tncbbthositgCommented:
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
 
tncbbthositgCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
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
 
tncbbthositgCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
tncbbthositgCommented:
What's the minus operator?

TNC
0
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
tncbbthositgCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
tncbbthositgCommented:
Oh SlighWV, you are my hero!  Thanks.  I've been dying to kick the oracle tires.  Thank you.
0
 
tncbbthositgCommented:
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now