asked on

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

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

tncbbthositg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tncbbthositg

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

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tncbbthositg

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

slightwv (䄆 Netminder)

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.

tncbbthositg

What's the minus operator?

TNC

slightwv (䄆 Netminder)

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.

slightwv (䄆 Netminder)

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.

tncbbthositg

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

slightwv (䄆 Netminder)

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

tncbbthositg

Oh SlighWV, you are my hero! Thanks. I've been dying to kick the oracle tires. Thank you.

tncbbthositg

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