?
Solved

Oracle select with sub select not working

Posted on 2006-11-30
13
Medium Priority
?
2,286 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
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.

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 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